sql select with where condition being too long

I am querying a db2 db via odbc and my query is including a where condition which is too long (15k names).
is there a way to rephrase the query to get the driver to accept this long list.
current query is:
select sknum, skcli, sktit, skdatc, skmont, skart, skaf, skqte, skgenr from srl where (skgenr = 'A' or skgenr = 'V')
 and (skdatc between 20070101 and 20100331)
 and (skcli='xxxxx' or skcli='yyyyyyyyy' or ..... 15000 names ......... or skcli='zzzzzzz' )


LVL 28
lesouefAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi lesouef,

You list 15,000 separate conditions on a WHERE clause?  All I can say is "wow"....

You'll find it hugely more efficient if you'll put those names into a table and inner join the table to your query.  If you want the exception rows, outer join the table to your query and select the rows where the value is NULL.


Kent
0
whowho12Commented:
Where are you getting these 15k names from? Are these names in another table? If they are not, could you feed them to a temporary table?

If they are in another table then the simplest solution would be to do a JOIN between the two tables.

Something on the lines of:

SELECT mynames.skcli, srl.sknum, srl.sktit, srl.skdatc, srl.skmont, srl.skart, srl.skaf, srl.skqte, srl.skgenr
FROM mynames
LEFT JOIN srl ON mynames.skcli=srl.skcli
WHERE (skgenr = 'A' or skgenr = 'V') AND (skdatc between 20070101 and 20100331)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave FordSoftware Developer / Database AdministratorCommented:

.... alternately, you could use the IN clause to simplify the expression

d.g.

(skcli in ('xxxxx', 'yyyyyyyyy', 'zzzzzzz' )

HTH,
DaveSlash
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

_agx_Commented:
(no points)

Yes, I agree an IN clause is more elegant. But obviously it too has limits.  15k is simply too many items. So IMO the first two suggestions of re-factoring are the best solution here.
0
Anthony PerkinsCommented:
>>Zones: DB2 Database, MS SQL Server, MySQL Server<<
Pray tell what has this question got to do with MS SQL Server or MySQL Server for that matter?
0
lesouefAuthor Commented:
to answer all these:
- I have also tried IN ('xxxx', 'xxxx'...) a bit faster, but not a big change. I do it now but batches of 100 at a time to work around the limits.
- where this list come from: a table, yes, but from another db on another system, and I can't make a tmp table in db2 as I am read-only on this main db. so I read it from the 2nd server and use this as arguments to query the 1st server. weird world, but this is the result of a badly managed company.
 - and is it in MSSQL because the list source is on a MSSQL server, but yes this is not the problem. actually the question is more a generic SQL question, but I did not find any software category like that.
0
_agx_Commented:
> I do it now but batches of 100 at a time to work around the limits.

   If you cannot create a temp table, or access the other db directly in your query (like a linked server in
   MS SQL).. it sounds like that's the best you can do.
0
lesouefAuthor Commented:
no idea, I have no as400/db2 admin here, and I am totally db2 ignorant myself, so I have to work 'as is', this machine being on its way out...
0
Anthony PerkinsCommented:
>>actually the question is more a generic SQL question<<
Then the "generic" solution is to build a table to contain all the "skcli".  Add all 15K "skcli" values to the table and then do an INNER JOIN using the DB2 SQL syntax.
0
lesouefAuthor Commented:
I thought of doing this, using a linked server from mssql to db2. but this requires to install an ODBC driver on mssql server to mount the db2 database as a linked server and ends up as being as slow as my solution. anyway, I will probably copy the entire table on a local db overnight and do these stats locallly.
0
lesouefAuthor Commented:
the linked server, apart from the speed is probably the most logic, even better than making a temp table, since being instantly available and using for any other crossed query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.