[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to return blank instead of NULL

Posted on 2011-03-17
13
Medium Priority
?
379 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
[X]
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
  • 3
  • 2
  • 2
  • +3
13 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 572 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 288 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 572 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

650 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