CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
Taladan
Taladan

Reputation: 479

Translate sybase select to mssql select

i need to translate some sql select from sybase to mssql dialect. Problems cause me simple queries with aliases. a simple example

select amount * 2 as doubleamount, doubleamount / 4 as half amount from event

You can see that the alias names are still used. With such simple ones as here, you can write amount * 2 several times. With complex queries you can't do that anymore, especially not if procedures etc. I have selects with dozens of alias-colums that are expected to continue. Can I somehow access the already existing result and keep reckoning?

Upvotes: 1

Views: 68

Answers (2)

markp-fuso
markp-fuso

Reputation: 34184

Have you tried using a derived table?

Setup:

drop table if exists event;
create table event(amount money);
insert into event (amount) values (4);

If you only need one level of re-use then a single derived table should suffice, eg:

select doubleamount,
       doubleamount / 4 as [half amount]
from
        (select amount * 2 as doubleamount
         from   event ) dt;

doubleamount | half amount
------------ | -----------
8.0000       | 2.0000

Here's a fiddle of the above.

If you need to reference doubleamount then you could turn the above into it's own derived table (ie, use nested derived tables).

Upvotes: 1

S3S
S3S

Reputation: 25112

You can't use an alias in a follow on column. Instead, you need to use the actual function. Also, column names must be enclosed in [brackets] if they contain spaces, or are a reserved work in SQL Server.

select 
   amount * 2 as doubleamount, 
   (amount * 2) / 4 as [half amount]
from event

Here is a more unified method of aliasing that many find better. I changed the space to an underscore also.

select 
   doubleamount = amount * 2, 
   half_amount = (amount * 2) / 4
from event

Upvotes: 0

Related Questions