在sql表中创建视图时出现NULL值问题

I am working on a SQL View over a table with different values in it. I am trying to combine two columns in a table to be displayed in a single column of a view separated by a '/'.

我正在一个具有不同值的表上的SQL视图。我正在尝试将表中的两列组合在一个以“/”分隔的视图的单个列中。

For Example I have two columns in a table named In and inVolume with values for 'In' being 1,2,NULL

例如,我在名为In和inVolume的表中有两列,其中'In'的值为1,2,NULL

and for 'inVolume ' being

对于'inVolume'而言

NULL, 100, 200

NULL,100,200

and the results I was expecting are 1/(NULL or Empty), 2/100, (NULL or Empty)/200 but when I created the view and ran it the results were (NULL or Empty), 2/100, (NULL or Empty). The issue being it is making the column in view as NULL if any of the columns In or inVolume in the table are NULL.

我期待的结果是1 /(NULL或空),2/100,(NULL或空)/ 200但是当我创建视图并运行它时,结果是(NULL或空),2/100,(NULL或空)。问题是,如果表中的任何列In或inVolume为NULL,则视图中的列为NULL。

I created the following view

我创建了以下视图

SQL View

CREATE VIEW [dbo].[Result]
AS

with CTE as(
SELECT distinct
   CC.Product,
   CC.Term,   
   CC.TermID,
   iCC.In,
   iCC.Out,
   iCC.inVolume,
   iCC.outVolume

   FROM Cust CC
CROSS APPLY (SELECT TOP 3
                In,
                Out, 
               inVolume,
                outVolume

             FROM Cust iCC
             WHERE CC.Term = iCC.Term and CC.Product = iCC.Product
             ORDER BY iCC.In DESC, iCC.Out ASC) iCC)
             select Product, Term, cast(inVolume as varchar(99)) + '/' + cast(In as varchar(99)) as value, inlabel as label from CTE 
             union
             select Product, Term, cast(Out as varchar(99)) + '/' + cast(outVolume as varchar(99)), outlabel from CTE 

GO

Any better way to deal with this?

有没有更好的方法来处理这个?

3 个解决方案

#1

Replace this:

cast(outVolume as varchar(99))

Whit this:

cast((CASE WHEN outVolume IS NULL THEN 0 ELSE outVolume END) as varchar(99))

On every field. Hope this help.

在每个领域。希望这有帮助。

#2

Use IsNull to enable nullable-column concatentation

使用IsNull启用可空列连接

select Product, Term, 
IsNull(inVolume, '(NULL OR EMPTY)', cast(inVolume as varchar(99))) + '/' + 
IsNull(In, '(NULL OR EMPTY)', cast(In as varchar(99))) as value, 
inlabel as label from CTE 

UNION

select Product, Term, 
IsNull(Out, '(NULL OR EMPTY)', cast(Out as varchar(99))) + '/' + 
IsNull(outVolume, '(NULL OR EMPTY)', cast(outVolume as varchar(99))), 
outlabel from CTE 

#3

Replace

cast(inVolume as varchar(99)) + '/' + cast(In as varchar(99))` 

with

cast(inVolume as varchar(99)) + '/' 
+ ISNULL(cast(In as varchar(99)), '(NULL OR EMPTY)')`

and

cast(Out as varchar(99)) + '/' + cast(outVolume as varchar(99))` 

with

cast(Out as varchar(99)) + '/' 
+ ISNULL(cast(outVolume as varchar(99)), '(NULL OR EMPTY)')