alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Will the next sql version allow referencing an alias in the same select level ?