Alias name

[FRBhote]

Facing a problem Sqlbase vs Sqlserver.

Sqlbase:

SELECT INVOICE_NO, INVOICE_NO FROM INVOICE ORDER BY INVOICE_NO ; works fine

SELECT INVOICE_NO AS A, INVOICE_NO AS B FROM INVOICE ORDER BY A ; 01401 DLU ICN Invalid column name A

Sqlerver:

SELECT S_ID_NO, S_ID_NO FROM S_ID ORDER BY S_ID_NO Ambiguous column name 'S_ID_NO'

SELECT S_ID_NO AS A, S_ID_NO AS B FROM S_ID ORDER BY A works fine

Would appreciate any help for both to be compatible.

[Igor Ivanovic]

AFAIK, the order clause accepts column names or numbers only, not an alias.
I think that SqlServer can also have column number in the order clause, so this should work:

Code: Select all

SQLBase
SELECT INVOICE_NO, INVOICE_NO FROM INVOICE ORDER BY 1
SqlServer
SELECT S_ID_NO AS A, S_ID_NO AS B FROM S_ID ORDER BY 1

[FRBhote]

Thanks! I had forgotten that.