I am using this code .Out put of this query is ok but records which are not present in either table are coming null and I want to remove this null values from out put and use their space.
Current out put is :-->
SAND-P 2 SAND-P 2
AIPL-P 4 AIPL-P 11
TMLW-P 6 TMLW-P 10
TIPL-P 2
ASHI-P 1
KIRTI-P 1
ADIT-P 2
AAPL-P 1
ATPL-P 3
EMEN-P 1
TACOS-P 10
MASA-P 1
YNGR-P 1
OKAY-P 6
SPDT-P 11
POLS-P 3
POAE-P 4
TACOCH-P 1
RACL-P 2
JTSV-P 1
HPPL-P 1
ATOT-P 1
I want out put in this format:-->
SAND-P 2 SAND-P 2
AIPL-P 4 AIPL-P 11
TMLW-P 6 TMLW-P 10
TIPL-P 2 JTSV-P 1
ASHI-P 1 HPPL-P 1
KIRTI-P 1 ATOT-P 1
ADIT-P 2
AAPL-P 1
ATPL-P 3
EMEN-P 1
TACOS-P 10
MASA-P 1
YNGR-P 1
OKAY-P 6
SPDT-P 11
POLS-P 3
POAE-P 4
TACOCH-P 1
RACL-P 2
What I have tried:
Select * from
(
select is b.identity as "Customer Code",b.Name,count(b.invoice_no) as "Completed Invoices",sum(b.Gross_Amount) as "Gross Amount" from
(select distinct load_id,invoice_no from CUST_ORDER_LOAD_LIST_INVOICE where order_no like 'COPN/%'
and invoice_no like 'DR1/18/%') a,
(select invoice_no,creators_reference,identity,Name,Invoice_Date,creation_date,Gross_Amount from CUSTOMER_ORDER_INV_HEAD_UIV where contract='PPR01') b,
(select distinct shipment_load_id,Gate_Pass_No,Gate_Pass_Date,identity,vehicle_no,order_no,header_state from GATE_PASS_DETAILS_V where Trans_Source='CO-LL' and contract='PPR01') c
where a.invoice_no=b.invoice_no
and to_date( to_char(b.invoice_date,'DD/MM/YYYY'),'DD/MM/YYYY') between to_date('&From_Date','DD/MM/YYYY') and to_date('&To_Date','DD/MM/YYYY')
and a.load_id=c.shipment_load_id
group by b.identity,b.Name
) m
full outer join
(
select b.identity as "Customer Code",b.Name,count(b.invoice_no) as "Pending Invoices",sum(b.Gross_Amount) as "Gross Amount" from
(select distinct load_id,invoice_no from CUST_ORDER_LOAD_LIST_INVOICE where order_no like 'COPN/%'
and invoice_no like 'DR1/18/%') a,
(select invoice_no,creators_reference,identity,Name,Invoice_Date,creation_date,Gross_Amount from CUSTOMER_ORDER_INV_HEAD_UIV where contract='PPR01') b,
(select distinct shipment_load_id,Gate_Pass_No,Gate_Pass_Date,identity,vehicle_no,order_no,header_state from GATE_PASS_DETAILS_V where Trans_Source='CO-LL' and contract='PPR01') c
where a.invoice_no=b.invoice_no
and to_date( to_char(b.invoice_date,'DD/MM/YYYY'),'DD/MM/YYYY') between to_date('&From_Date','DD/MM/YYYY') and to_date('&To_Date','DD/MM/YYYY')
and a.load_id=c.shipment_load_id(+)
and c.Gate_Pass_No is null
group by b.identity,b.Name
) t
on t."Customer Code" = m."Customer Code"