Re: A tricky SQL statement

From: Clemens Li <Clemens.Li_at_oscar.north.de>
Date: 1997/01/30
Message-ID: <6PrwTBSvN1B@oscar.north.de>#1/1

Hi folks,

Anders Hattestad presented this problem to us:

> I have a table that looks like this:
>
> sident mident dato m00
> M201 B1 07-16-95 2983
> M201 B2 07-16-95 2996
> M201 L1 07-16-95 2956
> M201 RF 07-16-95 3
> M201 VH 07-16-95 1
> M201 VR 07-16-95 33
> M251 B1 11-22-93 2555
> M251 L1 11-22-93 2535
> M251 RF 11-22-93 88
> M251 VH 11-22-93 6
> M251 VR 11-22-93 7
> M253 B1 11-30-93 2658
>
> And I would like to extract it on this form:
>
> Sident B1 B2 L1 RF
> M201 2983 2996 2956 3
> M251 2535 88
> M253 2658
>

Although I am a complete SQL newbie (Just did 2 weeks Oracle/SQL training), I guess from the original table that the result should be:

SIDENT B1 B2 L1 RF
-------- -------- -------- -------- --------

M201     2983     2996     2956     3
M251     2555              2535     88
M253     2658

Did you mess it up, Anders? Otherwise you want an SQL statement too tricky for primitive human beings ;-)

And following the logic, a complete result table should be:

SIDENT B1 B2 L1 RF VH VR -------- -------- -------- -------- -------- -------- --------

M201     2983     2996     2956     3        1        33
M251     2555              2535     88       6        7
M253     2658

Well, my solution to the problem is:

Use DISTINCT function for Column SIDENT to get each value exactly once. Rename the the m00 column to B1, B2, L1 and so on using multiple auto-joins with outer joins.

If the original data are stored in table dta, the SQL statement is

SELECT DISTINCT(dta.sident) SIDENT,b1.m00 B1,b2.m00 B2,l1.m00 L1, rf.m00 RF,

       vh.m00 VH, vr.m00 VR
    FROM dta,dta b1,dta b2,dta l1,dta rf,dta vh, dta vr     WHERE

        b1.mident(+)='B1' AND dta.sident=b1.sident(+)
    AND b2.mident(+)='B2' AND dta.sident=b2.sident(+)
    AND l1.mident(+)='L1' AND dta.sident=l1.sident(+)
    AND rf.mident(+)='RF' AND dta.sident=rf.sident(+)

    /* the following two lines are optional output cols VH and VR */     AND vh.mident(+)='VH' AND dta.sident=vh.sident(+)     AND VR.mident(+)='VR' AND dta.sident=VR.sident(+)     ORDER BY dta.sident;

It produced the table above.
Lacking experience, I don't know If you get a performance advantage if you use a DECODE construction instead.

cu, Clemens

• * * * * * * * * * * on the net since tuesday * * * * * * * * * * * * * _P.S._ Answer and/or comment _please_ (also) as follow-up-to: poster

Received on Thu Jan 30 1997 - 00:00:00 CST