Re: SQL query help (long) - Thanks

On Wed, 11 Feb 2004 09:35:40 +0100, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
><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)...
>>
>>...
>> 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
>

Michel,

Thanks for that, what you wrote re-focused my thoughts. Unfortunately I'm in one of the jobs where we (my group) handles "full" 2nd and 3rd tier support - Oracle, MS SQL, Sybase, app support, etc., so sometimes getting focused on a single task requires extra effort (especially when over-worked).

I gave up trying to do this completely without functions, procedures, etc due to not "owning" two of the databases being used. Soon as I saw what you wrote, it was a Doh! moment. I was using some temp tables because on several queries the joins were on several million row tables and often the reports were re-used quite often. I found the response time was acceptable when creating them in-line as only one of the tables was being pulled over the WAN.

Anyway, everything is running and I only have a couple of functions for my concatenations and was able to get one of the other database owners to agree to insert the functions in his database. The other database owner will pretty much have to agree due to this "being in the best interest of all parties".

Now that it's running, I'm sure I'll get another requirements analysis that will conflict with what they already gave me.

Such is the life in IT.

Thanks again,

Brad Received on Sat Feb 14 2004 - 12:04:46 CST