|
View previous topic :: View next topic
|
| Author |
Message |
Deepa Sridharan
New User
Joined: 15 Mar 2012
Posts: 5
Location: india
|
|
|
|
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 |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Given that the WITH clause affects only the isolation level, why do you think its use would be significant? |
|
| Back to top |
|
|
Deepa Sridharan
New User
Joined: 15 Mar 2012
Posts: 5
Location: india
|
|
|
|
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 |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011
Posts: 16
Location: India
|
|
|
|
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 |
|
|
Deepa Sridharan
New User
Joined: 15 Mar 2012
Posts: 5
Location: india
|
|
|
|
| Yes. Got the solution myself.Thanks |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| And, for others in a similar situation to yours, would you mind saying what the solution is? |
|
| Back to top |
|
|
Deepa Sridharan
New User
Joined: 15 Mar 2012
Posts: 5
Location: india
|
|
|
|
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 |
|
|
Parthiban DS
New User
Joined: 07 Aug 2011
Posts: 5
Location: India
|
|
|
|
| Your predicate should contain t1.groupid = :host-variable, else entire records from Table-1 will be extracted. |
|
| Back to top |
|
|
|
|
