Is this the place to ask this?

Toad for Oracle
1

I hope someone will be kind enough to help me even if this is the wrong place to ask a question like this.

I’ve got a union query and I need to return all rows that match the results of another query, I just can’t figure out how to use From and Where in this particular situation.

The Union query pulls all rows from two tables and gives me combined results

The second query compares two other tables and finds rows where a certain value does not = zero

How can I get all the rows (multiple for each) from the first query based on the results of the second where I’ve created a virtual TBL1 with a UNION.

here is a snippit of the code showing the gist of the whole thing (I just took out a bunch of details). I can post the entire code if needed.

Thanks!

SELECT DISTINCT

   TBL1.Record_Source,

FROM (SELECT ‘Revenue’ Record_Source,

     UNION ALL

SELECT ‘Manual’ Record_Source,)

TBL1

??? Select multiple records from above based on results of query below

– section two

SELECT ‘Manual’ Record_Source,

FROM ER.ER_PARTY_TRANSACTIONS A,

( SELECT ‘Revenue’ Record_Source,

FROM ER.ER_PARTY_TRANSACTIONS A,

     WHERE   D.TRANSACTION_COMPONENT = 'NET'

) ORIGINAL

WHERE D.TRANSACTION_COMPONENT = ‘NET’

   AND (ORIGINAL.COMPARE_AMT - D.COMPONENT_AMT) <> 0
2

I’m not 100% sure that I understand what you are asking, but I’ll try to answer anyway.

If you are trying to join these two queries (the green and the yellow sections) you could do it using a WITH, like this:

with inds as

(select *

 from dba_indexes

 where owner = 'SYS'),

tabs as

(select *

 from dba_tables

 where owner = 'SYS')

select tabs.owner,

  tabs.table_name,

  inds.owner as index_owner,

  inds.index_name

from tabs, inds

where tabs.owner = inds.table_owner

and tabs.table_name = inds.table_name

So in this example above, there are two separate queries in green and yellow, then I joined them with the blue section.

3

John, I think you understood my question perfectly. “With” appears to be exactly the key to solving my problem.

Since I’m just an accountant, with a very small amount of SQL knowledge, I don’t know if I ever would have stumbled on “with” - thank you very much for your help.