Re: SQL query help (long)

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

It might be, but I prefer your posting the actual and correct create table statements (as they are always more accurate as any human language description of the layout) as well as some insert statements.

After doing so, you might use human language to describe what you want or why that is or whatever. Optionally, you can also append a sample output of the query looked for.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch