Results 1 to 13 of 13

Thread: [RESOLVED] error sql query too complex

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    71

    Resolved [RESOLVED] error sql query too complex

    I am trying to run a query which can be very long. For example:

    SELECT * FROM Archief WHERE Id=1 Or Id=2 Or Id=3 Or Id=4 Or Id=5 Or Id=6 Or Id=7 Or Id=8 Or Id=9 Or Id=10 Or Id=11 Or Id=12 Or Id=13 Or Id=14 Or Id=15 Or Id=16 Or Id=17 Or Id=18 Or Id=19 Or Id=20 Or Id=21 Or Id=22 Or Id=23 Or Id=24 Or Id=25 Or Id=26 Or Id=27 Or Id=28 Or Id=29 Or Id=30 Or Id=31 Or Id=32 Or Id=33 Or Id=34 Or Id=35 Or Id=36 Or Id=37 Or Id=38 Or Id=39 Or Id=40 Or Id=41 Or Id=42 Or Id=43 Or Id=44 Or Id=45 Or Id=46 Or Id=47 Or Id=48 Or Id=49 Or Id=50 Or Id=51 Or Id=52 Or Id=53 Or Id=54 Or Id=55 Or Id=56 Or Id=57 Or Id=58 Or Id=59 Or Id=60 Or Id=61 Or Id=62 Or Id=63 Or Id=64 Or Id=65 Or Id=66 Or Id=67 Or Id=68 Or Id=69 Or Id=70 Or Id=71 Or Id=72 Or Id=73 Or Id=74 Or Id=75 Or Id=76 Or Id=77 Or Id=78 Or Id=79 Or Id=80 Or Id=81 Or Id=82 Or Id=83 Or Id=84 Or Id=85 Or Id=86 Or Id=87 Or Id=88 Or Id=89 Or Id=90 Or Id=91 Or Id=92 Or Id=93 Or Id=94 Or Id=95 Or Id=96 Or Id=97 Or Id=98 Or Id=99 Or Id=100 Or Id=101 Or Id=102 Or Id=103 Or Id=104 Or Id=105 Or Id=106 Or Id=107 Or Id=108 Or Id=109 Or Id=110 Or Id=111 Or Id=112 Or Id=113 Or Id=114 ORDER BY [Id];

    When I try to load it in a datatable I run into an error:

    System.Data.OleDb.OleDbException: 'The query is too complex

    Code:
    Public Function GetMyTable(strSQL As String) As DataTable
            ' Create new DataTable instance.
            Dim table As New DataTable
            Dim cmd As New OleDbCommand(strSQL, con)
            Using dr As OleDbDataReader = cmd.ExecuteReader
                table.Load(dr)
            End Using
    
            Return table
    End Function
    Is the query really too long or should I create another object to load it?

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: error sql query too complex

    I would use a BETWEEN clause ( WHERE ID BETWEEN (X and Y) )
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    71

    Re: error sql query too complex

    Quote Originally Posted by GaryMazzone View Post
    I would use a BETWEEN clause ( WHERE ID BETWEEN (X and Y) )
    That's not possible in my case since the records are selected based on some criteria:

    Code:
              If Not File.Exists(sArchive & sDoc) = True Then
                    iNum += 1
                    sql = sql & "Id=" & row.Item("Id") & " Or "
                End If

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: error sql query too complex

    well using "Select *" is never a good idea, you are selecting all the field in the Table

    but here you go,
    see if this works, I didn't test it
    Code:
     SELECT .*, Archief.id, Archief.id FROM Archief 
              WHERE (((Archief.id)>=1 And (Archief.id)<115) AND ((Archief.id)=3));
    if there is a Id = 3 it will show that record
    if not your query will return nothing


    EDIT:
    what is this for, something with files ?
    Last edited by ChrisE; Apr 22nd, 2021 at 11:02 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: error sql query too complex

    Use IN:

    https://www.w3schools.com/Sql/sql_in.asp

    Build up your array of values rather than concatenating a bunch of OR statements together. It will be faster and FAR more manageable.
    My usual boring signature: Nothing

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: error sql query too complex

    Quote Originally Posted by ChrisE View Post
    well using "Select *" is never a good idea, you are selecting all the field in the Table
    I did a bit of testing on that, and it isn't always going to be true. If you are getting a bunch of fields that you don't need, then that is a waste, but if you need all the fields, then SELECT * isn't going to perform worse than spelling out all the fields.
    My usual boring signature: Nothing

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: error sql query too complex

    Quote Originally Posted by Shaggy Hiker View Post
    I did a bit of testing on that, and it isn't always going to be true. If you are getting a bunch of fields that you don't need, then that is a waste, but if you need all the fields, then SELECT * isn't going to perform worse than spelling out all the fields.
    I never really tested it, I just write out the fields I need
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    71

    Re: error sql query too complex

    Quote Originally Posted by Shaggy Hiker View Post
    Use IN:

    https://www.w3schools.com/Sql/sql_in.asp

    Build up your array of values rather than concatenating a bunch of OR statements together. It will be faster and FAR more manageable.
    Still returned the same error:

    Code:
    sSQLMissing = "SELECT * FROM Archief WHERE Id In (" & sql & ") ORDER BY [Id];"

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: error sql query too complex

    What is in the variable sql when it happens?

    What database system are you running this against?

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    71

    Re: error sql query too complex

    Quote Originally Posted by si_the_geek View Post
    What is in the variable sql when it happens?

    What database system are you running this against?
    MS Access

    SELECT * FROM Archief WHERE Id=1 Or Id=2 Or Id=3 Or Id=4 Or Id=5 Or Id=6 Or Id=7 Or Id=8 Or Id=9 Or Id=10 Or Id=11 Or Id=12 Or Id=13 Or Id=14 Or Id=15 Or Id=16 Or Id=17 Or Id=18 Or Id=19 Or Id=20 Or Id=21 Or Id=22 Or Id=23 Or Id=24 Or Id=25 Or Id=26 Or Id=27 Or Id=28 Or Id=29 Or Id=30 Or Id=31 Or Id=32 Or Id=33 Or Id=34 Or Id=35 Or Id=36 Or Id=37 Or Id=38 Or Id=39 Or Id=40 Or Id=41 Or Id=42 Or Id=43 Or Id=44 Or Id=45 Or Id=46 Or Id=47 Or Id=48 Or Id=49 Or Id=50 Or Id=51 Or Id=52 Or Id=53 Or Id=54 Or Id=55 Or Id=56 Or Id=57 Or Id=58 Or Id=59 Or Id=60 Or Id=61 Or Id=62 Or Id=63 Or Id=64 Or Id=65 Or Id=66 Or Id=67 Or Id=68 Or Id=69 Or Id=70 Or Id=71 Or Id=72 Or Id=73 Or Id=74 Or Id=75 Or Id=76 Or Id=77 Or Id=78 Or Id=79 Or Id=80 Or Id=81 Or Id=82 Or Id=83 Or Id=84 Or Id=85 Or Id=86 Or Id=87 Or Id=88 Or Id=89 Or Id=90 Or Id=91 Or Id=92 Or Id=93 Or Id=94 Or Id=95 Or Id=96 Or Id=97 Or Id=98 Or Id=99 Or Id=100 Or Id=101 Or Id=102 Or Id=103 Or Id=104 Or Id=105 Or Id=106 Or Id=107 Or Id=108 Or Id=109 Or Id=110 Or Id=111 Or Id=112 Or Id=113 Or Id=114 ORDER BY [Id];
    ...... and so on

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: error sql query too complex

    Quote Originally Posted by Shaggy Hiker View Post
    I did a bit of testing on that, and it isn't always going to be true. If you are getting a bunch of fields that you don't need, then that is a waste, but if you need all the fields, then SELECT * isn't going to perform worse than spelling out all the fields.
    The danger though is if your code depends on fields being in a particular position. By using * you can't be guaranteed that fields will always be in the same position. Granted, you shouldn't be using positional indexes in code in the first place, but if you then throw it into a grid... you run the risk of columns jumping around unexpectedly.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: error sql query too complex

    Quote Originally Posted by clausowitz View Post
    MS Access

    SELECT * FROM Archief WHERE Id=1 Or Id=2 Or Id=3 Or Id=4 Or Id=5 Or Id=6 Or Id=7 Or Id=8 Or Id=9 Or Id=10 Or Id=11 Or Id=12 Or Id=13 Or Id=14 Or Id=15 Or Id=16 Or Id=17 Or Id=18 Or Id=19 Or Id=20 Or Id=21 Or Id=22 Or Id=23 Or Id=24 Or Id=25 Or Id=26 Or Id=27 Or Id=28 Or Id=29 Or Id=30 Or Id=31 Or Id=32 Or Id=33 Or Id=34 Or Id=35 Or Id=36 Or Id=37 Or Id=38 Or Id=39 Or Id=40 Or Id=41 Or Id=42 Or Id=43 Or Id=44 Or Id=45 Or Id=46 Or Id=47 Or Id=48 Or Id=49 Or Id=50 Or Id=51 Or Id=52 Or Id=53 Or Id=54 Or Id=55 Or Id=56 Or Id=57 Or Id=58 Or Id=59 Or Id=60 Or Id=61 Or Id=62 Or Id=63 Or Id=64 Or Id=65 Or Id=66 Or Id=67 Or Id=68 Or Id=69 Or Id=70 Or Id=71 Or Id=72 Or Id=73 Or Id=74 Or Id=75 Or Id=76 Or Id=77 Or Id=78 Or Id=79 Or Id=80 Or Id=81 Or Id=82 Or Id=83 Or Id=84 Or Id=85 Or Id=86 Or Id=87 Or Id=88 Or Id=89 Or Id=90 Or Id=91 Or Id=92 Or Id=93 Or Id=94 Or Id=95 Or Id=96 Or Id=97 Or Id=98 Or Id=99 Or Id=100 Or Id=101 Or Id=102 Or Id=103 Or Id=104 Or Id=105 Or Id=106 Or Id=107 Or Id=108 Or Id=109 Or Id=110 Or Id=111 Or Id=112 Or Id=113 Or Id=114 ORDER BY [Id];
    ...... and so on
    The suggestion to use "In" wasn't intended for you to then keep the rest of your SQL statement exactly as it was in your first post.

    If you use this line:

    Code:
    sSQLMissing = "SELECT * FROM Archief WHERE Id In (" & sql & ") ORDER BY [Id];"
    Then the contents of your "sql" variable should look like this:

    Code:
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
    ...and so on.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    71

    Re: error sql query too complex

    I get it. Now its working fine.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width