MSSQL column name alias: Invalid column name

[Pietro395]

I am using turbodbc on a Linux environment using FreeTDS as a Driver.

I find myself with a table with a DATE and a TIME column, combining these two columns into a new one of the DATETIME type and giving it an alias, when I use this column in a WHERE always getting the following error:

ODBC error
state: 42S22
native error code: 207
message: [FreeTDS][SQL Server]Invalid column name 'pippo'.

This is my query:

SELECT dbo.View_Log.ID, dbo.View_Log.Postazione, 
dbo.View_Log.Data, dbo.View_Log.ora, CAST(Data as DATETIME) + CAST(ora as DATETIME) AS pippo
FROM dbo.View_Log 
WHERE pippo >= ?
AND pippo < ?;

The query works correctly using another client

Can someone help me?

[xhochy]

Is the "other client" also using FreeTDS?

[Pietro395]

Is the "other client" also using FreeTDS?

No, i'm using Dbeaver with a JDBC Driver

[xhochy]

Can you please then check if this is also happening with FreeTDS and pyodbc? I would currently guess that this is a FreeTDS issue as ODBC errors are normally completely inside the ODBC driver, not turbodbc.

[keitherskine]

MS SQL Server does not allow the alias to be used in the WHERE clause. (I believe Oracle does allow this.) Try writing the WHERE clause in longhand, as follows:

SELECT dbo.View_Log.ID, dbo.View_Log.Postazione, 
dbo.View_Log.Data, dbo.View_Log.ora, CAST(Data as DATETIME) + CAST(ora as DATETIME) AS pippo
FROM dbo.View_Log 
WHERE CAST(Data as DATETIME) + CAST(ora as DATETIME) >= ?
AND CAST(Data as DATETIME) + CAST(ora as DATETIME) < ?;

[Pietro395]

Can you please then check if this is also happening with FreeTDS and pyodbc? I would currently guess that this is a FreeTDS issue as ODBC errors are normally completely inside the ODBC driver, not turbodbc.

Same problem! '42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'pippo'. (207) (SQLExecDirectW)")

[keitherskine]

Alternatively, use a subquery or CTE:

WITH T AS
(
SELECT ID, Postazione, Data, ora, CAST(Data as DATETIME) + CAST(ora as DATETIME) AS pippo
FROM dbo.View_Log 
)
SELECT * FROM T
WHERE pippo >= ?
AND pippo < ?;

[Pietro395]

MS SQL Server does not allow the alias to be used in the WHERE clause. (I believe Oracle does allow this.) Try writing the WHERE clause in longhand, as follows:

SELECT dbo.View_Log.ID, dbo.View_Log.Postazione, 
dbo.View_Log.Data, dbo.View_Log.ora, CAST(Data as DATETIME) + CAST(ora as DATETIME) AS pippo
FROM dbo.View_Log 
WHERE CAST(Data as DATETIME) + CAST(ora as DATETIME) >= ?
AND CAST(Data as DATETIME) + CAST(ora as DATETIME) < ?;

Thank you, but this way i have this error:

Unsupported type identifier for column ora @ UNKNOWN TYPE (precision 16, scale 7))

SELECT dbo.View_Log.ID, dbo.View_Log.Postazione, 
dbo.View_Log.Data, dbo.View_Log.ora, CAST(Data as DATETIME) + CAST(ora as DATETIME) AS pippo
FROM dbo.View_Log
WHERE CAST(dbo.View_Log.Data as DATETIME) + CAST(dbo.View_Log.ora as DATETIME) >= ?
AND CAST(dbo.View_Log.Data as DATETIME) + CAST(dbo.View_Log.ora as DATETIME) <= ?;

[Pietro395]

Alternatively, use a subquery or CTE:

WITH T AS
(
SELECT ID, Postazione, Data, ora, CAST(Data as DATETIME) + CAST(ora as DATETIME) AS pippo
FROM dbo.View_Log 
)
SELECT * FROM T
WHERE pippo >= ?
AND pippo < ?;

mmh.. also the same error: Error! Unsupported type identifier for column ora @ UNKNOWN TYPE (precision 16, scale 7))

                WITH T AS
                (
                SELECT dbo.View_Log.ID, dbo.View_Log.Postazione, 
                    dbo.View_Log.Data, dbo.View_Log.ora, CAST(dbo.View_Log.Data as DATETIME) + CAST(dbo.View_Log.ora as DATETIME) AS pippo
                FROM dbo.View_Log 
                )
                SELECT * FROM T
                WHERE T.pippo >= ?

Column ora is TIME datatype in DB https://imgur.com/7GH6jRU

[keitherskine]

@xhochy Does turbodbc support TIME datatypes? Issue #85 implies is doesn't, but the docs state it does.

[Pietro395]

@xhochy Does turbodbc support TIME datatypes? Issue #85 implies is doesn't, but the docs state it does.

I was relying on the documentation and I was assuming it was supported

[xhochy]

@xhochy Does turbodbc support TIME datatypes? Issue #85 implies is doesn't, but the docs state it does.

Someone would need to write a test, I'm not using any TIME type, so it could be that this isn't working.

[Pietro395]

@xhochy Does turbodbc support TIME datatypes? Issue #85 implies is doesn't, but the docs state it does.

Someone would need to write a test, I'm not using any TIME type, so it could be that this isn't working.

Is it normal that I don't see the TIME data type here? https://github.com/blue-yonder/turbodbc/blob/master/python/turbodbc/data_types.py

[Pietro395]

Hello, there are any news for this problem?

[keitherskine]

At this point @pietromingo , I'm assuming turbodbc does not support TIME datatypes. Hence, you won't be able to retrieve the "ora" column directly. Having said that, in the scenario you describe, I don't think you need to retrieve it separately at all. You are retrieving it indirectly in the "pippo" column, and you can retrieve the time portion of "pippo" in your Python code. If you really need to retrieve "pippo" separately though, you can always convert it to a DATETIME first. Not ideal I know, but it should get you going.

[Pietro395]

At this point @pietromingo , I'm assuming turbodbc does not support TIME datatypes. Hence, you won't be able to retrieve the "ora" column directly. Having said that, in the scenario you describe, I don't think you need to retrieve it separately at all. You are retrieving it indirectly in the "pippo" column, and you can retrieve the time portion of "pippo" in your Python code. If you really need to retrieve "pippo" separately though, you can always convert it to a DATETIME first. Not ideal I know, but it should get you going.

Thanks, you're right, I was focusing on the query and I hadn't thought about this detail!