我不想在结果为NULL的地方显示NULL [英] I don't want to show the NULL wherever the result is NULL
问题描述
我正在使用此代码。这个查询的输出是正常的，但是任何一个表中都没有的记录都是null，我想从输出中删除这个空值并使用它们的空间。
当前输出为： - >
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
我想要这种格式： - >
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
KIRT IP 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
我的尝试：
从
中选择*（
选择b.identity为客户代码，b.Name，count（b.invoice_no）为已完成发票，总和（b.Gross_Amount）为总金额来自
（选择不同的load_id，invoice_no来自CUST_ORDER_LOAD_LIST_INVOICE，其中order_no喜欢'COPN /％'
和invoice_no like 'DR1 / 18 /％'）a，
（选择invoice_no，creators_reference，identity，Name，Invoice_Date，creation_date，GUST_Amount from CUSTOMER_ORDER_INV_HEAD_UIV，其中contract ='PPR01'）b，
（选择不同的shipment_load_id，Gate_Pass_No，Gate_Pass_Date，identity，vehicle_no，order_no，header_state来自m GATE_PASS_DETAILS_V其中Trans_Source ='CO-LL'和contract ='PPR01'）c
其中a.invoice_no = b.invoice_no
和to_date（to_char（b.invoice_date） ，'DD / MM / YYYY'），'DD / MM / YYYY'）to_date（'& From_Date'，'DD / MM / YYYY'）和to_date（'& To_Date'，'DD / MM / YYYY） '）
和a.load_id = c.shipment_load_id
group by b.identity，b.Name
）m
完全外部加入
（
选择b.identity作为客户代码，b。名称，计数（b.invoice_no）作为待处理发票，总和（b.Gross_Amount）作为总金额来自
（选择不同的load_id，invoice_no来自CUST_ORDER_LOAD_LIST_INVOICE，其中order_no喜欢'COPN /％'
和invoice_no喜欢'DR1 / 18 /％'）a，
（选择invoice_no，creators_reference，identity，Name，Invoice_Date，creation_date，GUST_Amount from CUSTOMER_ORDER_INV_HEAD_UIV，其中contract ='PPR01'）b，
（选择distinct cargo_load_id，Gate_Pass_No，Gate_Pass_Date，ident来自GATE_PASS_DETAILS_V的ity，vehicle_no，order_no，header_state，其中Trans_Source ='CO-LL'且合约='PPR01'）c
其中a.invoice_no = b.invoice_no
和to_date（to_char（b.invoice_date，'DD / MM / YYYY'），'DD / MM / YYYY'）to_date（'& From_Date'，'DD / MM / YYYY'）和to_date（'& To_Date'）之间，'DD / MM / YYYY'）
和a.load_id = c.shipment_load_id（+）
和c.Gate_Pass_No为空
b.identity分组，b。名称
）t
on t。客户代码= m。客户代码
这是一种方法 - 您可以将null返回转换为您选择的值，例如空字符串。
NVL函数 - 替换NULL - Oracle到SQL Server迁移 - SQLines开源工具 [ ^ ]
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"解决方案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[^]
这篇关于我不想在结果为NULL的地方显示NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
