SQLTeam.com | Weblogs | Forums

Script error using the 'OR' arguement

sql2012

#1

The following script is failing for complexity and not sure why. There are about 39,000 of the 'or' arguments...is there a limit to the numbers of these types of arguments that will cause failures...Please help:

select distinct b.batdat, b.batseq, b.seqnbr, b.linnbr,

a.prvnbr, a.sbsnbr, a.svcfrm, a.svcto,

b.svccod, b.revcod, b.alwamt, b.pidamt

from umcfil a, umcdtl b where a.batdat=b.batdat

and a.batseq=b.batseq and a.seqnbr=b.seqnbr

and

(a.batdat='1/4/2016' and a.batseq='1' and a.seqnbr='36') or

(a.batdat='1/4/2016' and a.batseq='3' and a.seqnbr='20') or

(a.batdat='1/4/2016' and a.batseq='3' and a.seqnbr='43') or

(a.batdat='1/4/2016' and a.batseq='8' and a.seqnbr='3') or

about 39,000 'or' statements .......


#2

What is the error?

Also have you thought about using a table instead of OR's ?


#3

it's just not completing and once I received query too complex error message


#4

t might a good idea to explain the error more in detail:

"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information".


#5

I'd put them in a TABLE, as DJJ55 said, and JOIN that table (instead of all the ORs). It will be hugely faster than 39,000 OR statements in the WHERE clause.

You haven't got any parenthesis around your OR block, I wonder if that would help SQL's parser ... i.e. as a Band-aid)

where a.batdat=b.batdat
and a.batseq=b.batseq and a.seqnbr=b.seqnbr
and

(

(a.batdat='1/4/2016' and a.batseq='1' and a.seqnbr='36') or
(a.batdat='1/4/2016' and a.batseq='3' and a.seqnbr='20') or
(a.batdat='1/4/2016' and a.batseq='3' and a.seqnbr='43') or
(a.batdat='1/4/2016' and a.batseq='8' and a.seqnbr='3') or
...

)

#6

I suppose it is just possible that this might help the parser too:

FROM umcfil a
    JOIN umcdtl b 
         ON a.batdat=b.batdat
        AND a.batseq=b.batseq
        AND a.seqnbr=b.seqnbr
WHERE
    (
        (a.batdat='1/4/2016' and a.batseq='1' and a.seqnbr='36') or
        (a.batdat='1/4/2016' and a.batseq='3' and a.seqnbr='20') or
        (a.batdat='1/4/2016' and a.batseq='3' and a.seqnbr='43') or
        (a.batdat='1/4/2016' and a.batseq='8' and a.seqnbr='3') or
        ...
    )

#7

Hi,

Thanks a bunch...it was the open/close parenthesis...
go figure....thanks again.