| Author |
Message
|
| new2MB |
 Posted: Thu Aug 13, 2015 2:21 am Post subject: Select from database by comparing a column against list |
 |
|
Novice
Joined: 04 Jun 2014
Posts: 11
|
Please help me to understand how can i right a select statement in esql if i want to select based on a list.
my select query is like this
Select * from table where column in (Environment.Variables.list)
and i have to initialze this list on run time based on incoming input.
where i right a direct select query the where clause is not getting evaluated rather all the records from tables are selected and then each record is compared to the list.
Can we have a query with in clause and use passthru. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Aug 13, 2015 3:56 am Post subject: Re: Select from database by comparing a column against list |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| new2MB wrote: |
Can we have a query with in clause and use passthru. |
Yes.
Why not give us a bit more detail/example of what you are thinking of. Then we can probably help you more directly.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| new2MB |
| Posted: Thu Aug 13, 2015 8:49 am Post subject: |
|
|
Novice
Joined: 04 Jun 2014
Posts: 11
|
for following query
select * from table 1 where id in(select t.id from Environment.Value[] as t)
on debug i can see CurrentSelect which select each row from table ad then compares to the inner query which is a performance hit and too much time to process.
I want a query which gives result in one execution. |
|
| Back to top |
|
|
| maurito |
| Posted: Mon Aug 17, 2015 6:09 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| new2MB wrote: |
on debug i can see CurrentSelect which select each row from table ad then compares to the inner query which is a performance hit and too much time to process.
|
yes, it compares to the inner query, if not, how do you think it will find whether the value is in the IN clause ?! how do you propose it should work without comparing ?
Yes, you can do a PASSTHRU, do you think it will be more efficient ?
| new2MB wrote: |
Select * from table where column in (Environment.Variables.list)
and i have to initialze this list on run time based on incoming input.
|
why can you not use the input message in the inner select rather than building a list in the Environment ? |
|
| Back to top |
|
|
| new2MB |
| Posted: Tue Aug 18, 2015 11:49 pm Post subject: |
|
|
Novice
Joined: 04 Jun 2014
Posts: 11
|
I want to know how can i pass a list in passthru.
How can i append a list to the character query.
When i right like this
SET chrQuery = 'Select * from dbo.MyTable where id in (' || Environment.VAriable.ID[] || ')'
it gives an error -- Invalid or incompatible data types for '||' operator.
Either the data types of the operands of the operator were not valid for the operator, or the datatypes were incompatible
Please let me know how to do this. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Aug 19, 2015 1:50 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
You have to build the complete query inorder to use passthry.
You can use variable substitution but you need to know how many parameters are going to be substituted.
so in your case you can#t just specify
| Code: |
|| Environment.VAriable.ID[] ||
|
You have to create a CHAR variable that contains the contents of the list with all the punctuation needed.
If the List has two items [1]='AAAA' , [2]='BBBB'
Then you need to create a variable that looks like
so this sort of ESQL is needed
| Code: |
declare cParam CHAR
set cParam = ''' || Environment.VAriable.ID[1].Param || ''' , ';
set cParam = cParam || ''' || Environment.VAriable.ID[2].Param || ''' ';
|
This should get you the idea.
you could put this into a 'while' or 'for' loop structure.
I hope this gives you the pointers you need.
There might be an easier way to do this but this is how I do it because we log the complete SQL Commands for auditing purposes. This also makes finding problems a lot easier.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
|
|
