C# - SQLite error when using table alias: no such column

Question

I run the following query in SQLite Management Studio (a 2009 version) and it works fine and returns the desired result set, but when I execute from C# it gives me the following error:

No such column Q0.IntegrationItemCategoryLevelID.

It appears to be that it can't "see" the table aliases in the subqueries - I did some further tests and it can't see the other joined tables either (e.g. Q0). I tried separating it out to two queries but it absolutely killed performance. Does anyone have a good idea on how to fix this?

            SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc 
            FROM ((_Item I
            INNER JOIN (_ItemToItemCategory Q0 
                INNER JOIN _ItemCategory A0 ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID) ON Q0.IntegrationItemID = I.IntegrationItemID)
            INNER JOIN (_ItemToItemCategory Q1 
                INNER JOIN _ItemCategory A1 ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID) ON Q1.IntegrationItemID = I.IntegrationItemID)
            WHERE Q0.IntegrationItemCategoryLevelID = 14 AND A0.ShortDesc = 'LG05'
            AND Q1.IntegrationItemCategoryLevelID IN (9,4,5,7,10) ORDER BY Q1.IntegrationItemCategoryLevelID

Edit: removed the extra parenthesis, same result.

            SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc 
            FROM Item I
            INNER JOIN (ItemToItemCategory Q0 
                INNER JOIN ItemCategory A0 ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID) ON Q0.IntegrationItemID = I.IntegrationItemID
            INNER JOIN (ItemToItemCategory Q1 
                INNER JOIN ItemCategory A1 ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID) ON Q1.IntegrationItemID = I.IntegrationItemID
            WHERE Q0.IntegrationItemCategoryLevelID ='14' AND A0.ShortDesc = 'LG05'
            AND Q1.IntegrationItemCategoryLevelID IN (9,4,5,7,10) ORDER BY Q1.IntegrationItemCategoryLevelID

Why are you writing your query with so many nested parentheses? — Tim Biegeleisen
– Tim Biegeleisen, Commented May 30, 2018 at 1:47
Good question - would you believe me if I said that this was actually a previous developer's work, for an Access database no less? Anyways, I had tried to remove some of the extra parenthesis and it didn't have an effect. I'll post it. — Isaac
– Isaac, Commented May 30, 2018 at 2:02
What SQLite version are you using in both cases? (SELECT sqlite_version();) — CL.
– CL., Commented May 30, 2018 at 18:41
Looks like SqlLite Manager is using 3.8.3.1, the code is using 3.6.23.1. — Isaac
– Isaac, Commented May 30, 2018 at 20:53

Gordon Linoff · Accepted Answer · 2018-05-30 02:05:40Z

1

This is your query:

SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc 
FROM ((_Item I INNER JOIN 
       (_ItemToItemCategory Q0 INNER JOIN
        _ItemCategory A0
        ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID
       )
       ON Q0.IntegrationItemID = I.IntegrationItemID
      ) INNER JOIN
      (_ItemToItemCategory Q1 INNER JOIN
       _ItemCategory A1
       ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID
      )
      ON Q1.IntegrationItemID = I.IntegrationItemID
     )
WHERE Q0.IntegrationItemCategoryLevelID = 14 AND
      A0.ShortDesc = 'LG05' AND
      Q1.IntegrationItemCategoryLevelID IN (9, 4, 5, 7, 10)
ORDER BY Q1.IntegrationItemCategoryLevelID;

It is a bit hard to say what the exact error is. I think it is because I is defined at one too many nested levels for the last JOIN.

But, these are all inner joins. And you are not using MS Access, so you can rearrange them nicely:

SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc 
FROM _Item I INNER JOIN 
     _ItemToItemCategory Q0
     ON Q0.IntegrationItemID = I.IntegrationItemID INNER JOIN
     _ItemCategory A0
     ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID INNER JOIN
     _ItemToItemCategory Q1
     ON Q1.IntegrationItemID = I.IntegrationItemID INNER JOIN
      _ItemCategory A1
     ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID
WHERE Q0.IntegrationItemCategoryLevelID = 14 AND
      A0.ShortDesc = 'LG05' AND
      Q1.IntegrationItemCategoryLevelID IN (9, 4, 5, 7, 10)
ORDER BY Q1.IntegrationItemCategoryLevelID;

If you had outer joins, it wouldn't be so easy to rearrange them.

answered May 30, 2018 at 2:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Isaac Over a year ago

Thanks for the answer. Although the query works, the performance has increased by several minutes, only when running from C#. Works quick in SQLite Management Studio. Thoughts?

Gordon Linoff Over a year ago

@Isaac . . . I'm missing something. You claim that your version has a syntax error. Hence, how can you compare the performance?

Isaac Over a year ago

Sorry, should have clarified. This query was originally written to work with Access databases. That ran pretty quick, < 1s. We are attempting to convert it to a SQLLite query. Also, mentioned above but SqlLite Manager is using 3.8.3.1, the code is using 3.6.23.1. Also I haven't mentioned, this is using a local, client side database because of scenarios where internet connectivity is not guaranteed. Because of many other projects dependent on the file, I'm not sure we can safely upgrade to a newer version

Isaac Over a year ago

It seems the query is running really long because of the number of records returned. If I remove DISTINCT, the query runs fine but when the data reader starts trying to read, it freezes a long time. Running a COUNT() query in SqlLite Manager Studio also does the same thing. Still working on it.

Gordon Linoff Over a year ago

@Isaac . . . If you have a question about performance, you should ask another question. This particular question is about syntax.

|

Collectives™ on Stack Overflow

C# - SQLite error when using table alias: no such column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related