How to return blank instead of NULL

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

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

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?

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

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.