| Author |
Message
|
| ruchir123 |
 Posted: Mon Feb 27, 2012 10:42 pm Post subject: Pass a list in where clause for database query in ESQL |
 |
|
Acolyte
Joined: 04 Jan 2012
Posts: 58
|
Hi All,
I have a requirement where i need to query database for some specific values which are not fixed but will come as list.
I tried this query :
| Code: |
DECLARE result ROW ;
set result.temp[1] = '0001';
set result.temp[2] = '0002';
set result.temp[3] = '0003';
SET Environment.data[] = (select t.abc from Database.table1 AS t where t.def IN (select * from result.temp[])) |
but when i am writing this code it is showing error at delpoyment time saying
| Quote: |
| Incompatible operands for IN predicate |
Please  how we can pass a list to fetch required data.
Thanks a ton in advance . |
|
| Back to top |
|
 |
| ajit1981 |
| Posted: Mon Feb 27, 2012 11:01 pm Post subject: |
|
|
Apprentice
Joined: 09 Apr 2010
Posts: 41
Location: Bangalore, Inida
|
Please use PASSTHRU function.
ex PASSTHRU(sql stament).
_________________
Regards,
Ajit |
|
| Back to top |
|
|
| ruchir123 |
| Posted: Mon Feb 27, 2012 11:13 pm Post subject: |
|
|
Acolyte
Joined: 04 Jan 2012
Posts: 58
|
Hi Ajit,
Thanks for your reply.
I tried with passthru function also, but i am not sure how i can pass list in that also. Please suggest. |
|
| Back to top |
|
|
| vishnurajnr |
| Posted: Mon Feb 27, 2012 11:23 pm Post subject: |
|
|
Centurion
Joined: 08 Aug 2011
Posts: 134
Location: Trivandrum
|
The SQL IN Operator is expecting multiple values separated by Comma.
If Your number of parameters are fixed (3 parameters from your post), and only value is coming Dynamically, assign it to String variables or array and pass the elements separated by comma inside the IN Operator on your SQL query.
Suggest you to check the validity of your SQL statement by checking it via SQL developer or Toad tools before passing as ESQL database queries. |
|
| Back to top |
|
|
| ruchir123 |
| Posted: Mon Feb 27, 2012 11:42 pm Post subject: |
|
|
Acolyte
Joined: 04 Jan 2012
Posts: 58
|
Hi Vishnu,
Actually in my case number of parameters are not fixed, here i took it as 3 just for example.
| vishnurajnr wrote: |
| The SQL IN Operator is expecting multiple values separated by Comma. |
But when i tried this query in sql like :
select * from table1 where table1.column1 in (select * from table2)
works fine and also returning desired result.
So i think it is not like that IN operator expect only multiple values separated by comma.
So please suggest if it is the case with ESQL while passing it expect only multiple values separated by comma. |
|
| Back to top |
|
|
| vishnurajnr |
| Posted: Mon Feb 27, 2012 11:50 pm Post subject: |
|
|
Centurion
Joined: 08 Aug 2011
Posts: 134
Location: Trivandrum
|
|
| Back to top |
|
|
| Esa |
| Posted: Mon Feb 27, 2012 11:53 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
ESQL IN operator accepts a list attribute. See the exaples here
According to mgk this feature was added in 7.0.0.1 or 7.0.0.2.
So for example this should probably work (I didn't test):
| Code: |
DECLARE result ROW ;
set result.temp[1] = '0001';
set result.temp[2] = '0002';
set result.temp[3] = '0003';
SET Environment.data[] = (select t.abc from Database.table1 AS t where t.def IN (result.temp[]) |
|
|
| Back to top |
|
|
| ruchir123 |
| Posted: Tue Feb 28, 2012 12:29 am Post subject: |
|
|
Acolyte
Joined: 04 Jan 2012
Posts: 58
|
| Hi Esa, I tried that too , But it also results same. Same error at deployment. |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Feb 28, 2012 12:41 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
What is your broker version?
Did you try this as in the InfoCenter examples;
| Code: |
DECLARE result ROW ;
set result.temp[1] = '0001';
set result.temp[2] = '0002';
set result.temp[3] = '0003';
SET Environment.data[] = (select t.abc from Database.table1 AS t where t.def IN (select T from result.temp[] as T)) |
|
|
| Back to top |
|
|
| Esa |
| Posted: Tue Feb 28, 2012 12:44 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
Or this:
| Code: |
set InputLocalEnvironment.result.temp[1] = '0001';
set InputLocalEnvironment.result.temp[2] = '0002';
set InputLocalEnvironment.result.temp[3] = '0003';
SET Environment.data[] =
(select t.abc from Database.table1 AS t where t.def IN (select T from InputLocalEnvironment.result.temp[] as T)) |
|
|
| Back to top |
|
|
| ruchir123 |
| Posted: Tue Feb 28, 2012 12:55 am Post subject: |
|
|
Acolyte
Joined: 04 Jan 2012
Posts: 58
|
I m working on V6.1
Yup, i tried both the code you provided but all in vain. |
|
| Back to top |
|
|
| ruchir123 |
| Posted: Tue Feb 28, 2012 12:57 am Post subject: |
|
|
Acolyte
Joined: 04 Jan 2012
Posts: 58
|
So this works with 7.0.0.1 or 7.0.0.2
what other alternate we can take it to work with 6.1.
Any suggestion please... |
|
| Back to top |
|
|
| McueMart |
| Posted: Tue Feb 28, 2012 5:35 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Possibly write a function which takes a row as input and returns a character string with values comma delimited?
e.g.
Input:
set result.temp[1] = '0001';
set result.temp[2] = '0002';
set result.temp[3] = '0003';
SET MyOutput = FlattenRow(result);
And the output would look like:
"'0001','0002','0003'" |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Feb 28, 2012 5:49 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
No. The IN clause would have just one variable: '''0001'',''0002'',''0003'''
Find similar suggestions here.
There are two possible ways to do it in V6.1: EVAL and java. |
|
| Back to top |
|
|
| McueMart |
| Posted: Tue Feb 28, 2012 6:39 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Or how about flattening the row and using PASSTHRU?
| Code: |
DECLARE tmp CHARACTER '1,2,3';
DECLARE query CHARACTER 'select t.id from DB2ADMIN.TEST AS t where t.id IN (' || tmp || ')';
SET Environment.data[] = PASSTHRU(query);
DECLARE tmp2 CHARACTER '''1'',''2'',''3''';
DECLARE query2 CHARACTER 'select t.id from DB2ADMIN.TEST2 AS t where t.id IN (' || tmp2 || ')';
SET Environment.data2[] = PASSTHRU(query2);
|
That looks like it should work fine to me... |
|
| Back to top |
|
|
|
|
