Solved

How to return blank instead of NULL

Posted on 2011-03-17
13
359 Views
Last Modified: 2012-05-11
Hi Experts

How do I return a blank field instead of NULL in this query please?

case T0.[U_GarmentTID1]
when '1000' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1100' then ''
else ''
end 'Product',

case T0.[U_GarmentTID1]
when '1100' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1000' then ''
else '-'
end 'Hidden Product',

Regards
Jon
0
Comment
Question by:JonYen
  • 3
  • 2
  • 2
  • +3
13 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 143 total points
ID: 35155815
not sure I got yours question coreestly or not ?


else ''
will enter blank
to enter null you need to put it like
else null
0
 
LVL 4

Assisted Solution

by:LeDaouk
LeDaouk earned 72 total points
ID: 35155827
the case syntax should be like this:
CASE WHEN FieldName IS NULL THEN ..... ELSE .... END
and if you need you can nest in the else part another case
0
 
LVL 4

Assisted Solution

by:qasim_md
qasim_md earned 143 total points
ID: 35155847
try this:::

case T0.[U_GarmentTID1]
when '1000' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1100' then ' '
else ' '
end 'Product',

case T0.[U_GarmentTID1]
when '1100' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1000' then ' '
else '-'
end 'Hidden Product',
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 35155900
Hi,

If you are getting null in the case U_GarmentID1 is 1000 then use

case T0.[U_GarmentTID1]
when '1000' then ISNULL(dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID]),'')
when '1100' then ''
else ''
end 'Product',

case T0.[U_GarmentTID1]
when '1100' then ISNULL(dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID]),'')
when '1000' then ''
else '-'
end 'Hidden Product',

0
 

Author Comment

by:JonYen
ID: 35155970
thanks guys - but I keep getting zero.

is this something to do with the function at the start of the query which is forcing it to produce zero instead of empty string?

IF OBJECT_ID(N'dbo.LastValue', N'FN') IS NOT NULL
      DROP FUNCTION dbo.LastValue;
GO

CREATE FUNCTION dbo.LastValue
       (  
         @TID1 int= 0,
         @TID2 int= 0,
         @TID3 int= 0,
         @TID4 int= 0,
         @TID5 int= 0
       )RETURNS int
AS      
            BEGIN
            DECLARE @Result int
            SET @Result = 0
            IF @TID1 > 0
                  SET @Result = @TID1
            IF @TID2 > 0
                  SET @Result = @TID2
            IF @TID3 > 0
                  SET @Result = @TID3
            IF @TID4 > 0
                  SET @Result = @TID4
            IF @TID5 > 0
                  SET @Result = @TID5      
            RETURN @Result
            END
GO

SET NOCOUNT ON SELECT 'Name', 'Description', 'Colour', 'Size', 'SKU', 'Sub-SKU', 'Active', 'Stock', 'Threshold', 'Product', 'Product Hidden', 'Sell Price', 'PicturName', 'Garment Personalisation'

SELECT T0.[U_Garment_Title]'Name',

T0.[U_GarmentDesc],

case T0.[U_GarmentColour]
when 'NULL' THEN ''
else T1.[Name]
END as 'Colour',

case T0.[U_GarmentSize]
when 'NULL' THEN ''
else T2.[Name]
END as 'Size',

case t0.[U_GarmentSKU]
when 'NULL' then ''
else T3.[Name]
end as 'SKU',

T0.[ItemCode]'Sub-SKU',

case T0.[U_Active]
when 'Y' then 'YES'
when 'N' then 'NO'
else '-'
END 'Active',

T0.[OnHand]'Stock',

T0.[U_Threshold],

case T0.[U_GarmentTID1]
when '1000' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1100' then ' '
else ' '
end 'Product',

case T0.[U_GarmentTID1]
when '1100' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1000' then ' '
else '-'
end 'Hidden Product',
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 143 total points
ID: 35156004
check if you have given default value 0 to that column
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 35156425
Hi,

It looks that your function is returning ZERO and not an empty string.
Moreover, what is the datatype of column [U_Website_ID]. If you are not getting any error that means it is integer or implicitly can be converted to integer.

This is not a good condition for the query.
All the parts of "case" statement must return single datatype. But in the given query it can return integer and character both.

As per question, please tell us that where you are getting a NULL and you want a blank string?
0
 
LVL 4

Assisted Solution

by:qasim_md
qasim_md earned 143 total points
ID: 35156534
Try this, this must work :::: and let me know if it worked???

IF OBJECT_ID(N'dbo.LastValue', N'FN') IS NOT NULL
      DROP FUNCTION dbo.LastValue;
GO

CREATE FUNCTION dbo.LastValue
       (  
         @TID1 int= 0,
         @TID2 int= 0,
         @TID3 int= 0,
         @TID4 int= 0,
         @TID5 int= 0
       )RETURNS int
AS      
            BEGIN
            DECLARE @Result int
            SET @Result = NULL
            IF @TID1 > 0
                  SET @Result = @TID1
            IF @TID2 > 0
                  SET @Result = @TID2
            IF @TID3 > 0
                  SET @Result = @TID3
            IF @TID4 > 0
                  SET @Result = @TID4
            IF @TID5 > 0
                  SET @Result = @TID5      
            RETURN @Result
            END
GO

SET NOCOUNT ON SELECT 'Name', 'Description', 'Colour', 'Size', 'SKU', 'Sub-SKU', 'Active', 'Stock', 'Threshold', 'Product', 'Product Hidden', 'Sell Price', 'PicturName', 'Garment Personalisation'

SELECT T0.[U_Garment_Title]'Name',

T0.[U_GarmentDesc],

case T0.[U_GarmentColour]
when 'NULL' THEN ''
else T1.[Name]
END as 'Colour',

case T0.[U_GarmentSize]
when 'NULL' THEN ''
else T2.[Name]
END as 'Size',

case t0.[U_GarmentSKU]
when 'NULL' then ''
else T3.[Name]
end as 'SKU',

T0.[ItemCode]'Sub-SKU',

case T0.[U_Active]
when 'Y' then 'YES'
when 'N' then 'NO'
else '-'
END 'Active',

T0.[OnHand]'Stock',

T0.[U_Threshold],

case T0.[U_GarmentTID1]
when '1000' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1100' then ' '
else ' '
end 'Product',

case T0.[U_GarmentTID1]
when '1100' then dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID])
when '1000' then ' '
else '-'
end 'Hidden Product',
0
 

Author Comment

by:JonYen
ID: 35157046
sorry no!    this seems to have done it from another forum:

IF OBJECT_ID(N'dbo.LastValue', N'FN') IS NOT NULL
      DROP FUNCTION dbo.LastValue;
GO

CREATE FUNCTION dbo.LastValue
       (  
         @TID1 int= 0,
         @TID2 int= 0,
         @TID3 int= 0,
         @TID4 int= 0,
         @TID5 int= 0
       )RETURNS int
AS      
            BEGIN
            DECLARE @Result int
            SET @Result = 0
            IF @TID1 > 0
                  SET @Result = @TID1
            IF @TID2 > 0
                  SET @Result = @TID2
            IF @TID3 > 0
                  SET @Result = @TID3
            IF @TID4 > 0
                  SET @Result = @TID4
            IF @TID5 > 0
                  SET @Result = @TID5      
            RETURN @Result
            END
GO

IF OBJECT_ID(N'dbo.nulls2emptystr', N'FN') IS NOT NULL
      DROP FUNCTION dbo.nulls2emptystr;
GO

CREATE FUNCTION dbo.nulls2emptystr
       (  @val char(4)=''
       )RETURNS char(4)
AS      
            BEGIN
            IF @val = '0' OR @val='NULL'
                  SET @val = ''
            RETURN @val
            END
GO



SET NOCOUNT ON SELECT 'Name', 'Description', 'Colour', 'Size', 'SKU', 'Sub-SKU', 'Active', 'Stock', 'Threshold', 'Product', 'Product Hidden', 'Sell Price', 'PicturName', 'Garment Personalisation'

SELECT T0.[U_Garment_Title]'Name',

T0.[U_GarmentDesc],

case T0.[U_GarmentColour]
when NULL THEN ''
else T1.[Name]
END as 'Colour',

case T0.[U_GarmentSize]
when NULL THEN ''
else T2.[Name]
END 'Size',

case t0.[U_GarmentSKU]
when NULL then ''
else T3.[Name]
end as 'SKU',

T0.[ItemCode]'Sub-SKU',

case T0.[U_Active]
when 'Y' then 'YES'
when 'N' then 'NO'
else '-'
END 'Active',

T0.[OnHand]'Stock',

T0.[U_Threshold],




case T0.[U_GarmentTID1]
when '1000' then dbo.nulls2emptystr( CAST(dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID]) AS char(4)))
when '1100' then ' '
else ''
end 'Product',

case T0.[U_GarmentTID1]
when '1100' then dbo.nulls2emptystr( CAST(dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID]) AS char(4)))
when '1000' then ' '
end 'Hidden Product',


T9.[Price]'Sell Price',

case T0.[PicturName]
when NULL THEN ''
else T0.[PicturName]
END 'Picture',


case T0.[U_Garment_Pers]
when 'Y' then 'YES'
when 'N' then 'NO'
else '-'
END 'Garment Personalisation'



FROM [dbo].[OITM]  T0
LEFT JOIN [dbo].[@GARMENT_COLOUR]  T1 ON T0.U_GarmentColour = T1.Code
LEFT JOIN [dbo].[@GARMENT_SIZE]  T2 ON T0.U_GarmentSize = T2.Code
LEFT JOIN [dbo].[@GARMENT_SKU]  T3 ON T0.U_GarmentSKU = T3.Code
LEFT JOIN [dbo].[@GARMENT_TID]  T4 ON T0.U_GarmentTID1 = T4.Code
LEFT JOIN [dbo].[@GARMENT_TID2]  T5 ON T0.U_GarmentTID2 = T5.Code
LEFT JOIN [dbo].[@GARMENT_TID3]  T6 ON T0.U_GarmentTID3 = T6.Code
LEFT JOIN [dbo].[@GARMENT_TID4]  T7 ON T0.U_GarmentTID4 = T7.Code
LEFT JOIN [dbo].[@GARMENT_TID5]  T8 ON T0.U_GarmentTID5 = T8.Code
INNER JOIN ITM1 T9 ON T0.ItemCode = T9.ItemCode

WHERE T0.[U_Active] ='Y' and  T9.[PriceList] =8

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 142 total points
ID: 35157196
yes the function will return a 0 rather than a null, also since the function is returning an integer rather than a string you should cast it to a varchar....

you could try...  coalesce(nullif(...,'0'),'')

but if you want nulls honored then you should probably rewrite the function

which should be more like


CREATE FUNCTION dbo.LastValue
       (  
         @TID1 int= 0,
         @TID2 int= 0,
         @TID3 int= 0,
         @TID4 int= 0,
         @TID5 int= 0
       )RETURNS int
AS      
            BEGIN
   
            RETURN case when @tid5 > 0 then @tid5
                                 when @tid4>0 then @tid4
                                  when @tid3>0 then @tid3
                                   when @tid2>0 then @tid2
                                  when @@tid1>0 then @tid1
                                    else 0
                                   end

            END
GO

coalesce(nullif(case T0.[U_GarmentTID1]
when '1000' then convert(varchar(20),dbo.lastValue(T4.[U_Website_ID],T5.[U_Website_ID],T6.[U_Website_ID],T7.[U_Website_ID],T8.[U_Website_ID]))
when '1100' then ' '
else ' '
end,'0'),'') as 'Product',

Open in new window

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 142 total points
ID: 35157224
that is a ridiculous suggestion , and is implementing a basic coalesce/isnull
function in your code so v.inefficient
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 35164083
The Auther has not responded back with the required details of thread ID:35156425.

Also, the solution given by the author is not providing any solution about "How to return blank instead of NULL".

Instead of this, he is trying to convert ZERO to BLANK.

I am not sure that whether these are the enought points for Objection.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now