| Author |
Message
|
| neo_revolution |
 Posted: Tue Jul 20, 2004 12:39 pm Post subject: COALESCE behaving strange |
 |
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
I am using COALESCE function, something like below
SET OutputRoot.XML.(XML.Element)P = COALESCE(longDescription, 'NO DESCRIPTION AVAILABLE');
When 'longDescription' is NULL, COALESCE is returning a NULL value.
I can't understand why its not returning default text - 'NO DESCRIPTION AVAILABLE'
Am I doing anything different ? |
|
| Back to top |
|
 |
| kirani |
| Posted: Tue Jul 20, 2004 12:51 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
How are you populating the longDescription in your code?
What is the output of above statement? Can you post output message?
Also, let us know your environment details.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| neo_revolution |
| Posted: Tue Jul 20, 2004 12:58 pm Post subject: |
|
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
My input msg is XML.
SET longDescription =
InputRoot.XML.(XML.Element)XMI."XMI.content".MessageProcessingNodeType.MessageProcessingNode[i].(XML.Attribute)"longDescription";
What is the output of above statement? Can you post output message?
SET OutputRoot.XML.(XML.Element)P = COALESCE(longDescription, 'NO DESCRIPTION AVAILABLE');
The output is coming as NULL if longDescription is NULL.
I set my environment to 'ALL' |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Jul 20, 2004 1:28 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try this,
DECLARE DESC CHAR;
| Code: |
IF ( longDescription is NULL) THEN
SET DESC = 'NO DESCRIPTION AVAILABLE';
ELSE
SET DESC = InputRoot.XML.(XML.Element)XMI."XMI.content".MessageProcessingNodeType.MessageProcessingNode[i].(XML.Attribute)"longDescription";
END IF;
SET OutputRoot.XML.(XML.Element)P = DESC;
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| neo_revolution |
| Posted: Tue Jul 20, 2004 3:25 pm Post subject: |
|
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
Hi Kiran,
I am wondering why COALESCE is not working in this case?
Thanks |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Jul 20, 2004 3:47 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Well, that's what we are trying to find out. If you use above code and it works then there is something wrong with COALESCE function. If you get different results for above code then there is something wrong with the input data that might be causing this problem.
What version (and CSD) of WMQI/WBIMB you are using?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| fschofer |
| Posted: Wed Jul 21, 2004 4:21 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
Hi,
have you tried to run a debug trace of your flow => compute node.
Maybe there you can see what went wrong.
Greetings
Frank |
|
| Back to top |
|
|
| neo_revolution |
| Posted: Wed Jul 21, 2004 4:39 am Post subject: |
|
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
I did something like this,
IF(longDescription = '') THEN
SET description =
InputRoot.XML.(XML.Element)XMI."XMI.content".MessageProcessingNodeType.MessageProcessingNode[i].(XML.Attribute)"shortDescription";
END IF;
It worked. I am on WMQI 2.1 and CSD3.
Thanks. |
|
| Back to top |
|
|
| neo_revolution |
| Posted: Wed Jul 21, 2004 5:11 am Post subject: |
|
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
Guys,
I figured it out. My 'longDescription' is being populated as empty string, its not null. So COALSCE is returning me the empty string.
Thanks Kiran and everyone. |
|
| Back to top |
|
|
| solomita |
| Posted: Wed Dec 07, 2005 1:13 pm Post subject: Similar Coalesce problem |
|
|
Voyager
Joined: 06 May 2003
Posts: 94
|
I am having a problem with COALESCE. My ESQL was working a few months back but now I am back at the customer site and its not working. Here is the code:
SET OutputRoot.XML.event.detail.acars.(XML.Attribute)NextFixPos = COALESCE(InputBody.POS_LINE_2.NEXT_FIX_POS,'');
My messages now go to the failure queue which has me believing that it is not viewing my quotes as a blank but as NULL.
Any ideas?
_________________
IBM Certified Specialist - WebSphere MQ Integrator
IBM Certified System Administrator - WebSphere MQ V5.3
IBM Certified System Administrator - WebSphere Business Integration Message Broker V5 |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 07, 2005 3:39 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
| Quote: |
| My ESQL was working a few months back but now I am back at the customer site and its not working |
What changed. 
How do you know it's the Coalesce that's giving you the problems. What's the error/exception.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| solomita |
| Posted: Wed Dec 07, 2005 4:22 pm Post subject: |
|
|
Voyager
Joined: 06 May 2003
Posts: 94
|
I had begun commenting out ESQL and when i had the COALESCE lines commented out, the flows ran. I have a copy of the flows and messages sets deployed on a box internal to my company and everything is working fine there. Not sure why it isn't at the customer site.
_________________
IBM Certified Specialist - WebSphere MQ Integrator
IBM Certified System Administrator - WebSphere MQ V5.3
IBM Certified System Administrator - WebSphere Business Integration Message Broker V5 |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 07, 2005 5:56 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
| EddieA wrote: |
| What's the error/exception. |
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| solomita |
| Posted: Thu Dec 08, 2005 10:25 am Post subject: |
|
|
Voyager
Joined: 06 May 2003
Posts: 94
|
My messages were ending up on the failure queue. I started by commenting all the lines of ESQL and slowly releasing the lines until I got an error (our flow debugger is not working). After all the commenting was done lines similar to the one I mentioned above were the ones that were preventing the message from processing successfully. However...we do have an exact replica of this broker environment on an internal system and I checked it last night and it was working successfully. I brought my input messages over from that box internally to the site and its working now! I guess the input messages that were being used had invalid control characters embedded even though they looked identical.
_________________
IBM Certified Specialist - WebSphere MQ Integrator
IBM Certified System Administrator - WebSphere MQ V5.3
IBM Certified System Administrator - WebSphere Business Integration Message Broker V5 |
|
| Back to top |
|
|
| JT |
| Posted: Thu Dec 08, 2005 11:34 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
|
| Back to top |
|
|
|
|
