| Author |
Message
|
| LH33 |
 Posted: Wed Mar 12, 2003 2:08 pm Post subject: Database Lookup not working |
 |
|
Master
Joined: 21 Nov 2002
Posts: 200
|
HI! I have to take a value and look up it's decode value in a table. If no value is sent down in the XML, I need to assign a value of "DEFAULT" to the OutputRoot tag. It works fine if a valid value is sent down, but when no data is in the input tag, it doesn't assign the "DEFAULT". Can someone help me and see where I am messing up?
Thank you so much!! Lisa
Here is my code:
SET OutputRoot = InputRoot;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
DECLARE SqlState1 Character;
DECLARE SqlErrorText1 Character;
DECLARE SqlCode1 integer;
DECLARE SqlNativeError1 integer;
DECLARE IntSqlState integer;
Set OutputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode =
THE (SELECT ITEM A.CITY_ABBR FROM Database.BGE_LKUP_CITY as A where A.CITY_BGE =
InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode);
SET SqlState1 = sqlstate;
SET SqlCode1 = sqlcode;
SET SqlErrorText1 = sqlerrortext;
SET SqlNativeError1 = sqlnativeerror;
SET IntSqlState = CAST (SqlState AS INTEGER);
IF (IntSqlState <> 0)
or (InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode IS NULL)
or (InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode = ' ')
then
set OutputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode = 'UNKNOWN';
END IF; |
|
| Back to top |
|
 |
| AlexeiSkate |
| Posted: Wed Mar 12, 2003 2:29 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2002
Posts: 123
|
Have you try using a trace node to see if the SELECT returned anything?
I'm not sure if this makes any difference, but I think your conditional statement has to be something like
...or (InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode.CITY_ABBR IS NULL) or
(InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode.CITY_ABBR = ' ')
... |
|
| Back to top |
|
|
| LH33 |
| Posted: Wed Mar 12, 2003 2:31 pm Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
I haven't tried the trace node yet. What would I code in the trace node to see if the select returned anything?
Thank you!! |
|
| Back to top |
|
|
| lung |
| Posted: Wed Mar 12, 2003 5:36 pm Post subject: |
|
|
Master
Joined: 27 Aug 2002
Posts: 291
Location: Malaysia
|
This is what we did in our flows... In your trace, put $LocalEnvironment, this will display the LocalEnvironment values in your trace.
Then add this code.
| Code: |
Set OutputLocalEnvironment.Variables.XML.CityCode =
THE (SELECT ITEM A.CITY_ABBR FROM Database.BGE_LKUP_CITY as A where A.CITY_BGE =
InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode); |
Now look into your trace file.
Anyway, I think the code for your IF statements should refer to OutputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode.CITY_ABBR instead of InputRoot.XML.CreateJob.DataArea.Job.Location.Address.CityCode? Not sure what you want, actually. 
_________________
lung |
|
| Back to top |
|
|
| lillo |
| Posted: Wed Mar 12, 2003 11:26 pm Post subject: |
|
|
Master
Joined: 11 Sep 2001
Posts: 224
|
Lung,
You forgot the brakets. In the trace node you should have
| Code: |
| ${LocalEnvironment} |
Cheers,
_________________
Lillo
IBM Certified Specialist - WebSphere MQ |
|
| Back to top |
|
|
| EddieA |
| Posted: Thu Mar 13, 2003 7:22 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
I would also copy your SQL returns to the LocalEnvironment as well.
You 'might' be getting a 0 back for SqlState
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| lung |
| Posted: Thu Mar 13, 2003 4:17 pm Post subject: |
|
|
Master
Joined: 27 Aug 2002
Posts: 291
Location: Malaysia
|
Thanks for the correction there, lillo
Can't believe I made such a mistake... 
_________________
lung |
|
| Back to top |
|
|
|
|
