CODEWITHSUNDEEP
sql
Charlie
Charlie

Reputation: 11

Running a query with cast and alias - not working

I have this query I am trying to run, and I keep getting an error. Could be somethign related to the alias, but I think this seems correct?

SELECT user_key, playerslevel
FROM data_lab.vw_ft_Game_Progression a
INNER JOIN data_lab.vw_progress b on a.rock_key = b.rock_key
WHERE user_key = 21111119 AND type = 'Level'
AND (CAST(REGEXP_REPLACE(game_progression, 'LEVEL ', '') as INTEGER)) as playerslevel < 15

ERROR: Executed as Single statement. Failed [3707 : 42000] Syntax error, expected something like a 'SUCCEEDS' keyword or a 'MEETS' keyword or a 'PRECEDES' keyword or an 'IN' keyword or a 'CONTAINS' keyword between ')' and the 'as' keyword.

Do you know how I can fix this?

Upvotes: 0

Views: 236

Answers (1)

JonH
JonH

Reputation: 33163

Just get rid of as playerslevel. You do not need to alias it in your WHERE clause.

Result:

SELECT 
     user_key, 
     playerslevel
FROM 
     data_lab.vw_ft_Game_Progression a
INNER JOIN 
     data_lab.vw_progress b 
ON 
     a.rock_key = b.rock_key
WHERE 
     user_key = 21111119 
     AND type = 'Level'
     AND (CAST(REGEXP_REPLACE(game_progression, 'LEVEL ', '') as INTEGER)) < 15;

Based on your comment If you just want to get the number you can do this:

DECLARE @test_level VARCHAR(50) = (SELECT 'Level 15');

DECLARE @int_pos INT = (SELECT CHARINDEX(' ', @test_level, 0));

SELECT @int_pos;

SELECT CAST(SUBSTRING(@test_level, @int_pos, LEN(@test_level)-1) AS INT);

So in your case you can do:

SELECT 
     user_key, 
     playerslevel
FROM 
     data_lab.vw_ft_Game_Progression a
INNER JOIN 
     data_lab.vw_progress b 
ON 
     a.rock_key = b.rock_key
WHERE 
     user_key = 21111119 
     AND [type] = 'Level'
     AND CAST(SUBSTRING(game_progression, CHARINDEX(' ', game_progression, 0), LEN(game_progression)-1) AS INT) < 15;

Upvotes: 1

Related Questions