Click here to Skip to main content
15,881,852 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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"
Posted
Updated 11-Dec-18 0:39am

1 solution

This is one way - you can convert null returns into a value of your choice, such as an empty string.

NVL Function - Replace NULL - Oracle to SQL Server Migration - SQLines Open Source Tools[^]


 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900