CODEWITHSUNDEEP
sqloracle-databaseunion-all
Dekks
Dekks

Reputation: 137

Joining output of multiple queries

I've been trying to join the output of two queries that have a common field, I've gone over several examples people have provided in similar questions and answers before but can't seem to get them to work for me.

I have two queries, that basically provided Account_ID, Specific_Transaction_Type_Amount, Transaction_Date as output from the first query, and then second query outputs Account_ID, Account_Value.

The second query is a little complicated as it has to look up a account item id, the most recent price date for that item, the price for that price date, and then sum the quantity of that item, times the price of the item grouped by account id.

So the first query looks somewhat like this:

Select distinct
ACCOUNT_NUMBER
,TrANSACTION_AMOUNT
,TRANSACTION_DATE
from CLIENT CL
left join
CLIENT_ACCOUNT_LINK CAL on CL.CLIENT_ID = CAL.CAL_CLIENT_ID
left join
ACCOUNT A on CAL.CAL_ACCOUNT_ID = A.ACCOUNT_ID
left join
ACCOUNT_TYPE AT on A.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
left join
ACCOUNT_TRANSACTION ATR on A.ACCOUNT_ID = ATR.ACCOUNT_ID
WHERE 
AT.ACCOUNT_TYPE = 'ACCOUNTYPE2'
AND ATR.TRANSACTION_TYPE = 'TRANSACTIONTYPE6'

The second query looks something like this:

select distinct
A.ACCOUNT_NUMBER
,SUM(AB.ITEM_QTY * PR.ITEM_PRICE) OVER (PARTITION BY A.ACCOUNT_ID) as                     TotalValue
from ACCOUNT A 
join ACCOUNT_BALANCE AB on A.ACCOUNT_ID = AB.ACCOUNT_ID
join ITEM_LIST IL on AB.ITEM_ID = IL.ITEM_ID
join PRICE_DATE PD on IL.ITEM_ID= PD.ITEM_ID
join CODE_LOOKUP cODE on PD.PRICE_STATUS_CODE = CODE.CODE_ID AND CODE.DESCRIPTION = 'ACTIVE'
AND PD.PRICE_DATE = (SELECT MAX(PD2.PRICE_DATE)
FROM PRICE_DATE PD2
WHERE PD2.ITEM_ID = PD.ITEM_ID
AND PD2.PRICE_STATUS_CODE = CODE.CODE_ID
AND sprcp.prcp_effective_date < sysdate + 1)
join PRICE PR on PD.PRICE_ID = PRICE_PRICE_DATE_ID
join PRICE_TYPE PT on PR.PRICE_TYPE_ID = PT.PRICE_TYPE_ID

So I get two results:

ACCOUNT_ID    TRANSACTION_AMOUNT    TRANSACTION_DATE
_____________________________________________________
123456789      500.23                 07/02/2019

ACCOUNT_ID    TOTAL_VALUE
____________________________
123456789      24365.48

I'd like the TOTAL_VALUE column from query 2 to appear in query 1. If I merge the two queries into one, the query runs for a long time before I get a out of memory error. If I run them separately they each take a couple of minutes but I get the results without any problem.

I did try based on earlier examples doing something like:

SELECT *
FROM (QUERY1) AS Q1
LEFT JOIN (QUERY2) AS Q2
on Q1.A.ACCOUNT_ID = Q2.A.ACCOUNT_ID

I get a syntax error when I try to run this query, it specifically doesn't seem to like the query alias?

Upvotes: 0

Views: 37

Answers (1)

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

Try 

with 
q2 (select distinct
            A.ACCOUNT_NUMBER
            ,SUM(AB.ITEM_QTY * PR.ITEM_PRICE) OVER (PARTITION BY A.ACCOUNT_ID) as                     TotalValue
            from ACCOUNT A 
            join ACCOUNT_BALANCE AB on A.ACCOUNT_ID = AB.ACCOUNT_ID
            join ITEM_LIST IL on AB.ITEM_ID = IL.ITEM_ID
            join PRICE_DATE PD on IL.ITEM_ID= PD.ITEM_ID
            join CODE_LOOKUP cODE on PD.PRICE_STATUS_CODE = CODE.CODE_ID AND CODE.DESCRIPTION = 'ACTIVE'
            AND PD.PRICE_DATE = (SELECT MAX(PD2.PRICE_DATE)
            FROM PRICE_DATE PD2
            WHERE PD2.ITEM_ID = PD.ITEM_ID
            AND PD2.PRICE_STATUS_CODE = CODE.CODE_ID
            AND sprcp.prcp_effective_date < sysdate + 1)
            join PRICE PR on PD.PRICE_ID = PRICE_PRICE_DATE_ID
            join PRICE_TYPE PT on PR.PRICE_TYPE_ID = PT.PRICE_TYPE_ID),
    q1 as (Select distinct
                ACCOUNT_NUMBER
                ,TrANSACTION_AMOUNT
                ,TRANSACTION_DATE
                from CLIENT CL
                left join
                CLIENT_ACCOUNT_LINK CAL on CL.CLIENT_ID = CAL.CAL_CLIENT_ID
                left join
                ACCOUNT A on CAL.CAL_ACCOUNT_ID = A.ACCOUNT_ID
                left join
                ACCOUNT_TYPE AT on A.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
                left join
                ACCOUNT_TRANSACTION ATR on A.ACCOUNT_ID = ATR.ACCOUNT_ID
                WHERE 
                AT.ACCOUNT_TYPE = 'ACCOUNTYPE2'
                AND ATR.TRANSACTION_TYPE = 'TRANSACTIONTYPE6')
        select * from q1
          join q2  on q1.account_number = q2.account_number

Upvotes: 2

Related Questions