Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to return blank instead of NULL

Posted on 2011-03-17
13
366 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

856 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