| Author |
Message
|
| vadivel |
 Posted: Wed Oct 10, 2007 11:20 am Post subject: NULLIF Function |
 |
|
Voyager
Joined: 05 Aug 2002
Posts: 79
Location: US
|
Hi,
I'm facing an issue with the NULLIF function, not sure if thats how the function works. I'm using WMB 5.0
When I do a NULLIF for a message set field which is defined as short, then the function returns NULL if the field value is '0', if it is a non-zero value then the function returns the value as such.
NULLIF(variable,'')
returns NULL if variable value is '0'
returns 1 if variable value is '1'
I've pasted the output from trace for reference
2007-10-10 14:55:16.989761 3944 UserTrace BIP2539I: Node 'TXX_WMB_AVIEXPAcknowledgeShipmentPUB00_001.MapAVIEXPToOAG': Finished evaluating expression 'NULLIF(AVIEXP.*:*[1].RECTIFIED_IND, '')' at (.TXX_WMB_AVIEXPAcknowledgeShipmentPUB00_001_MapAVIEXPToOAG.MapAVIEXP_To_OAGISCanonical, 473.8 . This resolved to 'NULLIF(0, '')'. The result was 'NULL'.
Thanks in advance
Vadi |
|
| Back to top |
|
 |
| AkankshA |
| Posted: Wed Oct 10, 2007 7:49 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
|
| Back to top |
|
|
| vadivel |
| Posted: Thu Oct 11, 2007 5:54 am Post subject: |
|
|
Voyager
Joined: 05 Aug 2002
Posts: 79
Location: US
|
I too thought so, but I guess it behaves in a different manner if the input field that is being checked against is a short or long. I had pasted the out from broker trace in my earlier post to show how it gets evaluated.
This resolved to 'NULLIF(0, '')'. The result was 'NULL'.
-Vadi |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Oct 11, 2007 6:10 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If you set the right properties on a message set, when MRM processes a "0" value in the physical message, it will create a NULL value in the logical message.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Oct 11, 2007 12:19 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Jeff is correct ( as usual ). The easiest way to do this is to exploit the automatic null processing features of the MRM parser. The exact settings depend on the physical format you are using ( CWF / TDS / XML ). |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Oct 11, 2007 12:24 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| kimbert wrote: |
| Jeff is correct ( as usual ). |
If I'm correct 88% of the time, I consider that I'm doing very well for the day. I'm not nearly smart enough to manage 100%.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
