-
-
select ob.amount from BRDB_SU_OPENING_BALANCE ob
-
where ob.BRANCH_ACCOUNTING_CODE=654321
-
AND ob.STOCK_UNIT='UT1'
-
AND ob.TRADING_PERIOD=101
-
AND ob.BALANCE_PERIOD=1
-
AND ob.PROD_ID=654
-
-
select nvl(ds.amount,0) from BRDB_DAILY_CUMULATIVE_SUMMARY ds
-
where ds.BRANCH_ACCOUNTING_CODE=654321
-
AND ds.STOCK_UNIT='UT1'
-
AND ds.TRADING_PERIOD=101
-
AND ds.BALANCE_PERIOD=1
-
AND ds.PROD_ID=654
-
AND ds.TRADING_DATE=(SELECT MAX(TRADING_DATE) from BRDB_DAILY_CUMULATIVE_SUMMARY
-
where BRANCH_ACCOUNTING_CODE=654321
-
AND STOCK_UNIT='UT1'
-
AND TRADING_PERIOD=101
-
AND BALANCE_PERIOD=1)
-
-
select nvl(sum(sd.amount),0) from BRDB_RX_REP_SESSION_DATA sd
-
where sd.BRANCH_ACCOUNTING_CODE=654321
-
AND sd.STOCK_UNIT='UT1'
-
AND sd.TRADING_PERIOD=101
-
AND sd.BALANCE_PERIOD=1
-
AND sd.PROD_ID=654
-
AND sd.TRADING_DATE=
-
-
I want to convert these three queries into one. So that I can add the amount of all the three queries and return one column back. How should I go about doing this?
The first query will definitely return me one record and only one.
The second query may or may not return any records. But if there are records for the combination given in the query then I want to fetch the one having the latest date (trading_date) and that is why I have a subquery. Can this be achieved without using sub-query?
The third query may or may not return any records. But if there any records then it should retrieve them where trading_date is > the max of trading date found in second query. But if there are no records from the second query then it should retrieve records where trading_date >= insert_timestamp from the first query table. Currently I don’t even how I can do this separately and that is the reason I have left the last AND as blank.
I think if we combine the three queries into one then we need outer-joins for the tables given in second and third queries (BRDB_DAILY_CUMULATIVE_SUMMARY, BRDB_RX_REP_SESSION_DATA).
