| Author |
Message
|
| EKhalil |
 Posted: Wed Mar 09, 2005 11:18 am Post subject: Database interaction issues in WBIMB 5.0 CSD 4 + Updates |
 |
|
Voyager
Joined: 29 Apr 2003
Posts: 99
Location: Boston, MA
|
This is a production query that has been working fine in prod environment on 2.1 Integrator.
This is the esql for the query:
SET Environment.Variables.Result[storageNoCnt] = THE (SELECT ITEM A.HUB_CD_CODE_NAME FROM Database.HUB_CD_TRANSLATION AS A
WHERE A.HUB_CD_GROUP_NAME = 'LOB'
AND A.HUB_CD_SYSTEM_ID = 'SBL'
AND A.HUB_CD_GROUP_ID =
THE (SELECT ITEM B.HUB_CD_GROUP_ID FROM Database.HUB_CD_TRANSLATION AS B
WHERE B.HUB_CD_CODE_NAME = inMRMpolicyAREA.SPA_POLICY_RETURN[storageNoCnt].SPA_POLICY_AREA.SPA_LOB
AND B.HUB_CD_GROUP_NAME ='LOB'
AND B.HUB_CD_SYSTEM_ID = 'LEG'
This is our exception handling output:
".....RecoverableException 2437. Error detected, rethrowing. array subscript error. See [Exception Handling Trace.txt] log file for more information.......
<Text>SET Environment.Variables.Result[storageNoCnt] =
THE (SELECT ITEM COLUMN(0) FROM DATABASE(SELECT A.HUB_CD_CODE_NAME FROM HUB_CD_TRANSLATION A WHERE (((A.HUB_CD_GROUP_NAME)=('LOB'))AND((A.HUB_CD_SYSTEM_ID)=('SBL')))AND((A.HUB_CD_GROUP_ID)=(?)),
THE (SELECT ITEM COLUMN(0) FROM DATABASE(SELECT B.HUB_CD_GROUP_ID FROM HUB_CD_TRANSLATION B WHERE (((B.HUB_CD_CODE_NAME)=(?))AND((B.HUB_CD_GROUP_NAME)=('LOB')))AND((B.HUB_CD_SYSTEM_ID)=('LEG')), inMRMpolicyAREA.SPA_POLICY_RETURN[storageNoCnt].SPA_POLICY_AREA.SPA_LOB))));</Text>
....."
Could someone please enlighten me and tell me if they see anything wrong with this query ???
Eva |
|
| Back to top |
|
 |
| JT |
| Posted: Wed Mar 09, 2005 4:26 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
What's the value of the index 'storageNoCnt'?
Basically, the 2437 error is saying you're assigning a value to Environment.Variables.Result with an index of storageNoCnt, but an occurence of Environment.Variables.Result with an index of storageNoCnt-1 doesn't exist.
Click here for details. |
|
| Back to top |
|
|
| JMB |
| Posted: Thu Mar 10, 2005 5:45 am Post subject: |
|
|
Apprentice
Joined: 01 Apr 2002
Posts: 29
|
| storageNoCnt is set to one before this code is exicuted the first time, and incremented by one after each iteration. |
|
| Back to top |
|
|
| fschofer |
| Posted: Thu Mar 10, 2005 6:52 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
Hi,
it seems to me you are getting NULLs back from your select.
Put a COALESCE statement arround your select, like
.... = COALESCE ( ...select... , storageNoCnt);
to see when the select fails.
Greetings
Frank |
|
| Back to top |
|
|
| EKhalil |
| Posted: Thu Mar 10, 2005 8:46 am Post subject: |
|
|
Voyager
Joined: 29 Apr 2003
Posts: 99
Location: Boston, MA
|
Yep you're absolutely right -nulls back !!!
I'll try coalesce, hopefully it will work.
THANKS  |
|
| Back to top |
|
|
| EKhalil |
| Posted: Thu Mar 10, 2005 11:25 am Post subject: |
|
|
Voyager
Joined: 29 Apr 2003
Posts: 99
Location: Boston, MA
|
Well, here it goes remember that ITEM doesn't return a name/value pair it just returns a value
Here is the fix
SET Environment.Variables.Result[storageNoCnt].HUB_CD_CODE_NAME
So I will RTFM next time |
|
| Back to top |
|
|
|
|
