| Author |
Message
|
| prabhakarp |
 Posted: Wed Jul 30, 2003 4:29 pm Post subject: access database using IN Clause |
 |
|
Newbie
Joined: 30 Jul 2003
Posts: 7
|
I am new to MQ. I am getting the exception, when i am trying to use IN Clause( Is it CASTing problem????) Can any body help me how to solve this problem.
DECLARE ARIBASUPP CHAR;
SET Environment.Variables.SUPPLIST[] = (select AN.erpowner_id from Database.um.erpmap as AN where AN.buyer_id = UM_UCOMPANYREF and AN.erpnum =ACCOUNTNUMBER and AN.address_id IS NULL);
SET SUPPSIZE= CARDINALITY(Environment.Variables.SUPPLIST[]);
IF SUPPSIZE > 1 THEN
DECLARE I INTEGER;
SET I = 1;
SET ARIBASUPP='';
WHILE I <= SUPPSIZE DO
SET UM_CEUREFITEM=CAST (Environment.Variables.SUPPLIST[I].erpowner_id AS INTEGER);
SET TEMPHOLD= THE(select ITEM AM.ARIBA_ID FROM Database.um.ARIBAMAP as AM where AM.UM_ID = UM_CEUREFITEM and AM.TYPE = 'USER');
SET ARIBASUPP=ARIBASUPP||','||TEMPHOLD;
SET I = I+1;
END WHILE;
SET ARIBASUPP=Substring(ARIBASUPP FROM 2 FOR LENGTH(ARIBASUPP));
END IF;
After this i get the data like '5466,5467' then i am using database some where in my code.
IF SUPPSIZE > 1 THEN
SET Environment.Variables.FINALARIBASUP2=ARIBASUPP;
SET CEUREFITEM= THE(select ITEM sp.sellerid from Database.SPREFCOUNT_VIEW as sp where sp.buyerid=UREFITEM and sp.buyercompanyref=UCOMPANYREF and sp.buyerpartnum = BUYERPARTNUM and sp.countryref =COUNTRY and sp.sellerid in(ARIBASUPP));
SET Environment.Variables.FINALARIBASUP1=CEUREFITEM;
END IF;
the CEUREFITEM allways getting 0, but i check in the database(oracle) there are two records. Can anybody help me how to solve this problem? |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Jul 31, 2003 2:42 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Which level of WMQI are you using? A log of select defects were fixed in version 5... |
|
| Back to top |
|
|
| prabhakarp |
| Posted: Thu Jul 31, 2003 5:27 am Post subject: accessing Database using IN Clause |
|
|
Newbie
Joined: 30 Jul 2003
Posts: 7
|
We are using MQ 5.1 version.
DECLARE ARIBASUPP CHAR;
SET ARIBASUPP ='5296,5297';
SET CEUREFITEM= THE(select ITEM sp.sellerid from Database.SPREFCOUNT_VIEW as sp where sp.buyerid=100 and sp.buyercompanyref=120 and sp.buyerpartnum = 'test' and sp.countryref =4 and sp.sellerid in(ARIBASUPP));
Thanks,
Prabhakar P |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 31, 2003 8:36 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try this,
| Code: |
SET ARIBASUPP ='5296,5297';
SET CEUREFITEM= THE(select ITEM sp.sellerid from Database.SPREFCOUNT_VIEW as sp where sp.buyerid=100 and sp.buyercompanyref=120 and sp.buyerpartnum = 'test' and sp.countryref =4 and sp.sellerid in({ARIBASUPP}));
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| prabhakarp |
| Posted: Thu Jul 31, 2003 9:02 am Post subject: access database using IN Clause |
|
|
Newbie
Joined: 30 Jul 2003
Posts: 7
|
Kiran,
It is gicing syntax error.
BIP2402E: (330, 224) : Syntax error : '{'.
The token caused a syntax error. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 31, 2003 9:21 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
What is the CSD level on your server?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| prabhakarp |
| Posted: Thu Jul 31, 2003 10:00 am Post subject: access database using IN Clause |
|
|
Newbie
Joined: 30 Jul 2003
Posts: 7
|
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 31, 2003 11:07 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I think I was wrong, you cannot use { here. Try using EVAL statement instead.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| mgk |
| Posted: Mon Aug 04, 2003 7:46 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You cannot use the {...} syntax without a correlation name on the front it. Also you do not have to use EVAL in this instance.
You mentioned an exception in your first post. Can you please post the exception details from the Syslog / Event log here, as knowing what is going wrong exactly is very important. You also mentioned is could be it could be a CAST error, so are you sure that the datatypes are correct, that is the operands to IN must all be comparable with the first operand (the expression before IN).
You could also try removing other sections of your where clause, one section at a time, untill you are sure the problem is in the IN predicate, not another piece of the where clause. |
|
| Back to top |
|
|
|
|
