Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-24-2014
05:33 AM

10-24-2014
05:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-24-2014
08:03 AM

10-24-2014
08:03 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-25-2014
12:46 PM

10-25-2014
12:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-27-2014
05:26 AM

10-27-2014
05:26 AM

Re: Subquery in Case Statement

Hi all,

Anybody have solution. Please help me!!

Thanks

Vinay

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-27-2014
08:34 AM

10-27-2014
08:34 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-28-2014
12:53 AM

10-28-2014
12:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-29-2014
11:58 AM

10-29-2014
11:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-29-2014
12:27 PM

10-29-2014
12:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-29-2014
12:47 PM

10-29-2014
12:47 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-29-2014
01:05 PM

10-29-2014
01:05 PM

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;