| Author |
Message
|
| pcelari |
 Posted: Tue Sep 09, 2008 9:40 am Post subject: how to get cardinality of array of type row? |
 |
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
Hi,
I declared an array as of type ROW to store resultset from a stored procedure, which contains rows of business hours. But it doesn't let me get the cardinality of the array after the assignment, see code below. Can any expert point out what I'm missing? thanks a lot.
declare Business_CustSvc_Hours row;
set Business_CustSvc_Hours =
select
P.wk_day as DayOfWk,
P.open as Hour_Open,
P.close as Hour_Close
from Environment.Result[] as P
where P.dept = 'CustomerService';
declare M integer cardinality(Business_CustSvc_Hours[]); <--- why is this wrong?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
 |
| sridhsri |
| Posted: Tue Sep 09, 2008 6:20 pm Post subject: |
|
|
Master
Joined: 19 Jun 2008
Posts: 297
|
I don't know how to make it work with a row variable. But I can suggest an alternative. you could store the result set in the local environment.
Set OutputLocalEnvironment.ResultSet = SELECT * FROM....
The cardinality of ResultSet.<fieldname>[] is what you are looking for. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Sep 10, 2008 3:52 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| declare M integer cardinality(Business_CustSvc_Hours[]); <--- why is this wrong? |
Because presumably you really want to count the children of the row variable like this [not tested]:
declare M integer cardinality(Business_CustSvc_Hours.*[]);
_________________
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 |
|
|
| keenlearner |
| Posted: Wed Sep 10, 2008 4:44 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2006
Posts: 62
|
Please read documentations before posting
declare Business_CustSvc_Hours row;
set Business_CustSvc_Hours.DATA[]=
select
P.wk_day as DayOfWk,
P.open as Hour_Open,
P.close as Hour_Close
from Environment.Result[] as P
where P.dept = 'CustomerService';
declare M integer cardinality(Business_CustSvc_Hours.DATA.Hour_Open[]);
Try this .. this should work |
|
| Back to top |
|
|
| pcelari |
| Posted: Wed Sep 10, 2008 7:06 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
Many, many thanks for all the insight !
apparently the manual doesn't sufficiently explain how to use the ROW type. But how did you know to include the ".DATA" keyword in the expression? It's nowhere mentioned.
again, many many thanks.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| madi |
| Posted: Wed Sep 10, 2008 7:46 am Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
because the select statement will return multiple rows, you need another repeatable variable within your row to take them in
--madi
_________________
IBM Certified Solutions Developer - WMB 6.0 |
|
| Back to top |
|
|
|
|
