Join 2 with statements into a single query
How do I join two separate "With" statements into one query to create one single record per supplier?
Both With statements work separately now I need to join the to using a Left Outer Join.
Both With statements work separately now I need to join the to using a Left Outer Join.
SELECT PRV_DB.*, CUR_DB.*
FROM PRV_DB, CUR_DB
WHERE PRV_DB.SUPPLIER_ID = CUR_DB.SUPPLIER_ID
AND PRV_DB.SUPPLIER_CD = CUR_DB.SUPPLIER_CD
AND PRV_DB.PREVIOUS = CUR_DB.CURRENT;
WITH PRV_DB AS
(
SELECT DISTINCT B.SUPPLIER_ID .....
and
WITH CUR_DB AS
(
/* Current Cost Values*/
SELECT DISTINCT B.SUPPLIER_ID.....SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
do you mean Union ?
you need to have both queries to have fields identical.
The UNION [ALL], INTERSECT, MINUS Operators
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
you need to have both queries to have fields identical.
The UNION [ALL], INTERSECT, MINUS Operators
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
ASKER
Thanks, no I need to combine the two with to create a single record for each supplier?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Won't Union All give me two records per supplier, instead of a single record that includes all data from tbl1 & tbl2?
A "union all" will give you ALL the records from query1 and ALL the records from query2.
That is what you wanted from your original question. This is why you should try to include sample data and expected results. Then we can see what you are after.
If you want the data on the same row, then it is a join:
output:
That is what you wanted from your original question. This is why you should try to include sample data and expected results. Then we can see what you are after.
If you want the data on the same row, then it is a join:
WITH PRV_DB AS
(
select 1 join_column, 'Hello' junk from dual
)
, CUR_DB AS
(
select 1 join_column, 'World' junk from dual
)
select * from prv_db p join cur_db c on p.join_column = c.join_column
/output:
JOIN_COLUMN JUNK JOIN_COLUMN JUNK
----------- ----- ----------- -----
1 Hello 1 World
I suggest you ignore "with" completely as it really isn't important.
What is important is understanding what you want to achieve and the best way to do that is to provide:
1 sample data (for each table), and
2 the expected result
With those 2 items we can propose a efficient solution.
What is important is understanding what you want to achieve and the best way to do that is to provide:
1 sample data (for each table), and
2 the expected result
With those 2 items we can propose a efficient solution.
ASKER
Attached is a example of the data
1. the results of a UNION ALL query - still returns rows of data mixing previous & current data together.
2. The results I am seeking.
example.xlsx
1. the results of a UNION ALL query - still returns rows of data mixing previous & current data together.
2. The results I am seeking.
example.xlsx
Did you see my example in #a42492061?
Without actual RAW sample data, I have no way to set up a test to provide tested SQL that generates your expected results.
If you want 100% tested SQL, you need to provide us with raw data and the expected results for the raw data you provide.
We only need enough raw data for the results you want.
Without actual RAW sample data, I have no way to set up a test to provide tested SQL that generates your expected results.
If you want 100% tested SQL, you need to provide us with raw data and the expected results for the raw data you provide.
We only need enough raw data for the results you want.
Without complete examples from you of what your data looks like, and exactly what results you want, we have to guess a bit. It sounds like you've learned a bit about Oracle's "with" sub-queries and you assume that is the best approach for your current business problem. I and others here are not convinced that is true. We suspect that you need a "union" much more than multiple "with" sub-queries.
If you want a report something like this, you don't need "with" queries at all (but they can be very helpful for some problems):
If you want a report something like this, you don't need "with" queries at all (but they can be very helpful for some problems):
Supplier Columns from Prv Qtr Columns from Cur Qtr
ABC 50 $100 60 $120
DEF 25 $200 35 $140
Also, in your "with" queries, you start with "select distinct...". I really don't like seeing that syntax in Oracle queries! Yes, it is legal. And, sometimes it even returns the results you expect. But, this forces a sort operation (a performance penalty) that you may not need.
Usually the better options in Oracle to return "distinct" results include one or more of these:
1. Base your query on a table that has only the distinct records you want
2. Include enough "where" clause conditions to exclude possible duplicates
3. Use a group operator (sum, count, min, max, etc.) on the date and numeric columns you want included, and a "group by" on the column(s) where you want only distinct values returned.
Usually the better options in Oracle to return "distinct" results include one or more of these:
1. Base your query on a table that has only the distinct records you want
2. Include enough "where" clause conditions to exclude possible duplicates
3. Use a group operator (sum, count, min, max, etc.) on the date and numeric columns you want included, and a "group by" on the column(s) where you want only distinct values returned.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So what is the correct syntax to combine both and do I need to select both with statements within my code to get the single select statement to work?