?
Solved

How to return blank instead of NULL

Posted on 2011-03-17
13
Medium Priority
?
376 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

800 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