Subquery in Case Statement

Database
Enthusiast

Subquery in Case Statement

Hi All,

This is my first Post. I would like to run somthing using Case statement in Teradata SQL Assistant.

My Query as below

sel

case when  Day_of_week = 2 then

  (sel top 5  Fld1,Fld2 from Table1)

 else

   (sel top 5  Fld1,Fld2 from Table2)

 end

from

SYS_CALENDAR.Calendar

 WHERE SYS_CALENDAR.Calendar.calendar_date = date; 

I am geting below Error Message:

SELECT failed. 3706:Syntax Error: expected something between '(' and the 'SELECT' keyword.

Please help me out!

Thanks.

Vinay

9 REPLIES
Supporter

Re: Subquery in Case Statement

sel top 5  Fld1,Fld2 from Table1 will give you up to 5 rows back -> so what is your expected assignment?

Enthusiast

Re: Subquery in Case Statement

Hi.

I know it will give me only 5 rows. But  I want to use Select statement when condition is correct.

I posted just sample and want to know Can i use select statement?

Thanks

Vinay

Enthusiast

Re: Subquery in Case Statement

Hi all,

Anybody have solution. Please help me!!

Thanks

Vinay

Junior Supporter

Re: Subquery in Case Statement

Hi.

This could be an option:

SELECT TOP 5
Fld1,
Fld2
FROM (
SELECT Fld1, Fld2 FROM TABLE1
WHERE ((DATE - date '1900-01-01') mod 7) + 1 <> 2
UNION ALL
SELECT Fld1, Fld2 FROM TABLE2
WHERE ((DATE - date '1900-01-01') mod 7) + 1 = 2
) pre
;

(monday as first day of week)

HTH

Cheers.

Carlos.
Enthusiast

Re: Subquery in Case Statement

When we use a case statement in select list, the subquery must return one and only one value. Imagine a query returning values more than a field can take. You can select something like MAX(field) from table. Something like below would be valid:

select case when day_of_week = 2 then

(select max(field1) from Table1)

else (select min(field1) from Table1) end as my_field

from .....

....

....

;

Also, a subquery will not support TOP, or SAMPLE.

In a case statement, you can not select more than one field. It's like putting oil and water in one container. They just wont mix.

If your requirement is to select top 5 field1 and field 2 based on day_of_week = 2 condition, then the solution suggested by our friend Carlos is good.

Thanks,

Shardul

Enthusiast

Re: Subquery in Case Statement

Hi Carlos,

Just wanted to know what does Mod7 does and how you are achieving the results without using SYS_CALENDAR.Calendar . DATE in the below query are you referring to current_date. Can you explain me with an example?

WHERE ((DATE - date '1900-01-01') mod 7) + 1 <> 2

WHERE ((DATE - date '1900-01-01') mod 7) + 1 = 2

Supporter

Re: Subquery in Case Statement

maybe I can give also an explanation

mod calcualtes the rest based on a devision - 1 mod 3 = 1, 4 mod 3 = 1,  6 mod 3 = 0 etc.

now you calculate the difference between 2 dates.

1.1.1900 was a Monday

-> 15.1.1900 was a Monday as well for example

diff is 14 mod 7 = 0  

+ 1 gives 1 for this calculation

so when ever the calcualtion gives 1 it is a Monday

2 means Tuesday... 7 Sunday.

So you can make a choise on Tuesdays...

Clear

Enthusiast

Re: Subquery in Case Statement

Hi Ulrich,

Thanks for the details but still i am not clear about the logic of mod. When you say mod calculates rest based on dvision...for ex:14/7 = 2 is this the output or mod has some specific calculation?

1.1.1900 was a Monday

27.10.2014 was also a Monday and what will be the difference for this one and how we can calculate the days as mentioned above?

Supporter

Re: Subquery in Case Statement

check the manual 

Modulo (remainder). 

MOD calculates the remainder in a division operation.

For example, 60 MOD 7 = 4: 60 divided by 7 equals 8, with a remainder of 4. The result takes the sign of the dividend, thus:

-17 MOD 4 = -1 -17 MOD -4 = -1 17 MOD -4 = 1 17 MOD 4 = 1

This is a Teradata extension to the ANSI SQL:2011 standard.

wikipedia 


select (('2014-10-27' (date)) - ('1900-01-01' (date))) as dt_diff, dt_diff mod 7;