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.

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 Received on Tue Feb 10 2004 - 22:09:02 CST