| Author |
Message
|
| omerakhter |
 Posted: Tue May 19, 2009 4:43 am Post subject: ESQL SELECT with ITEM doesnt work |
 |
|
Novice
Joined: 19 May 2009
Posts: 22
|
Hello
I have trying to SELECT from a database view which has single column of type varchar2 using the following statement but it doesn't work:
DECLARE TEMP ROW;
SET TEMP[] = SELECT ITEM T.TID FROM Database.ORCL.UPDATE_VIEW AS T;
Can anyone please tell me whats wrong here?
I am using Message Broker 6.1 |
|
| Back to top |
|
 |
| WMBDEV1 |
| Posted: Tue May 19, 2009 4:57 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| How do you mean doesnt work? doesnt return anything or throws an error? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 19, 2009 5:00 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
See, when I try and run that exact piece of ESQL, it complains that it can't find a database table of that name.
So it doesn't work for me either. |
|
| Back to top |
|
|
| omerakhter |
| Posted: Tue May 19, 2009 5:18 am Post subject: |
|
|
Novice
Joined: 19 May 2009
Posts: 22
|
Well when I use the above syntax, it marks the error in editor with a RED X sign and says
"Syntax error. Valid options include: identifier ) } ] , || / = >= > <= < - <> ( . + ; *
AND AS BEGIN CALL CASE CATALOG CREATE DAY DECLARE DEFAULT
DELETE DO DOMAIN ELSE ELSEIF END ESCAPE EXCEPTION FOR FROM
HOUR IDENTIFIER IDENTITY IF IN INSERT INTO IS ITERATE LEAVE
LIKE LOOP MINUTE MODE MONTH NOT OR REPEAT RETURN SECOND
THEN TO UNTIL VALUES WHEN WHERE WHILE YEAR"
And when I modify this to be
DECLARE TEMP ROW;
SET TEMP = SELECT ITEM T.TID FROM Database.ORCL.UPDATE_VIEW AS T;
The broker doesnt allow me to deploy and logs an erroe in Error Log which says:
"BIP2497E: (.TESTFLOW_Compute.Main, 30.7) : 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.
Correct the syntax of your ESQL expression in node '.TESTFLOW_Compute.Main', around line and column '30.7', then redeploy the message flow."
The database and table names are valid in my environment and available through intellisensce as well. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Tue May 19, 2009 5:22 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
Now we're getting to it....
| omerakhter wrote: |
"BIP2497E: (.TESTFLOW_Compute.Main, 30.7) : Illegal data type for target. A list field reference is required.
|
Have you searched to try and better understand what this error could mean? |
|
| Back to top |
|
|
| mgk |
| Posted: Tue May 19, 2009 8:47 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
SELECT returns a list. You are assigning the list to a ROW directly, which would imply multiple ROWs, rather than multiple children of the single ROW which is what you actually want. Try this:
SET TEMP.FOO[] = SELECT ITEM T.TID FROM Database.ORCL.UPDATE_VIEW AS T;
_________________
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 |
|
|
| omerakhter |
| Posted: Tue May 19, 2009 10:01 pm Post subject: |
|
|
Novice
Joined: 19 May 2009
Posts: 22
|
Thanks, it works
Could change the data type of TEMP to make my statement work? Is there any list data type? |
|
| Back to top |
|
|
| omerakhter |
| Posted: Tue May 19, 2009 10:09 pm Post subject: |
|
|
Novice
Joined: 19 May 2009
Posts: 22
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 20, 2009 5:01 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
ROW type is what you want.
You could use TEMP.[] instead of TEMP.FOO[].
But TEMP[], as MGK said, is an array of ROWs, and you've only got one.
There's no such thing as a ROW ROW type. Too many people would make one named BOAT, and we can't have that. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 20, 2009 5:06 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| There's no such thing as a ROW ROW type. Too many people would make one named BOAT, and we can't have that. |
Is that a data stream.....? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
