| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| PASSTHRU in IF Statement |
« View previous topic :: View next topic » |
| Author |
Message
|
| paustin_ours |
 Posted: Sat Aug 14, 2004 11:36 am Post subject: PASSTHRU in IF Statement |
 |
|
Yatiri
Joined: 19 May 2004
Posts: 667
Location: columbus,oh
|
This is what am tryin to do,
I am trying to see if a row already exist in a table, if it does i UPDATE otherwise i INSERT.
I tried
IF ((SELECT COUNT(*) FROM Database.EMP1 AS T WHERE T.SAL = InputBody.DATA.INFO[M].SALARY) = 0) THEN
INSERT...
ELSE UPDATE ....
this works just fine,
Then i tried using PASSTHRU which looked like this
IF (PASSTHRU('SELECT COUNT(*) FROM EMP1 WHERE SAL = ?',InputBody.DATA.INFO[M].SALARY) = 0) THEN
INSERT....
ELSE UPDATE....
The error Says:
An attempt has been made to assign a scalar, row or list quantity to a target of a different basic type.
Shud i use THE keyword? but again that gives me syntax errors...where shud i use THE if i was to use it?
What concerns me even more is that i have another code where i planned to use PASSTHRU to INSERT an XML date field to ORACLE date column using to TO_DATE function, but PASSTHRU always seems to be a mystery, any place i can find clear explanation of the usage of PASSTHRU??? |
|
| Back to top |
|
 |
| martinrydman |
| Posted: Sun Aug 15, 2004 3:08 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi,
a good place to start is the manual... 
But the basic thing to keep in mind is that if PASSTRHRU is used to SELECT data, it will allways return a vector, so yo must allways do something like this:
| Code: |
SET Environment.Data[] = PASSTRHRU('SELECT...
|
You can then inspect the Data vecctor:
| Code: |
IF Environment.Data[1].*[1] = 0 THEN...
|
This would test the first field of the first record of the returned resultset. If you know the names of your returned fields, use them:
| Code: |
IF Environment.Data[1].MyField = 0 THEN...
|
Note that you must use this method even if you know that you'll get back only one row of data.
HTH
/Martin |
|
| Back to top |
|
|
| paustin_ours |
| Posted: Sun Aug 15, 2004 5:46 am Post subject: |
|
|
Yatiri
Joined: 19 May 2004
Posts: 667
Location: columbus,oh
|
I came accross this in the ESQL reference manual under examples of PASSTHRU statement usage.
SET OutputRoot.XML.Result.Data
= PASSTHRU(’SELECT AccountNum FROM user2.AccountData
WHERE AccountId = ?’, InputBody.Data.Account.Id);
it has no [], kinda misleading...is this any different from my scenario?
any better document you can suggest me read?
--------
About the Date conversion,
Do you think this construct will work?
PASSTHRU('INSERT INTO EMP1 (IN_DATE,SERVICE_DATE,END_DATE)
VALUES (to_date(?,‘yyyymmdd’),to_date(?,‘yyyymmdd’),
to_date(?,‘yyyymmdd’))', InputBody.IN_DATE, InputBody.SERVICE_DATE, InputBody.END_DATE);
my incoming message is of the form yyyymmdd |
|
| Back to top |
|
|
| martinrydman |
| Posted: Sun Aug 15, 2004 10:04 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi,
yep, looks like a typo to me. No, the manual is usually the way to go, maybe somone else has a better idea?
Your date-construct looks OK at a glance.
Good luck!
/Martin |
|
| Back to top |
|
|
| mq_developer |
| Posted: Thu Aug 19, 2004 2:07 pm Post subject: |
|
|
Voyager
Joined: 18 Feb 2002
Posts: 82
|
Martin , you are correct .. PASSTHRU always "assume" to return list of values , irrespective of the of the data being returned.
The reason being , with normal SELECT function there is a keyword "THE" available to distinguish between the scalar & list of values being returned.
Since with PASSTHRU , we are completely evading the broker and statements are getting executed by the database engine (absence of keyword THE) , there is no way in ESQL understand or explicitly mention the data type (list / scalar) of the data being returned.
Hope this helps .. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
