IBM Mainframe Forum

by **k singh** » Thu May 28, 2015 2:03 am

hi

I need a little help.
I have table with structure as follows:
table1

Year Id1 ID2 ID3
2001 1 20 Null
2001 2 400 Null
2002 1 100 Null
2002 2 50 Null
2002 3 null 60
2002 4 88 Null
2005 3 Null 45

Each year can have upto 4 rows with Id1 as (1,2,3,4). If ID2 has value then ID3 will be null and vice versa
I want my output as

Select all

Key1 Val1 Val2 Val3 Val4
2001 20    400 Null Null
2002 100    50 60    88
2005 null null 45    null

I have designed a query

Select all

SELECT Year,
(CASE WHEN Id1 = 1 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END) AS Val1,
(CASE WHEN Id1 = 2 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END) AS Val2,
(CASE WHEN Id1 = 3 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END) AS Val3,
(CASE WHEN Id1 = 4 THEN (CASE WHEN Id2 IS Not NULL THEN Id2 ELSE Id3 END ) ELSE NULL END)) AS Val4
FROM Sales
GROUP BY Year

WIll This work to get my desired output. If not Any suggestions/help will be appreciated

by **alexm** » Wed Jun 17, 2015 1:21 am

Hi k singh,

Running your query results in

DSNT408I SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID

Looks like the problem sits in the GROUP BY specification.

I've created a possible solution by this query:

Select all

WITH DATA0 (YEAR)
AS (SELECT YEAR FROM SALES GROUP BY YEAR)
, DATA1 (YEAR ,VAL1)
AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 1)
, DATA2 (YEAR ,VAL2)
AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 2)
, DATA3 (YEAR ,VAL3)
AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 3)
, DATA4 (YEAR ,VAL4)
AS (SELECT YEAR ,COALESCE(ID2,ID3) FROM SALES WHERE ID1 = 4)
SELECT D0.YEAR ,D1.VAL1 ,D2.VAL2 ,D3.VAL3 ,D4.VAL4
FROM DATA0 D0
LEFT JOIN DATA1 D1 ON D1.YEAR = D0.YEAR
LEFT JOIN DATA2 D2 ON D2.YEAR = D0.YEAR
LEFT JOIN DATA3 D3 ON D3.YEAR = D0.YEAR
LEFT JOIN DATA4 D4 ON D4.YEAR = D0.YEAR
;
---------+---------+---------+---------+---------+---------+-
YEAR VAL1 VAL2 VAL3 VAL4
---------+---------+---------+---------+---------+---------+-
2001 20 400 ------ ------
2002 100 50 60 88
2005 ------ ------ 45 ------
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Depending on the amount of rows to process, this may result in an expensive operation (unfortunately you didn't provide table size/index defs).

As an alternative, you may use some of the available XML aggregate and serialize funtionality (see also this thread)

Best regards,
alexm

by **NicC** » Wed Jun 17, 2015 1:21 pm

WIll This work to get my desired output

The best way to find out is not ask someone do do your work for you but to try it yourself and then if you have a problem that you or your colleagues cannot answer to ask on a forum. And this sort of query has been asked before on forums and the last time was only a week or two ago. There is a forum search function that should be used before posting.

IBM Mainframe Forum

Convert rows into column in select

Convert rows into column in select

Re: Convert rows into column in select

Re: Convert rows into column in select