T-SQL "select 1+1 as two, two+1 as three" ?

Question

Post reply

T-SQL "select 1+1 as two, two+1 as three" ?

pcross

Grasshopper

Points: 14
More actions
November 19, 2003 at 3:59 pm

#82857

Is there a way I can get
select 1+1 as two, two+1 as three
without having a sub query like
select two, two+1 as three from (select 1+1 as two) as x
I've used the top method in hundreds of queries in Access which are making their way to SQL, so any help would be much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

as far as I know, I don't think you can use column aliasing within an expression.

how about:

select (1+1) as two, ((1+1)+1) as three

HTH

Billy

pcross Grasshopper Points: 14 More actions · Answer 2

Thanks Billy, it helps to have a name for the problem "column aliasing within an expression"!

Sometimes repeating the (1+1) formula is possible, but it quickly gets unweildy.

I was hoping for something like "sure, you can do that, just ...", but confirming that it _can't_ be done, and using sub-queries _is_ the way to go, is also valuable.

Cheers,

Peter

jmadsen SSC Veteran Points: 285 More actions · Answer 3

Not completely clear from your example if this helps, but for what it's worth:

*********************

declare @one int

declare @two int

declare @three int

SELECT @one = 1 , @two = @one + @one, @three = @two + @one

SELECT @one as 'One', @two as 'Two', @three as 'Three'

*********************

Something like that?

Jeff

pcross Grasshopper Points: 14 More actions · Answer 4

Thanks for that Jeff.

You're right as far as the example goes, but the problem comes of course when the '1' changes to a field as in :

select qty+1 as two, two+1 as three from sales.

or adding a GROUP BY etc.:

select payterms, sum(qty+1) as two, two+1 as three FROM sales GROUP BY payterms

I think the only way to use variables here takes you into using cursors, so you may as well use sub-queries?

--Peter

jmadsen SSC Veteran Points: 285 More actions · Answer 5

I thought that was too simple...

I might try building this dynamically then, as if it were a program in C/VB/Java....

Example of what I mean:

declare @one varchar(255)

declare @two varchar(255)

select @one = 'SEQ + 1'

select @two = '(' + @one + ') + 2'

EXEC('select SEQ, ' + @one + ' as One, ' + @two + ' as Two FROM resultsTable' )

This takes the pain out of retyping everything, makes it easy to modify, etc. but still give a straight query instead of cursors or subqueries.

Is that better?

Jeff

pcross Grasshopper Points: 14 More actions · Answer 6

By George, Jeff, I think that may be it!

Billy's answer was right - you can't use aliased columns elsewhere in a SQL expression. You have to type out the formula each time.

Jeff then provided a way to do that without the typing and maintenance of several identical formulas by building the SQL expression with code. The formula for each computed/aliased column is stored in its own string variable.

Like crosstab queries, you can't do this in an Access project's QBE grid or the Enterprise Manager's visual tools, but you can do it.

Thanks to you both,

Peter

---------------------

use pubs

declare @one varchar(255)

declare @two varchar(255)

select @one = 'sum(qty + 1)'

select @two = '(' + @one + ') + 2'

EXEC('select payterms, sum(qty) as Qty, ' + @one + ' as One, ' + @two + ' as Two FROM sales GROUP BY payterms HAVING ' + @one + ' > 138')

--------------------------------