Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

02-11-2008
10:28 AM

02-11-2008
10:28 AM

Hi,

Can anybody help me with the following Please!!!!

The procedure below runs in SQL server, however I can't seem to convert it to Teradata code.

The main problem is that I can't use a different select statement to create each new column. as shown in the code below, I am not sure if it is a syntax problem, or if it is a teradata can't do a similar thing problem.

Any help at this point would be great.

SELECT YEAR,

Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =

Q.YEAR),0),

Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =

Q.YEAR),0),

Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =

Q.YEAR),0),

Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =

Q.YEAR),0)

FROM QTRSALES Q

GROUP BY YEAR

Thanks a Mill

Chris

Can anybody help me with the following Please!!!!

The procedure below runs in SQL server, however I can't seem to convert it to Teradata code.

The main problem is that I can't use a different select statement to create each new column. as shown in the code below, I am not sure if it is a syntax problem, or if it is a teradata can't do a similar thing problem.

Any help at this point would be great.

SELECT YEAR,

Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =

Q.YEAR),0),

Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =

Q.YEAR),0),

Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =

Q.YEAR),0),

Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =

Q.YEAR),0)

FROM QTRSALES Q

GROUP BY YEAR

Thanks a Mill

Chris

6 REPLIES

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

02-11-2008
12:19 PM

02-11-2008
12:19 PM

SELECT YEAR,

Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =

Q.YEAR),0),

Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =

Q.YEAR),0),

Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =

Q.YEAR),0),

Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =

Q.YEAR),0)

FROM QTRSALES Q

GROUP BY YEAR

select year

, case when (quarter = 1 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q1

, case when (quarter = 2 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q2

, case when (quarter = 3 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q3

, case when (quarter = 4 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q4

from qtrsales Q

group by year

I guess this should work

Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =

Q.YEAR),0),

Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =

Q.YEAR),0),

Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =

Q.YEAR),0),

Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =

Q.YEAR),0)

FROM QTRSALES Q

GROUP BY YEAR

select year

, case when (quarter = 1 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q1

, case when (quarter = 2 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q2

, case when (quarter = 3 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q3

, case when (quarter = 4 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end as Q4

from qtrsales Q

group by year

I guess this should work

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

02-11-2008
12:22 PM

02-11-2008
12:22 PM

My bad please ignore my earlier post.

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

02-11-2008
09:26 PM

02-11-2008
09:26 PM

is this what you need? added SUM, so you end up with one row per year

select year

, case when (quarter = 1 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q1

, case when (quarter = 2 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q2

, case when (quarter = 3 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q3

, case when (quarter = 4 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q4

from qtrsales Q

group by year

select year

, case when (quarter = 1 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q1

, case when (quarter = 2 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q2

, case when (quarter = 3 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q3

, case when (quarter = 4 and q.year = year ) then (sum(coalesce(qtrsales,0))) else 0 end as Q4

from qtrsales Q

group by year

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

02-11-2008
09:30 PM

02-11-2008
09:30 PM

actually the sum would go around the entire case

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

02-13-2008
05:42 AM

02-13-2008
05:42 AM

So Jeff, Are you talking about something like this:

select year

, SUM(case when (quarter = 1 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q1

, SUM(case when (quarter = 2 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q2

, SUM(case when (quarter = 3 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q3

, SUM(case when (quarter = 4 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q4

from qtrsales Q

group by year

select year

, SUM(case when (quarter = 1 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q1

, SUM(case when (quarter = 2 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q2

, SUM(case when (quarter = 3 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q3

, SUM(case when (quarter = 4 and q.year = year ) then (coalesce(qtrsales,0)) else 0 end ) as Q4

from qtrsales Q

group by year

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

02-13-2008
04:00 PM

02-13-2008
04:00 PM

dont forget to change the qtrsales to Amount in the coalesce.

select year

, SUM(case when (quarter = 1 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q1

, SUM(case when (quarter = 2 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q2

, SUM(case when (quarter = 3 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q3

, SUM(case when (quarter = 4 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q4

from qtrsales Q

group by year

select year

, SUM(case when (quarter = 1 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q1

, SUM(case when (quarter = 2 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q2

, SUM(case when (quarter = 3 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q3

, SUM(case when (quarter = 4 and q.year = year ) then (coalesce(amount,0)) else 0 end ) as Q4

from qtrsales Q

group by year

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.