Query is too complex

[h_k]

[SOLVED] Query is too complex

Hello all,

I'm having an issue with a query that throws up "Query is too complex".

If I remove some of the WHERE conditions, the query runs which leads me to believe that the query is too long.

Is there a length limit that I'm unaware of?

Many thanks in advance for any help

SELECT S.*, D.*, F.*, IIf([F.fillstart] > [F.fillstop], ([F.fillstop] - [F.fillstart] + 1) * 1440, ([F.fillstop] - [F.fillstart]) * 1440) AS FillDuration
FROM (DFRs D INNER JOIN Shifts S ON D.DFRid = S.DFRid) INNER JOIN Fills F ON S.ShiftID = F.ShiftID
WHERE (S.[AllocatedBase] = 4 OR S.[AllocatedBase] = 2 OR S.[AllocatedBase] = 3 OR S.[AllocatedBase] = 1) AND (D.[AssignedVan] = 9 OR D.[AssignedVan] = 8 OR D.[AssignedVan] = 1 OR D.[AssignedVan] = 2 OR D.[AssignedVan] = 5 OR D.[AssignedVan] = 6 OR D.[AssignedVan] = 7) AND (S.[driver] = 73 OR S.[loader] = 73 OR S.[driver] = 21 OR S.[loader] = 21 OR S.[driver] = 74 OR S.[loader] = 74 OR S.[driver] = 34 OR S.[loader] = 34 OR S.[driver] = 60 OR S.[loader] = 60 OR S.[driver] = 31 OR S.[loader] = 31 OR S.[driver] = 68 OR S.[loader] = 68 OR S.[driver] = 33 OR S.[loader] = 33 OR S.[driver] = 50 OR S.[loader] = 50 OR S.[driver] = 36 OR S.[loader] = 36 OR S.[driver] = 72 OR S.[loader] = 72 OR S.[driver] = 32 OR S.[loader] = 32 OR S.[driver] = 67 OR S.[loader] = 67 OR S.[driver] = 40 OR S.[loader] = 40 OR S.[driver] = 4 OR S.[loader] = 4 OR S.[driver] = 22 OR S.[loader] = 22 OR S.[driver] = 35 OR S.[loader] = 35 OR S.[driver] = 43 OR S.[loader] = 43 OR S.[driver] = 54 OR S.[loader] = 54 OR S.[driver] = 16 OR S.[loader] = 16 OR S.[driver] = 62 OR S.[loader] = 62 OR S.[driver] = 46 OR S.[loader] = 46 OR S.[driver] = 2 OR S.[loader] = 2 OR S.[driver] = 20 OR S.[loader] = 20 OR S.[driver] = 69 OR S.[loader] = 69 OR S.[driver] = 71 OR S.[loader] = 71 OR S.[driver] = 15 OR S.[loader] = 15 OR S.[driver] = 30 OR S.[loader] = 30 OR S.[driver] = 8 OR S.[loader] = 8 OR S.[driver] = 49 OR S.[loader] = 49 OR S.[driver] = 57 OR S.[loader] = 57 OR S.[driver] = 9 OR S.[loader] = 9 OR S.[driver] = 29 OR S.[loader] = 29 OR S.[driver] = 18 OR S.[loader] = 18 OR S.[driver] = 23 OR S.[loader] = 23 OR S.[driver] = 28 OR S.[loader] = 28 OR S.[driver] = 25 OR S.[loader] = 25 OR S.[driver] = 5 OR S.[loader] = 5 OR S.[driver] = 3 OR S.[loader] = 3 OR S.[driver] = 51 OR S.[loader] = 51 OR S.[driver] = 55 OR S.[loader] = 55 OR S.[driver] = 1 OR S.[loader] = 1 OR S.[driver] = 52 OR S.[loader] = 52 OR S.[driver] = 58 OR S.[loader] = 58 OR S.[driver] = 37 OR S.[loader] = 37 OR S.[driver] = 10 OR S.[loader] = 10 OR S.[driver] = 6 OR S.[loader] = 6 OR S.[driver] = 13 OR S.[loader] = 13 OR S.[driver] = 19 OR S.[loader] = 19 OR S.[driver] = 24 OR S.[loader] = 24 OR S.[driver] = 14 OR S.[loader] = 14 OR S.[driver] = 59 OR S.[loader] = 59)
ORDER BY D.DFRdate DESC, F.fillstart DESC;

 

[namliam]

Would it really hurt you to apply any sort of formatting to your sql instead of splashing it on the forum??

First glance, looks like a lot of OR on the same column, have you considered using the IN contruct instead?
I.e. instead of

WHERE (S.[AllocatedBase] = 4 OR S.[AllocatedBase] = 2 OR S.[AllocatedBase] = 3 OR S.[AllocatedBase] = 1)

Format it like so:

WHERE (    S.[AllocatedBase] = 4 
        OR S.[AllocatedBase] = 2 
        OR S.[AllocatedBase] = 3 
        OR S.[AllocatedBase] = 1)

As an In construct

WHERE (    S.[AllocatedBase] in (1,2,3,4)  )

I hope this sql was generated instead of manual entra, that is a LOT of OR clauses!

 

[h_k]

Thanks for the quick reply namliam.

I apologise for the formatting - I was torn between having it ridiculously long vertically or simply splitting it by SELECT/FROM/WHERE/ORDER given that the WHERE is very repetitive. I'll adjust the formatting next time.

Yes, it was generated depending on the selections in various list boxes.

I hadn't thought about trying the In construct - I'll give it a go and see how I get on.

Thank you!

 

[h_k]

Worked great - thank you very much.