| Author |
Message
|
| pcelari |
 Posted: Thu Mar 05, 2009 3:00 pm Post subject: ESQL select against Environment.Resultset[] question |
 |
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
Hi,
in a compute node, a call to a stored procedure returns a resultset stored in the Environment.Resultset[].
I need to retrieve only one row that contains a particular value. So I use the following select statement to achieve that:
set s = select T.ITEMDESC
FROM Environment.ResultSet[] AS T
where T.TYPE = '2';
But at deploy time, I got the following failure report about the statement about my use of Environment.ResultSet[]
"Illegal data type for target. A list field reference is required.
The expression supplying the target must evaluate to a value of a suitable type. The given expression cannot possibly do so."
If I remove the "[]", it deploys successfully, but I get nothing back and T points to the first row only, which doesn't meet the condition.
Is it possible to use select statement against a resultset at all?
Of course, I can use the while loop to go over all rows until the match is found. But a select statement would be so simple.
any insight would be greatly appreciated.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Mar 05, 2009 3:13 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Yes this can work, but to construct the correct query I need to see the format of the data in the Envrionment.ResultSet.
Can you post the output of a trace node showing the Environment tree please.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| pcelari |
| Posted: Thu Mar 05, 2009 5:16 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
Here's the output of ${Environment}
( ['MQROOT' : 0x20022a8e650]
(0x01000000:Name):ResultSet = (
(0x03000000:NameValue):ITEM_ID = '46201' (CHARACTER)
(0x03000000:NameValue):TYPE = '0' (CHARACTER)
(0x03000000:NameValue):ITEMDESC = 'Base Price' (CHARACTER)
(0x03000000:NameValue):VALUE = '231.99' (CHARACTER)
)
(0x01000000:Name):ResultSet = (
(0x03000000:NameValue):ITEM_ID = '46201' (CHARACTER)
(0x03000000:NameValue):TYPE = '1' (CHARACTER)
(0x03000000:NameValue):ITEMDESC = 'Availability'
(0x03000000:NameValue):VALUE = 'Y' (CHARACTER)
)
(0x01000000:Name):ResultSet = (
(0x03000000:NameValue):ITEM_ID = '46201' (CHARACTER)
(0x03000000:NameValue):TYPE = '2' (CHARACTER)
(0x03000000:NameValue):ITEMDESC = 'Commercial Toaster'
(0x03000000:NameValue):VALUE = '' (CHARACTER)
)
)
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| m.schneider |
| Posted: Fri Mar 06, 2009 12:25 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
|
| Back to top |
|
|
| pcelari |
| Posted: Fri Mar 06, 2009 5:48 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thanks for the suggestion.
But the THE function didn't work either, as it returns only the first in the list, or empty, which is what I get with the select stmt.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Mar 06, 2009 3:17 pm Post subject: Re: ESQL select against Environment.Resultset[] question |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| pcelari wrote: |
I need to retrieve only one row that contains a particular value. So I use the following select statement to achieve that:
| Code: |
set s = select T.ITEMDESC
FROM Environment.ResultSet[] AS T
where T.TYPE = '2'; |
|
How is s declared?
| pcelari wrote: |
But at deploy time, I got the following failure report about the statement about my use of Environment.ResultSet[]
| Code: |
"Illegal data type for target. A list field reference is required.
The expression supplying the target must evaluate to a value of a suitable type. The given expression cannot possibly do so." |
|
It is not the use of Environment.ResultSet[] that is generating the error; it is the use of s.
| pcelari wrote: |
| But the THE function didn't work either, as it returns only the first in the list, or empty, which is what I get with the select stmt. |
If s is scalar, then the THE function probably works much better with ITEM in the SelectClause of the SELECT function. |
|
| Back to top |
|
|
| flahunter |
| Posted: Mon Mar 09, 2009 12:56 am Post subject: |
|
|
Acolyte
Joined: 30 Oct 2008
Posts: 62
|
| What's the data type of s? |
|
| Back to top |
|
|
| pcelari |
| Posted: Mon Mar 09, 2009 9:43 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
it is of character type.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Mar 09, 2009 11:31 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Then you will need to the both THE and ITEM with the select to ensure it returns a scalar value.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| flahunter |
| Posted: Tue Mar 10, 2009 12:20 am Post subject: |
|
|
Acolyte
Joined: 30 Oct 2008
Posts: 62
|
Yes, something like this
set s = THE(select ITEM T.ITEMDESC
FROM Environment.ResultSet[] AS T
where T.TYPE = '2'); |
|
| Back to top |
|
|
| pcelari |
| Posted: Wed Mar 11, 2009 11:54 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
Many thanks for all the insight!
the following code
set s = RTRIM (THE (select T.ITEMDESC
FROM Environment.ResultSet[] AS T
where T.TYPE = '2'));
delivers the correct result. It's so much simpler than the otherwise search and get loop.
declare M integer cardinality(Environment.ResultSet[]);
declare s char '';
X : WHILE i <= M DO
IF (Environment.ResultSet[i].TYPE = '2') THEN
set s = rtrim(Environment.ResultSet[i].ITEMDESC);
LEAVE X;
END IF;
set i = i + 1;
END WHILE X;
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
|
|
