CODEWITHSUNDEEP
sqlsqlite
Intrepid
Intrepid

Reputation: 2831

How to specify columns in an SQLite subquery join

I have the following SQL Server query that I want to port over to SQLite 3, but it doesn't like it and complains of a syntax error "near (".

After some debugging it appears that the problem is that SQLite doesn't like the column specifiers after the sub query alias (I have indicated these with a double asterisk).

What do I need to do to make this query work with SQLite?

    SELECT  rowSet.DBId_0,
        rowSet.TableID_0, 
        CASE WHEN rowSet.HasIdentity_0 != rowSet.HasIdentity_1 THEN 1 ELSE 0 END 'Result'
FROM (
    SELECT * 
    FROM
    (
        SELECT 'Master' AS 'Db', z.[DBId], z.[TableId], MAX(z.[MaxIdentity]) AS 'HasIdentity' 
        FROM
        (
            SELECT so.[DBId], sc.[TableId], MAX(CAST(sc.[Identity] AS TINYINT)) AS MaxIdentity
            FROM syscolumns sc
                JOIN sysobjects so ON sc.[tableid] = so.[id]
            WHERE so.[dbid] = 1
            GROUP BY so.[DBId], sc.[TableId], sc.[Identity]
        ) z
        GROUP BY z.[DBId], z.TableId
    ) **src (Db_0, DBId_0, TableID_0, HasIdentity_0)**
    JOIN 
    (
        SELECT 'Target' AS 'Db', z.[DBId], z.[TableId], MAX(z.[MaxIdentity]) AS 'HasIdentity' 
        FROM
        (
            SELECT so.[DBId], sc.[TableId], MAX(CAST(sc.[Identity] AS TINYINT)) AS MaxIdentity
            FROM target.syscolumns sc
                JOIN target.sysobjects so ON sc.[tableid] = so.[id]
            WHERE so.[dbid] = 1
            GROUP BY so.[DBId], sc.[TableId], sc.[Identity]
        ) z
        GROUP BY z.[DBId], z.TableId
    ) **tar (Db_1, DBId_1, TableID_1, HasIdentity_1)** ON tar.[DBId_1] = src.[DBId_0] AND tar.[TableId_1] = src.[TableId_0]
) rowSet

Here is a sample query you can try:

This works (in SQLite):

select col1, col2
from
(
    select 1 as 'col1', 2 as 'col2'
    union
    select 3 as 'col1', 4 as 'col2'
)

This doesn't (in SQLite):

select col1, col2
from
(
    select 1 as 'col1', 2 as 'col2'
    union
    select 3 as 'col1', 4 as 'col2'
) z (col1, col2)

Upvotes: 1

Views: 3565

Answers (2)

CL.
CL.

Reputation: 180172

The standard SQL way of renaming columns is to use column aliases, i.e.:

SELECT z.col1, z.col2
FROM
(
    SELECT 1 AS col1, 2 AS col2
    UNION ALL
    SELECT 3,         4
) AS z

Upvotes: 2

John Tseng
John Tseng

Reputation: 6352

You can alias the columns inside the subquery, but in the table alias, you can't specify the alias again. In the following query, you can specify your table alias outside the subquery, and specify the column aliases inside:

select col1, something
from
(
    select 1 as 'col1', 2 as 'something'
    union
    select 3, 4 
) z

Upvotes: 1

Related Questions