Hi I'm new to SQL.
I have two tables that is not giving me the correct output on my select statement at the end.
Create table2, As
received_date - discharge date AS difference_1
, CASE WHEN group1 IN ('1') AND state IN ('CA') THEN '150'
, CASE WHEN group1 IN ('1') AND state IN ('NV') THEN '180'
ELSE '' end Timeline
from table 1)
group by 1,2
select * from table2
...this select statement above is not giving me the correct output. There is no error message, but it's not pulling all the correct records that meet the criteria.
The output is giving me all records instead of the criteria. Not sure if the syntax is wrong. Even when I write... Select * from table2 where timeline > 150, I still get all the records instead of applicable records.
I assume that the sql that you've shown is pseudo-sql, because as is it will not run on Teradata.
The 'create table as' needs to be:
Create table2 As (select received_date - discharge date AS difference_1 CASE WHEN group1 IN ('1') AND state IN ('CA') THEN '150' WHEN group1 IN ('1') AND state IN ('NV') THEN '180' ELSE '' end Timeline from table 1 group by 1,2) with data;
The final sql that you showed selects from 'table1', but the columns Timeline and Difference_1 are in 'table2'.
Some sample data with a more detailed explanation of what is/is not coming back from your final sql might help us to help you.
You'll need to change your final select to:
Select * from table2 where timeline > '150'
(note that I've added single quotes around the 150 literal value).
In your create table as statement the timeline column is created a character, but in your final sql you're providing a numeric literal (no quotes). This is not a good mix.
try that and see what happens.