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 |
|
 |
|