Join multiple Select Statements using an NVL Clause

Question

Hi I appreciate all help in advance here,

As I am reasonably new to SQL and the Join Types, looking for a little assistance here

Query 1

(select ic.ITEM_ID, ic.ITEM_NAME
from ITEM_CBO ic 
where COLOR_DESC = 'BRAND');

Query 2

(select ptt.ITEM_ID, TO_CHAR(MAX(DISTINCT(ptt.CREATE_DATE_TIME)), 'dd/mm/yyyy') as "Last Trans Date"
from PROD_TRKG_TRAN ptt
where ptt.TRAN_TYPE = 500 and ptt.TRAN_CODE = 010 and ptt.MENU_OPTN_NAME = 'LOA Pck Pick Cart'
Group By ptt.ITEM_ID);

Query 3

(select wi.ITEM_ID, sum(wi.ON_HAND_QTY)
from WM_INVENTORY wi
where wi.ITEM_ID in (SELECT ic.ITEM_ID from ITEM_CBO ic where COLOR_DESC = 'BRAND')
group by wi.ITEM_ID);

I am trying to join the results of the above queries to return the following

all results of from Query 1 (Column ic.ITEM_NAME) matching results from Query 2 (Column TO_CHAR(MAX(DISTINCT(ptt.CREATE_DATE_TIME)), 'dd/mm/yyyy') as "Last Trans Date") Query 3 (sum(wi.ON_HAND_QTY)

All Joins Can be performed using the ITEM_ID in each Sub Query,

Not really Sure how to include the NVL to account for non existent records in Query 2 and 3

Answer 1

I don't think it is NVL you're looking for, but outer join.

Queries you posted can be used as CTEs.

So:

with 
q1 as
  (select ic.ITEM_ID, ic.ITEM_NAME
   from ITEM_CBO ic 
   where COLOR_DESC = 'BRAND'),
q2 as
  (select ptt.ITEM_ID, 
     TO_CHAR(MAX(ptt.CREATE_DATE_TIME), 'dd/mm/yyyy') as last_trans_date
   from PROD_TRKG_TRAN ptt
   where ptt.TRAN_TYPE = 500 
     and ptt.TRAN_CODE = '010' 
     and ptt.MENU_OPTN_NAME = 'LOA Pck Pick Cart'
   group By ptt.ITEM_ID
  ),
q3 as
  (select wi.ITEM_ID, 
     sum(wi.ON_HAND_QTY) as sum_on_hand_qty
   from WM_INVENTORY wi
   where wi.ITEM_ID in (SELECT ic.ITEM_ID 
                        from ITEM_CBO ic 
                        where COLOR_DESC = 'BRAND')
   group by wi.ITEM_ID
  )
-- final query
select a.item_id, a.item_name, b.last_trans_date, c.sum_on_hand_qty
from q1 a left join q2 b on a.item_id = b.item_id
          left join q3 c on c.item_id = a.item_id