Database

Turn on suggestions

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

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 6

Highlighted
##
Re: Transpose a Table, Using possible nested select statements, Working in SQL-Server But won't work in Teradata

- 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