Case when statement

General
Highlighted

Case when statement

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

(select

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

where timeline>difference_1

...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.

3 REPLIES
Senior Apprentice

Re: Case when statement

Hi,

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.

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Case when statement

I made my updates and added additional information. This is Teradata SQL too.

Senior Apprentice

Re: Case when statement

Hi,

 

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.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com