MS Access, SQLite, and Don Quixote #msaccess #subqueries #rmnocase

I wanted to report some further investigations into my quest to be able to write the same query only once for use both directly in SQLite and again for MS Access, with hopefully not a whole lot editing required to make the same basic query work in either environment.

On the issue of what may be called the “nested JOIN” problem where Access wants to base queries on other queries, further testing and some sage advice from Tom suggests that this problem may be solved with nested SELECT’s rather than with nested JOIN’s, and the resultant SQL will work pretty much equally well in either environment. To wit, I am finding that I’m wanting to create queries that can be structured roughly as follows:

SELECT
        some_right_and_left_stuff
FROM
        (SELECT some_left_stuff FROM some_tables_on_the_left) AS L
LEFT JOIN
        (SELECT some_right_stuff FROM some_tables_on_the_right) AS R ON some_right_and_left_stuff;

Of course, both some_tables_on_the_left and some_tables_on_the_right may involve some additional JOINS, but the required parentheses and nesting seem so far to be totally compatible between Access and SQLite. But then, the RMNOCASE issue problem and the BLOB problem must be dealt with differently between Access and SQLite.

Here’s an example of a compatibility problem I didn’t expect. Suppose we replace “SELECT some_left_stuff FROM some_tables_on_the_left” from the third line of the pseudo-code with the following. It’s a real query that will run successfully on both environments. The query returns a list RM Record Numbers associated with a Named Group. In this example, it’s group #6, and I used the group number rather than the group name to avoid having to deal with text strings. Indeed, that’s why this query is so compatible – it references no text strings. I totally stole this query from Tom, by the way. I doubt I could have figured out how to do it on my own.

SELECT
        N.OwnerID AS Recno
 
FROM GroupTable AS G,
     PersonTable AS P INNER JOIN
     NameTable AS N ON P.PersonID=N.OwnerID
 
WHERE
       N.OwnerID >= G.StartID
            AND
       N.OwnerID <= G.EndID
            AND
       G.GroupID = 6

Next, we make the exact same query into one using nested SELECT’s. In this case, it’s totally silly to introduce an extra level of nesting just for this one query. But remember it’s ultimately our purpose to LEFT JOIN this query with another, and the best way to do the LEFT JOIN will surely be with nested SELECT’s.

SELECT
        L.RecNo AS RecNo
 
FROM
       (
         SELECT
                N.OwnerID AS Recno
 
         FROM GroupTable AS G,
              PersonTable AS P INNER JOIN
              NameTable AS N ON P.PersonID=N.OwnerID
 
        WHERE
 
          N.OwnerID >= G.StartID
               AND
          N.OwnerID <= G.EndID
               AND
          G.GroupID = 6
 
       ) AS L

Again, this query “just works” in both environments and it still produces a list of the all the RM Record Numbers associated with one particular Named Group. And again the reason the query works in both environments is that it is only dealing with numeric data. So let’s confuse things by adding one text field. We will start with the original query that was not nested.

SELECT
        N.OwnerID AS Recno,
        N.Surname AS Surname
 
FROM
      GroupTable AS G,
      PersonTable AS P INNER JOIN
      NameTable AS N ON P.PersonID=N.OwnerID
 
WHERE
 
      N.OwnerID >= G.StartID
           AND
      N.OwnerID <= G.EndID
           AND
      G.GroupID = 6

Perhaps a little surprisingly, this query still works in both environments even though I have added a text string to it, namely, NameTable.Surname. But I think it’s more or less by accident that the query works in both environments without any use of COLLATE NOCASE or CAST or StrConv. Which is to say, I’m not doing any manipulation or comparison of NameTable.Surname whatsoever.

Finally, we add a text string to the nested query.

SELECT
        L.RecNo AS RecNo,
        L.Surname AS Surname
 
FROM
      (
       SELECT
               N.OwnerID AS Recno,
               N.Surname AS Surname
 
       FROM
            GroupTable AS G,
            PersonTable AS P INNER JOIN
            NameTable AS N ON P.PersonID=N.OwnerID
 
       WHERE
              N.OwnerID >= G.StartID
                   AND
              N.OwnerID <= G.EndID
                   AND
              G.GroupID = 6
 
      ) AS L

This query still works fine in ACCESS, and indeed it’s more user friendly than before because it’s listing the person’s surname in addition to the person’s Record Number. But in SQLite the query gives one of those obnoxious “no such collation sequence: RMNOCASE” errors. I’m not doing any processing or manipulation or comparison of a text string, but there’s the error, anyway. I’m guessing that SQLite doing something that causes the collation sequence error because it’s converting the data from N.Surname in the innermost SELECT to L.Surname in the outermost SELECT. I can fix the query in SQLite by adding COLLATE NOCASE to Surname, but then the query will no longer work in Access. Apparently there is always going to be a lot of editing required to be able to have both an Access version and an SQLite version of the same query. And the query above is really simple.

Jerry

Discussions & comments from Wikispaces site

Inline comments