| Author |
Message
|
| dosttumhara1810 |
 Posted: Mon Dec 30, 2013 3:03 am Post subject: COALESCE FUNCTION |
 |
|
Voyager
Joined: 01 Dec 2010
Posts: 76
|
Hi All,
I have a requirement in which if an input field (ChannelName) is coming empty/null or if the field itself is absent in request, then corresponding Output tag(TargetName) should not be created/generated.
The issue with COALESCE function is this that the Output tag gets created in this case if the Input tag is coming as empty or null.
Is there any way i can achieve this.
SET OutputRoot.XMLNSC.Response.Message.TargetName=COALESCE(InputRoot.XMLNSC.Request.Message.ChannelName,NULL); |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Dec 30, 2013 3:15 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Please use [ C O D E ] Tags for your code.
How about this
1) Test the input using COALESCE but with a unique/impossible value rather than NULL.
2) If the value of the input is not the unique/impossible value then set the output.
[/quote]
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| dosttumhara1810 |
| Posted: Mon Dec 30, 2013 3:54 am Post subject: |
|
|
Voyager
Joined: 01 Dec 2010
Posts: 76
|
Did not got your point here... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Dec 30, 2013 4:09 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
It really isn't that diffiult. Something like this (untested)
| Code: |
declare tChar CHAR COALESCE(InputRoot.XMLNSC.Request.Message.ChannelName,'PLUGGH');
if tChar <> 'PLUGGH' then
SET OutputRoot.XMLNSC.Response.Message.TargetName = tChar;
end if;
|
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| dosttumhara1810 |
| Posted: Mon Dec 30, 2013 4:46 am Post subject: |
|
|
Voyager
Joined: 01 Dec 2010
Posts: 76
|
| Good option thanks anyways , but cannot do it , i have 300+ fields , cannot do it for every field. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Dec 30, 2013 4:53 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have you looked at using the mapping node and doing it that way?
A message model with the correct defaults would help (IMHO)
If you have to maunally parse 300+ fields like this then there is something wrong with what you being asked to do.
The majority of cases could be covered by a simple map.(again IMHO)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Dec 30, 2013 10:16 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
You could write a simple esql function : for example
| Code: |
CREATE FUNCTION foo (IN parm CHAR) RETURNS CHAR
IF COALESCE(parm,'') = '' THEN RETURN NULL;
ELSE
RETURN parm;
end if;
end;
|
and then for each field
| Code: |
SET OutputRoot.XMLNSC.Response.Message.TargetName = foo(tChar); |
you will need one such function for each data type you want to set to nulls |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Mon Dec 30, 2013 8:40 pm Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
Use EXISTS function.
If exist then only map.. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Dec 30, 2013 11:46 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| vikas.bhu wrote: |
Use EXISTS function.
If exist then only map.. |
That will not work as the OP wants.
Firstly, you are suggesting an IF for each field which is exactly what the OP said did not want.
Secondly, if the input message contains a field like
The EXISTS function will return true, and if you read the requirement, if the field is empty/null then do not map. |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Dec 31, 2013 1:37 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
My experience is broker will generate the out empty tag if no check is done on input tag...
And yes one more condition will be required( <>'' ) and null check is not required in this case.
Last edited by vikas.bhu on Tue Dec 31, 2013 1:41 am; edited 1 time in total |
|
| Back to top |
|
|
| dogorsy |
| Posted: Tue Dec 31, 2013 1:41 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| vikas.bhu wrote: |
My experience is broker will generate the if no check is required...
|
Can you please explain what you are trying to say ? the broker will not generate any if statements.
As you said, the OP will need
| Code: |
| If exist then only map |
for each field, which is what he is trying to avoid. |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Dec 31, 2013 1:47 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
I saw you post your solution is better..it will avoid EXISTS and less coding will be required.But need to check whether is solve the problem.
(I think last parent tag will be populated). |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Dec 31, 2013 2:06 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
| vikas.bhu wrote: |
I saw you post your solution is better..it will avoid EXISTS and less coding will be required.But need to check whether it solve the problem.
(I think last parent tag will be populated). |
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Dec 31, 2013 6:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
If you write
| Code: |
| Set OutputRoot.XMLNSC.Request.ColumnName = NULL; |
then you should not see an empty tag in the output message.
If you are positive that the value you are assigning to the Output field is *really* NULL, and not "NULL", or "", or another kind of empty value, and you are still seeing an empty tag in the output message, then you are missing somewhere that you have told it to put the element in the logical message tree, or you are explicitly setting the fieldvalue rather than the element, or you have done something to ensure that default values are populated.
Take a user trace of the code.
Use a Trace node to output the message tree before the output node.
This will help you figure out why you are seeing an empty tag. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jan 02, 2014 4:30 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
There is an alternative, simpler solution. It may use a little more CPU.
- Remove all of the empty/null fields using an XSLT node
- Do the mapping without needing to worry about empty/null fields.
For performance reasons, if you do use an XSLT node then it should be the first node in the flow. You can find a stylesheet to do what you need here : http://stackoverflow.com/questions/6648679/removing-empty-tags-from-xml-via-xslt.
btw, you have not defined what you mean by 'null'. You need to think very clearly about that. Note that 'null' and 'empty' are very different concepts in XML.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
|
|
