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.

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

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

WITH PRV_DB AS 
(
SELECT DISTINCT B.SUPPLIER_ID .....
, CUR_DB AS 
( 
/* Current Cost Values*/
SELECT DISTINCT B.SUPPLIER_ID....
)
Select ...

Open in new window

Karen SchaeferBI ANALYSTAuthor Commented:
sorry what is the correct syntax for the combines select statement?  The and was not part of the code!!  I have two separate with statements 1 called Prev_DB the other Cur_db.

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?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Karen SchaeferBI ANALYSTAuthor Commented:
Thanks, no I need to combine the two with to create a single record for each supplier?
slightwv (䄆 Netminder) Commented:
This sounds like your previous question where you said you had a solution:
https://www.experts-exchange.com/questions/29087571/Limit-query-results-by-quarters.html

If you plan to use Common Table Expressions (CTE), you will be accessing the same table at least twice.  Based on your previous question this will likely not be the most efficient way to solve your problem.

Using CTE is just like using inline views.  It is really no different.

Based on the previous question, PRV returns one row and CUR returns one row and you want two rows.  You need a UNION ALL:
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
union all
select * from cur_db
/

Open in new window


Still probably not the best solution to your problem.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Won't Union All give me two records per supplier, instead of a single record that includes all data from tbl1 & tbl2?
slightwv (䄆 Netminder) Commented:
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:
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
/

Open in new window


output:
JOIN_COLUMN JUNK  JOIN_COLUMN JUNK
----------- ----- ----------- -----
          1 Hello           1 World

Open in new window

PortletPaulEE Topic AdvisorCommented:
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.
Karen SchaeferBI ANALYSTAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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.
Mark GeerlingsDatabase AdministratorCommented:
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):
Supplier		Columns from Prv Qtr		Columns from Cur Qtr
ABC			50		$100			60		$120
DEF			25		$200			35		$140

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
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.
PortletPaulEE Topic AdvisorCommented:
Without some reliable sample data from each table separately (not already combined by a query) I am almost certain you do not need to use "with" at all. It seems that your desired result is just a simple join, combined with a relevant where clause, like this:

SELECT
      p.Supplier_ID 
    , p.Supplier_Name 
    , p.StartDate 
    , p.EndDate  
    , p.Qtr   
    , p.Label    
    , p.Cat Color 
    , p.COMMENTS
    , c.Supplier_ID    cur_Supplier_ID
    , c.Supplier_Name  cur_Supplier_Name
    , c.StartDate      cur_StartDate
    , c.EndDate        cur_EndDate
    , c.Qtr            cur_Qtr
    , c.Label          cur_Label
    , c.Cat            cur_Color
    , c.COMMENTS       cur_COMMENTS
FROM PRV_DB p
INNER JOIN CUR_DB c ON p.SUPPLIER_ID = c.SUPPLIER_ID
      AND p.SUPPLIER_CD = c.SUPPLIER_CD
      AND p.PREVIOUS = c.CURRENT
WHERE p.Qtr = '2017-Q4' and c.Qtr = '2018-Q1'

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.