sub select / case or another method problem

[MIKELEIBO]

Problem: Table ST with column aa, ab, ac, etc. has column ay pointing to another row in table ST where aa = xy.
az also matches to aa in different row in ST.
(sometimes xy and az are filled and sometimes not).

The result table has to show:
a.aa, a.ab, a.ac, b.xx (c.ab(xy = aa) or ' '),
(c.ab(xz = xz) or space).

I can't figure out what to do... please help

I tried:
Select a.aa, a.ab, a.ac, b.xx
case xy
when = ' ' then ' ' else
select c.ab from ST C where
c.aa = a.xy
end case
, case xz
when = ' ' then ' ' else
select c.ab from ST C where
c.aa = a.xz
end case
from ST a, SX b
and ------------------------------------

Select a.aa, a.ab, a.ac, b.xx
fto = xy
case
when fto = ' ' then ' ' else
select c.ab from ST C where
c.aa = fto
end case
ffr = xz
, case
when ffr = ' ' then ' ' else
select c.ab from ST C where
c.aa = ffr
end case
from ST a, SX b
-----------------------------------
SELECT DISTINCT * FROM ST, SX
CASE yz
WHEN ' ' THEN ' '
ELSE
SELECT * FROM ST c
WHERE C.aa = a.xy
END
from ST A, SX b
----------------------------------------
a million other variations...

 

[MarcLodge]

Mike,
I know that you've tried to be clear, but I'm not following you 100%.

You've stated that you have a table ST and that it has columns aa, ab, ac etc thru to az. So far I'm with you, but you then state column aa can equal column xy. Where does xz come from?

Also in your SQL you show tables qualified as a. b. and sometimes c. and I'm not sure whether these are all joins of the same table, or different, particularly as you mention table SX along with ST in your last example.

Come back to us with a slightly more simplistic description and example of what you are trying to achieve.

Marc

 

[MIKELEIBO]

Thanks! I hope you can help.

xy AND zy ARE COLUMNS IN TABLE ST.
THEY REFERENCE OTHER ROWS IN THE TABLE (A SORT OF FOREIGN KEY?) WHERE xy/xz can = aa. SOMETIMES XY/XZ are NULL.
I have to get ab(other row) when ay(1st row) = aa(other row)

There are two tables; ST and SX both provide columns in the resulting table.

 

[MarcLodge]

OK, Mike that's made it a little clearer. I'll have a think about it and get back to you a little later on.

Marc

 

[blom0344]

If your table is self-referencing then you may have a recursive structure within the table. Perhaps you may need to look into recursive SQL. Anyway, I can't really fathom what you want to achieve. Set your hopes on Marc

Ties Blom

 

[BrianTyler]

Like Ties, i cannoet quite see you requirement.

Perhaps you could try left outer joins to alias tables.

This may be inefficient, so it depends on volumes.

Brian

(PS this is my last post to TT as I retire today)

 

[blom0344]

Brian,

Should we congratulate you on your retirement?
Anyway, I hope that you will enjoy this next phase in life!

Come to think of it, this should not automatically mean that you retire from TT, or does it!

Cheers!



Ties Blom

 

[MarcLodge]

Mike,
I still want to simplify your stucture and take it back to just 4 columns for the time being: AA, AB, XY, and XZ

From what you've said there is a chaining effect across the table where AA can link to either XY or XZ, although that's not always the case.

If I've got this correct you can have the following data:

AA AB XY XZ
== == == ==
AA1 AB1 null null
AA2 AB2 AA1 null
AA3 AB3 null AA1
AA4 AB4 AA2 AA3

From this, you wish to always display the value of AA and AB and if XY or XZ contain a value, you want to chain back to the relevant row and show the value of AB.

Have I got this correct?

If so, I think you could be looking at a LEFT OUTER JOIN.

Get back to me and let me know if I have understood the problem correctly.

Brian,
Pleased for you that you are retiring, although like Ties, I believe that should not stop you from posting on Tek Tips, although I guess that if you're lying on a beach in the Carribean, you might get sand in your laptop! Enjoy yourself Brian, and it's been a pleasure having your expert advice over the years.

Marc

 

[MIKELEIBO]

Sorry that I'm having trouble explaining this but you almost have it stated right... It must have been my error

If the rows look like this.

AA1,AB1,null,null
AA2,AB2,null,AA3
AA3,AB3,AA2,null

The result has to look like this.

AB XY XZ
== == ==
AB1 ' ' ' '
AB2 AB3 ' '
AB3 ' ' AB2

 

[MarcLodge]

Mike,

Not sure about the efficiency of this, but how about

SELECT A.AB, LOJ1.AB, LOJ2.AB
  FROM ST A
LEFT OUTER JOIN
     ST LOJ1
     ON A.AA = LOJ1.XY
LEFT OUTER JOIN
     ST LOJ2
     ON A.AA = LOJ2.XZ

Think this might give you what you require ie the base table joined to itself (twice) in order to find data that might or might not be present.

Let me if this is what you are looking for. The code is untested as I'm at home, so you may need to play around with the syntax to get it correct.

Marc

 

[sathyarams]

BrianTyler,
Good luck with your retiremnt ..

We'll be missing your expertise here.

Thanks for your time for the community

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp