I have two select queries, where query A has all the data and I have another query with some data. We need to fetch the uncommon data from both query records. Let's say (query A - query B).
Please find below example with existing data and expected output.
Query A:
SELECT meaning
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP';
Output of Query A:
Meaning |
---|
Autoinvoice Import Program |
Purge Concurrent Request and/or Manager Data |
Create Intercompany AP Invoices |
Rollup Cumulative Lead Times |
Record Order Management Transactions |
Query B:
SELECT DISTINCT fcs.program
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_responsibility_tl frl,
apps.fnd_user fu,
apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code = 'P'
AND fcr.request_id = fcs.request_id
AND frl.language = 'US'
AND fcr.requested_by = fu.user_id
AND fcr.responsibility_id = frl.responsibility_id
AND fcr.status_code IN ('P','Q')
AND fcp.language = 'US'
AND fcp.source_lang = 'US'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.requested_start_date >= SYSDATE
AND fcs.program IN (SELECT meaning FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP');
Output of query B:
Program |
---|
Autoinvoice Import Program |
Create Intercompany AP Invoices |
Record Order Management Transactions |
Below is the final expected output (fetch the uncommon data from both queries - query A which will have all the data will be minus from query B records):
Meaning |
---|
Purge Concurrent Request and/or Manager Data |
Rollup Cumulative Lead Times |
Can it be achieved from INNER JOIN
/OUTER JOIN
/EXPECT
/UNION
or anything else?
"query A - query B" is actually a great way of describing it, as Oracle has a minus
set operator:
SELECT meaning -- This is query A
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP';
MINUS -- Set operator here
SELECT fcs.program -- This is query B, with the DISTINCT modifier removed
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_responsibility_tl frl,
apps.fnd_user fu,
apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code = 'P'
AND fcr.request_id = fcs.request_id
AND frl.language = 'US'
AND fcr.requested_by = fu.user_id
AND fcr.responsibility_id = frl.responsibility_id
AND fcr.status_code IN ('P','Q')
AND fcp.language = 'US'
AND fcp.source_lang = 'US'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.requested_start_date >= SYSDATE
AND fcs.program IN (SELECT meaning FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP');