Re: SQL query help (long)

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 11 Feb 2004 09:35:40 +0100
Message-ID: <4029e8d5$0$28158$626a14ce@news.free.fr>

<Oracle-remthis-Support_at_shaw.ca> a écrit dans le message de news:o03j20p76t20sfseko8qaiaqc5sd67e8ar_at_4ax.com...
> A little too tired to really show this well, but as I need to get this
> resolved here goes (translated from actual schema which I don't have
> in front of me, but I believe this is the basic design)...
>
> Background:
>
> Oracle 8i database(s) - 3 similar databases with different companies
> Visual Studio reporting tool (all servers running W2K)
> Current reports done in MS Access (I had not part in this ;-)
> Each database contains the same schema design, but different data.
>
> Requirement:
>
> I now have the task to create reports that can view / compare data
> from any of the databases (I have full control over the local
> database, but read-only access to the two client databases). It is
> expected that the reporting will not be done via MS Acess (I did have
> a part in that decision). I can create the basic reports without any
> problem, but the queries are giving me fits (without resorting to
> creating temp tables - which I may not be able to do on the client's
> database).
>
> Table Structure:
>
> TableA: seqnoA (primary key)
> dataA1
> (more columns)
>
> TableB: seqnoB (primary key)
> seqnoA
> dataB1
> (more columns)
>
> TableC: seqnoC (primary key)
> dataC1
> dataB1
> dataB2
> (more columns)
>
> TableD: dataC1 (primary key)
> dataD1 (primary key)
> dataD2
>
> TableE: seqnoE (primary key)
> seqnoC
> dataE1
>
> TableD for each dataC1 value there are two (and only two) dataD1
> values (0 and 1)
>
> Problem(s):
>
> I need the query to return the following:
>
> select seqnoA, dataA1, seqnoC, (dataD2 where dataD1=0), (dataD2 where
> dataD1=1), ('DOWN' if dataB2 like 'd%' else dataB2), (concat dataE1,
> dataE1, dataE1, ...)
> from TableA a, TableB b, TableC c, TableD d, TableE e
> where a.seqnoA=b.seqnoA and b.dataB1=c.dataB1 and d.dataC1=c.dataC1
> and e.seqnoC=c.seqnoC
>
>
> Hopefully that's understandable.
>
> 1) I can't figure out how to output the two dataD2 on the same line:
>
> ... seqnoC dataD2 dataD2 ...
>
> ... 14 oracle sqlserver ...
>
>
> Can this be done without resorting to a temporary table? How?
>
> 2) how can I substitute a value for dataB2 if the condition is met?
>
> and
>
> 3) how can I concatenate all the dataE1 values for each seqnoC
>
> Unfortunately I been doing more sysadmin work than Oracle over the
> past several years, so the SQL is really rusty.
>
> Any help much appreciated (or if more clarification is required please
> let me know)
>
> Thanks.
>
> Brad

If you know the max number of dataE1, you can do something like:

create or replace function concatE (seqno number) return varchar2 is

   retval varchar2 := '';
   sep varchar2 := '';
begin

   for rec in (select dataE1 from tableE where seqnoC = seqno) loop

      retval := retval || sep || rec.dataE1;
      sep = ' ';

   end loop;
   return retval;
end;
/
select a.seqnoA, a.dataA1, c.seqnoC, d.dataD20, d.dataD21,

   decode(substr(c.dataB2,1,1),'d','DOWN',c.dataB2) dataB2,    concatE(c.seqnoC) dataE
from tableA a, tableB b, tableC c,

   ( select d.dataC1, max(decode(d.dataD1,0,d.dataD2,'') dataD20,

           max(decode(d.dataD1,1,d.dataD2,'') dataD21
     from dataD1 d group by d.dataC1 ) d

where b.seqnoA = a.seqnoA and c.dataB1 = b.dataB1 and d.dataC1 = c.dataC1 /

I don't see how you can avoid the function.

Regards
Michel Cadot Received on Wed Feb 11 2004 - 02:35:40 CST