All Forums Database
vinaynaran 10 posts Joined 10/14
24 Oct 2014
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
 
 

ulrich 816 posts Joined 09/09
24 Oct 2014

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

vinaynaran 10 posts Joined 10/14
25 Oct 2014

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
 
 

vinaynaran 10 posts Joined 10/14
27 Oct 2014

Hi all,
 
Anybody have solution. Please help me!!
 
Thanks
Vinay
 
 

CarlosAL 512 posts Joined 04/08
27 Oct 2014

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.

sk8s3i 35 posts Joined 06/13
28 Oct 2014

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

-Thanks Shardul

ravimans 54 posts Joined 02/14
29 Oct 2014

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

ulrich 816 posts Joined 09/09
29 Oct 2014

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
 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ravimans 54 posts Joined 02/14
29 Oct 2014

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?

ulrich 816 posts Joined 09/09
29 Oct 2014

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 

http://en.wikipedia.org/wiki/Modulo_operation

 

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

 
 
 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.