| View previous topic :: View next topic |
| Author |
Message |
deepatred
Beginner
Joined: 29 Nov 2005
Posts: 19
Topics: 6
|
 Posted: Mon Mar 31, 2008 10:20 am Post subject: Help with one design problem - DB2 |
 |
|
Hi,
We have a database in which data appears in this format:
| Code: |
M1 T1
M1 T2
M1 T3
M2 T1
M2 T2
M2 T3
M2 T4 |
Etc... T row will be unique for an M value. For an M value there can be maximum of ten T values.
Now my requirement is to search an M value having a particular T combination i.e. if the search is for an M value with combination T1 T2 T3 then the result should be M1.
One of the way i thought is having sub queries. | Code: |
Select M
From DB
Where T = T1
and M IN (Select M
From DB
Where T = T2
and M IN (Select M
From DB
Where T = T3)) |
But as the no of T's increase the query will become very complex.
Other way is to organize the data in horizontal form | Code: |
M1 T1 T2 T3
M2 T1 T2 T3 T4
|
and a query on this. But is it possible to organize the DB2 data (using views or any other feature) in this format without creating a new table.
This is required in CICS code. We thought of having a VSAM file. But wanted to explore the option of doing it through DB2 as using a VSAM might add additional overhead in Online code.
Help to resolve this or any other suggestion will be really helpful.
Thanks |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 31, 2008 10:43 am Post subject: |
|
|
| Quote: |
Now my requirement is to search an M value having a particular T combination i.e. if the search is for an M value with combination T1 T2 T3 then the result should be M1. |
deepatred,
m2 also fulfills the condition as it also has the combination of t1,t2,t3. If you just want the records which has the combination,just get a count of records for the combination
for ex : (untested solution)
| Code: |
SELECT M
FROM TABLE
WHERE T IN ('T1','T2','T3')
GROUP BY M HAVING COUNT(*) = 3
|
Now all the records with a count of 3 will be the ones you are looking for.
Hope this helps...
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
deepatred
Beginner
Joined: 29 Nov 2005
Posts: 19
Topics: 6
|
| Posted: Mon Mar 31, 2008 12:02 pm Post subject: |
|
|
Hi Kolusu,
Thanks for the reply.
But T1 T2 T3 are just example values. It may change to T1 T3 T4 and a new T value t5 can get added. There is a user interface to do so.
So it can not be hard coded and T1 T2 T3 in my previous example should result M1.
Lets say we have a table as
| Code: | M value Tvalue1 Tvalue2 Tvalue3 Tvalue4 Tvalue5
M1 T1 T2 T3
M2 T1 T2 T3 T4
|
Tvalue4 and Tvalue5 in first case will be spaces. So i will have to consider all the Tvalues (10) even though it is spaces in some cases.
Thanks. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 31, 2008 3:01 pm Post subject: |
|
|
Try this.
| Code: |
SELECT M
,SUM(CASE WHEN T = 'T1 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T2 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T3 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T4 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T5 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T6 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T7 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T8 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T9 ' THEN INT(1) ELSE INT(0) END)
,SUM(CASE WHEN T = 'T10' THEN INT(1) ELSE INT(0) END)
FROM Table
GROUP BY M
;
|
This will produce an output like this
| Code: |
M value Tvalue1 Tvalue2 Tvalue3 Tvalue4 Tvalue5 .... all 10 values
M1 1 1 1 0 0 ...
M2 1 1 1 1 1 ... |
Now read each record and search for the matching string u passed. A value of 1 indicates that there is a matching record and a value of 0 indicates there is no record
Hope this helps...
Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
