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
 

 

DB2 WITH Clause

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

New User


Joined: 15 Mar 2012
Posts: 5
Location: india

PostPosted: Wed Mar 21, 2012 11:58 am    Post subject: DB2 WITH Clause
Reply with quote

Table 1 : Table giving details about the groups and its members.
--------
group id member id

A 1
A 2
A 3
B 4
B 5
B 6

Table 2 : Details of member id and its associated desc
--------

Member id Member desc
------- --------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fff

Table 3
--------

Member id Member attribute 1 Member attribute 2
------ ------------------ -------------------
1 MA11 MA21
2 MA12 MA22
3 MA13 MA23
4 MA14 MA24
5 MA15 MA25
6 MA16 MA26



Input Parmeter PARM1 in my SP

PARMIN = Groupid1,Groupid2,...<can be upto 15 groupid's>

Result set should be all the member id's and corresponding member details for each of the member id as below.

Format of Result Set of my SP shud be as below:

Group id Member id Memberdesc Member attribute 1 Member attribute 2


How can i acheive this using 'WITH CLAUSE' of DB2.
Can someone help?
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Mar 21, 2012 4:01 pm    Post subject:
Reply with quote

Given that the WITH clause affects only the isolation level, why do you think its use would be significant?
Back to top
View user's profile Send private message
Deepa Sridharan

New User


Joined: 15 Mar 2012
Posts: 5
Location: india

PostPosted: Wed Mar 21, 2012 4:58 pm    Post subject:
Reply with quote

Reason why i need With Clause:
The most obvious thing one gets from use of the WITH clause is the ability to construct reusable components inside a SELECT. We can give a name to a set of rows (no matter how complicated the SELECT is to build them), and then use the name of the NAMED SELECT, anywhere we would have otherwise had to duplicate the text we named.

I cannot use a array in my native SP and would like to avoid GTMP as WITh CLAUSE(common temp tables) will be more performance efffective. Hence the with clause.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Wed Mar 21, 2012 5:00 pm    Post subject: Reply to: DB2 WITH Clause
Reply with quote

Are you talking about the Common Table Expressions? E.g. -

Code:
WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
Back to top
View user's profile Send private message
Deepa Sridharan

New User


Joined: 15 Mar 2012
Posts: 5
Location: india

PostPosted: Wed Mar 21, 2012 5:34 pm    Post subject:
Reply with quote

Yes. Got the solution myself.Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Mar 21, 2012 5:44 pm    Post subject: Reply to: DB2 WITH Clause
Reply with quote

And, for others in a similar situation to yours, would you mind saying what the solution is?
Back to top
View user's profile Send private message
Deepa Sridharan

New User


Joined: 15 Mar 2012
Posts: 5
Location: india

PostPosted: Thu Mar 22, 2012 12:08 pm    Post subject:
Reply with quote

Sorry.. Here is what i came up with

With array1(groupid,memberid) as (select groupid,memeberid from table1 where groupid in <input parm>)

select t1.groupid,t1.memberid,t2.memberdesc,t3.memberattribute1,t3.memberattribute2 from
array1 t1,table2 t2,table3 t3 where
t1.memberid = t2.memberid and
t2.memberid = t3.memberid with ur;
Back to top
View user's profile Send private message
Parthiban DS

New User


Joined: 07 Aug 2011
Posts: 5
Location: India

PostPosted: Thu Mar 22, 2012 1:59 pm    Post subject:
Reply with quote

Your predicate should contain t1.groupid = :host-variable, else entire records from Table-1 will be extracted.
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 Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts [SQL0029] INTO clause missing from em... HABBIE DB2 2 Fri Sep 04, 2015 3:54 pm
No new posts 3-D Table with nested DEPENDING ON Cl... VivekKhanna COBOL Programming 5 Sat Aug 08, 2015 11:18 pm
No new posts when=group clause: question about the... tuxama DFSORT/ICETOOL 2 Thu Mar 26, 2015 12:51 pm
No new posts pic clause on group variable error CuriousMainframer COBOL Programming 6 Mon Jul 14, 2014 6:33 pm


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