[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Setting a NULL value to an empty string in SQL

Posted on 2016-10-04
32
Medium Priority
?
72 Views
Last Modified: 2016-10-04
Heyas,

I have the following query:

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd));

If this SET statement returns a NULL value is their way to change  'f3.DBWCode' field to an empty string ''.

Any assistance is always appreciated.

Thank you.
0
Comment
Question by:Zack
[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
  • 14
  • 9
  • 9
32 Comments
 
LVL 9

Expert Comment

by:Karen
ID: 41829086
COALESCE(f3.DBWCode, '')
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829095
You can use below..

ISNULL(f3.DBWCode, '')
0
 

Author Comment

by:Zack
ID: 41829115
Heyas,

Can you be a bit more descriptive as to how I would use these?

Thank you.
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 32

Expert Comment

by:Pawan Kumar
ID: 41829122
Hi,

Please find the usage below.

ISNULL

SELECT Col1, ISNULL(Col2,0) Col2
FROM TableName

Open in new window


SELECT Col1, ISNULL(Col2,'') Col2
FROM TableName

Open in new window



COALESCE

SELECT Col1 , COALESCE(Col2,0) Col2
FROM TableName

Open in new window



SELECT Col1 , COALESCE(Col2,'') Col2
FROM TableName

Open in new window



Definition -

ISNULL - Replaces NULL with the specified replacement value.

ISNULL ( check_expression , replacement_value )  

From MSDN - The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

COALESCE - Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

COALESCE ( expression [ ,...n ] )  

FROM MSDN -

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

REF [ https://msdn.microsoft.com/en-in/library/ms190349.aspx ]
0
 
LVL 9

Expert Comment

by:Karen
ID: 41829130
hmm... hang on, do you mean
If this SET statement returns a NULL value
or
If this SELECT statement returns a NULL value
0
 

Author Comment

by:Zack
ID: 41829133
Hi Snowberry,

Clarification: If this SET statement returns a NULL value.

Thank you.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829135
May be..this

SET Environment.Variables.flag3 = ISNULL (SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 
									WHERE f3.ItemName='presendonadmit' 
									AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd) , '' )

Open in new window

0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829136
Or you can check like below..after SET,...

ISNULL ( Environment.Variables.flag3 , '' )
0
 
LVL 9

Expert Comment

by:Karen
ID: 41829147
ok, in that case your "subject" confused me

IF Environment.Variables.flag3 IS NULL THEN
    UPDATE Database.WMB_DBW_SAP_CODE as f3 SET f3.DBWCode = '' WHERE f3.ItemName='presendonadmit' 

Open in new window

0
 

Author Comment

by:Zack
ID: 41829148
Hi Pawan,

Thank for the input but I what I need is just the ISNULL to filter just on the f3.DBWCode field see below for a pseudocode example.

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd),'');

Thank you.
0
 

Author Comment

by:Zack
ID: 41829151
Hi Snowberry,

My apologies to you I just re-read my question I didn't make it 100% clear what I require is something like this:

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd),'');

How I filter just on the f3.DBWcode field for ISNULL?

Thank you.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829154
So do you need records where f3.DBWcode IS NULL ?
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829155
may be..

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd),'');

AND f3.DBWCode IS NULL
0
 
LVL 9

Expert Comment

by:Karen
ID: 41829158
Do you want to do the following, but in only one statement?

SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd));
IF Environment.Variables.flag3 IS NULL THEN
    SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''));

Open in new window

0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829160
Or may be...this

SET Environment.Variables.flag3 = 

CASE WHEN (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd)) IS NULL
THEN 
	(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''));
END

Open in new window

0
 

Author Comment

by:Zack
ID: 41829161
Hi Pawan,

Yes, I need the SET statement to flag when  f3.DBWCode ISNULL and change it to ''.

Thank you.
0
 
LVL 9

Expert Comment

by:Karen
ID: 41829163
Do you want to know when  f3.DBWCode is NULL, or when Environment.Variables.flag3 is NULL ??????
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829164
Didnt get can you explain via Pesudo code?
0
 

Author Comment

by:Zack
ID: 41829167
Hi Snowberry,

When f3.DBWCode  ISNULL in the SET Statement: SET Environment.Variables.flag3 =(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd));

My apologies for the lack of clarification initially.

Thank you.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829170
May be this

SET Environment.Variables.flag3 = 
    	
	(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode , 
	
	(THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''))

	=InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd))

Open in new window

0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829171
SET Environment.Variables.flag3 =
          
      (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode ,
      
      (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode=''))

      =InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd))


0
 
LVL 9

Expert Comment

by:Karen
ID: 41829174
it is really unclear what you want

lets try an example

if there is a record in f3 table

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = 456

then your query will return 123 if InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd = 456

So what problem are you trying to cope with ?
0
 

Author Comment

by:Zack
ID: 41829198
Hi Snowflake,

The best way I can clarify is if 'InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd' ISNULL change to ''.

Thank you.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829200
Ok try this then


SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND f3.DBWCode= ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829201
or you need like below

SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode,'')= ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))
0
 
LVL 9

Expert Comment

by:Karen
ID: 41829203
so

if there is a record in f3 table

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = 456

then you want your query to return 123 if InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd IS NULL


OR


if there is a record in f3 table

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = ''

then you want your query to return 123 if InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd IS NULL
0
 

Author Comment

by:Zack
ID: 41829221
Hi Snowflake

Correction:

so

if DBWCode equals NULL

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = NULL

Then I want DBWCode to be:

InvCode = 123
ItemName = 'presendonadmit'
DBWCode = ''

I think SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND ISNULL(f3.DBWCode,'')= ISNULL(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))

Posted by Pawan will work I will try in the next hr.

Thanks.
0
 
LVL 9

Expert Comment

by:Karen
ID: 41829225
are you saying you want to update the database????
your last comment does not make any sense
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41829249
I think he wanted to compare these fields and WHEN both are NULL then the comparison was failing.

Now when we are replacing NULLs with '' the comparison will happen.

0
 
LVL 9

Assisted Solution

by:Karen
Karen earned 1000 total points
ID: 41829268
Hopefully you are right Pawan!

By the way, since he is using ESQL I do not think ISNULL will work. But COALESCE should.
0
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41829271
Ohhh yes thanks , great sir... Let me rewrite the query for him.

SET Environment.Variables.flag3 =

 (THE(SELECT ITEM f3.InvCode FROM Database.WMB_DBW_SAP_CODE as f3 WHERE f3.ItemName='presendonadmit' AND COALESCE(f3.DBWCode,'')= COALESCE(InputRoot.MRM.ns:DG1[3].ns:DG26PresentOnAdmissionInd,'')))
1
 

Author Closing Comment

by:Zack
ID: 41829315
Thank you very much for the help guys.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

649 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