| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| SELECT USING "IN" WITH ARRAY as parameter |
« View previous topic :: View next topic » |
| Author |
Message
|
| WBIBB |
 Posted: Mon Jan 24, 2005 10:17 am Post subject: SELECT USING "IN" WITH ARRAY as parameter |
 |
|
Novice
Joined: 24 Jul 2003
Posts: 19
|
Hi,
I am unable to get SELECT IN to work by modifying the exisiting code with a single parameter.The following is the code and the temporary xml is built as a part of processing (InputRoot.XML.CUST.Detail.ID.TransID[]).The result set being returned is that of the first TransID from the list and not from subsequent TransID's in the list.
I did try other options like explicitly specifying the array with subscript but still couldn't see the subsequent TransID's being queried in the TRACE.The TransID's can be upto 25.
| Code: |
DECLARE TMDate CHAR;
DECLARE AMDate CHAR;
DECLARE fname CHAR;
DECLARE lname CHAR;
DECLARE chardob CHAR;
DECLARE Cust_Nbr CHAR;
DECLARE ID CHAR;
DECLARE Cust_ID CHAR;
DECLARE fname_MIXED CHAR;
DECLARE lname_MIXED CHAR;
SET TMDate = '2002-02-19';
SET AMDate = '2002-01-24';
SET fname = 'BILL';
SET lname = 'LAMBERT';
SET chardob = '1965-14-01';
SET Cust_Nbr = '7770';
SET fname_MIXED = ' ';
SET lname_MIXED = ' ';
SET OutputRoot.XML.Data[] =
PASSTHRU('SELECT ORDER_ID,NBR_TAX,NBR_CBS FROM CLMS.ACCEPT_ORDER WHERE TRANSMISSION_TMS >= to_date(?, ''YYYY-MM-DD'')
AND LTRIM(NBR_CBS,''0'') = ?
AND STMNT_FROM_DT = to_date(?, ''YYYY-MM-DD'')
AND (CUST_LAST_NM = ? OR CUST_LAST_NM = ?)
AND (CUST_FIRST_NM = ? OR CUST_FIRST_NM = ?)
AND CUST_BIRTH_DT = to_date(?, ''YYYY-MM-DD'')
AND CUST_ID IN ? OR UPPER(CUST_ID) IN ?',
TMDate,
TRIM(LEADING '0' FROM Cust_Nbr),
AMDate,
fname, fname_MIXED,
lname, lname_MIXED,chardob,
InputRoot.XML.CUST.Detail.TransID[],
InputRoot.XML.CUST.Detail.TransID[]'); |
Let me know if I am doing something wrong or if there is a better way of doing it.I would appreciate your response and thanks in advance.
Regards
Mike |
|
| Back to top |
|
 |
| Tibor |
| Posted: Tue Jan 25, 2005 5:22 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
Mike,
I'm not sure how does the broker pass an array but I think it does by reference. That's why the Oracle won't send you a correct reply.
You can create a string from array elements separated with colons, e.g. ('A','B','C').
Tibor |
|
| 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
|
|
|
|
