| Author |
Message
|
| et381 |
 Posted: Thu Jul 09, 2009 6:04 pm Post subject: How to select NULL value from SELECT? |
 |
|
Newbie
Joined: 09 Jul 2009
Posts: 3
|
Hi All,
Got a quick question regarding ESQL
If I want to set a node value to NULL, I could do something like this.
SET OutputRoot.MRM.SomeNode VALUE = NULL;
Can I achieve something similar using the SELECT function?
Anything like
SET OutputRoot.MRM =
SELECT VALUE NULL AS SomeNode
Any suggestion or pointers appreciated.
Cheers
et381 |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Jul 09, 2009 11:10 pm Post subject: Re: How to select NULL value from SELECT? |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| et381 wrote: |
SET OutputRoot.MRM =
SELECT VALUE NULL AS SomeNode
|
Why? What's wrong with the other method? What's the requirement here?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jul 10, 2009 4:26 am Post subject: Re: How to select NULL value from SELECT? |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| et381 wrote: |
| Can I achieve something similar using the SELECT function |
Yes. In general, anything you can do with SET you can do with SELECT.
You may find you're tying yourself into knots trying to do it that way, but it's possible. |
|
| Back to top |
|
|
| et381 |
| Posted: Mon Jul 13, 2009 3:16 pm Post subject: |
|
|
Newbie
Joined: 09 Jul 2009
Posts: 3
|
Any pointers to what the syntax might be to achieve this?
The scnerio is for SELECT to fetch data from a database and the database column field is nullable, hence wants to SELECT the NULL value into the message tree. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jul 14, 2009 5:04 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Ah, I see.
Use COALESCE with your column identifier as the first value and an empty value for the second argument.
Set OutputRoot.A.FIELD = SELECT COALESCE(A.MYNULLFIELD, '') from Database.MYDB AS A;
Or you may also, possibly, find interesting results using AS clauses. |
|
| Back to top |
|
|
| et381 |
| Posted: Wed Jul 15, 2009 12:02 am Post subject: |
|
|
Newbie
Joined: 09 Jul 2009
Posts: 3
|
| Thanks, ended up doing some post processing after the select to set NULL. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Jul 15, 2009 12:27 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If it helps, I believe that SELECT does not return NULLs by design, but PASSTHRU does, so if you put your SELECT inside PASSTHRU you will see the NULL values...
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
