This is my first Post. I would like to run somthing using Case statement in Teradata SQL Assistant.
My Query as below
case when Day_of_week = 2 then
(sel top 5 Fld1,Fld2 from Table1)
(sel top 5 Fld1,Fld2 from Table2)
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!
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?
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
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.
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
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...
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?
check the manual
select (('2014-10-27' (date)) - ('1900-01-01' (date))) as dt_diff, dt_diff mod 7;