Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

output in the same order of conditions given in where clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sagarnsydney

New User


Joined: 12 Dec 2008
Posts: 16
Location: sydney

PostPosted: Thu Aug 12, 2010 2:38 am    Post subject: output in the same order of conditions given in where clause
Reply with quote

Hi All,

Hope everybody is doing good.

I have a below requirement. I want to get output as mentioned in where clause of the query.

For Eg:- TABLE1 has three columns COL1,COL2 and COL3

Query is:
SELECT COL1,COL2,SUM(COL3)
FROM TABLE1
WHERE (COL1=A AND COL2=1
OR COL1=E AND COL2=5
OR COL1=F AND COL2=6
OR COL1=C AND COL2=3
OR COL1=D AND COL2=4
OR COL1=B AND COL2=2
OR COL1=G AND COL2=7)
GROUP BY COL1,COL2

I want the output as mentioned in above where clause. But, I am getting output in some default order of COL1 and COL2 as below

Actual Output:
A 1 4
B 2 6
C 3 7
D 4 9
E 5 8
F 6 2
G 7 3

Expected Output:
A 1 4
E 5 8
F 6 2
C 3 7
B 2 6
D 4 9
G 7 3

Is there any way in DB2 to get the output as mentioned in where clause.

Thank you in advance.

Regards,
Sagar
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Aug 12, 2010 3:54 am    Post subject:
Reply with quote

Try reading about order by!!!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 12, 2010 3:55 am    Post subject:
Reply with quote

Hello,

The sequence will be the same as the "group by" . . .

You will need to use a different method to get what you want.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Aug 12, 2010 1:13 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

The sequence will be the same as the "group by" . . .

You will need to use a different method to get what you want.

No it will not. at least not always.maybe if your lucky with the accesspath.
The only guarantee is to use ORDER BY as craq said.

but to solve this specific problem, it's not a very nice solution but it works :
Code:
With Temp(srt, col1,col2) as (
select 1,'A',1  from sysibm.sysdummy1 union all
select 2,'E',5  from sysibm.sysdummy1 union all
select 3,'F',6  from sysibm.sysdummy1 union all
select 4,'C',3  from sysibm.sysdummy1 union all
select 5,'D',4  from sysibm.sysdummy1 union all
select 6,'B',2  from sysibm.sysdummy1 union all
select 7,'G',7  from sysibm.sysdummy1 )

SELECT Temp.COL1,Temp.COL2
, (Select SUM(T1.COL3) from TABLE1  T1 where temp.COL1 = T1.col1 and temp.col2 = T1.col2) as Sum_Col3
FROM temp
order by Temp.srt

OR
Code:
With Temp(srt, col1,col2) as (
select 1,'A',1  from sysibm.sysdummy1 union all
select 2,'E',5  from sysibm.sysdummy1 union all
select 3,'F',6  from sysibm.sysdummy1 union all
select 4,'C',3  from sysibm.sysdummy1 union all
select 5,'D',4  from sysibm.sysdummy1 union all
select 6,'B',2  from sysibm.sysdummy1 union all
select 7,'G',7  from sysibm.sysdummy1 )

SELECT Temp.srt, Temp.COL1,Temp.COL2,SUM(T1.COL3)
FROM temp
join TABLE1  T1 on temp.COL1 = T1.col1 and temp.col2 = T1.col2
GROUP BY Temp.srt,Temp.COL1,Temp.COL2
order by Temp.srt


"With" is just another way to write a Subselect.

As a side remark : if you are going to use ANDs and ORs in one where clause , I suggest to use brackets for clarity.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 12, 2010 9:50 pm    Post subject:
Reply with quote

Hello,

Quote:
No it will not. at least not always.maybe if your lucky with the accesspath.
The only guarantee is to use ORDER BY as craq said.
The only "guarantee" is the ORDER BY (which i also mention with some regularity).

But my point was that one cannot ORDER BY one thing and GROUP BY some other thing in the same query. . . I probably should have used more keystrokes. . . icon_redface.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Aug 13, 2010 2:06 pm    Post subject:
Reply with quote

dick scherrer wrote:
But my point was that one cannot ORDER BY one thing and GROUP BY some other thing in the same query. . . I probably should have used more keystrokes. . . icon_redface.gif

Sure you can :

Code:
select dbname, max(obid) ,  count(*) from sysibm.systables
group by dbname
order by 2 desc , 3

or
Code:
group by timestamp order by day(timestamp)
of course with a group by you can't sort on columns not in the group by , but even then it is possible to change the hierarchy of the sort.


suppose you have an index on colA,colB but you need it ordered by colB :
prior to Version 9 you could (depending on cardinalities) gain performance by writing
Code:
group by colA, colB
order by colB, colA

since V9 the optimizer can rearrange the sequence of the columns in the group by to search for a matching index
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Aug 13, 2010 11:52 pm    Post subject:
Reply with quote

Hi Guy,

Quote:
Sure you can :
Not until V9. For example, V8 gives a -122. . . icon_sad.gif

I suspect it will be much more valuable to the majority if "things" that are only available in V9 (and probably nfm at that) are so mentioned. . .

From what i can tell most of the forum members are on less than V9 and several of the ones that are on V9 have not gone to the new function mode. Possibly, i'm mistaken icon_confused.gif

Actually, i've not yet worked with an organization that is running V9 except in a test environment. But then, i don't get around as much these days icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us