how to reuse a calculated field in query? 1

[Pampers]

Hi everyone,
I'm trying to convert a mdb-query to adp-query (transSQL). Instead of IIF I now use CASE When. But it doesn't seem possible to reuse an alias. I created PrepaidAmount, which is calculated in the first line. Now I want to reuse this calculated field to do a second calculation, but this produces an ADO error message: 'Invalid column name PrepaidAmountANG'. How do I 'reuse' a calculated field in this query?

SELECT     CASE WHEN [Collect/Prepaid] = 1 THEN [rate] * [qtyPer] END AS PrepaidAmount, 
                      CASE WHEN [Collect/Prepaid] = 0 THEN [rate] * [qtyPer] END AS CollectAmount, 
                      CASE WHEN [currency] = 'USD' THEN PrepaidAmount * 1.82 END AS PrepaidANG
FROM         dbo.tblChargeBolImp

Pampers
Just let it go...

 

[vongrunt]

That kind of reuse is not possible.

Alternatives are:

1. Computed column in a table. Makes sense if PrepaidAmount is frequently reused, otherwise not.

2. View. Ditto.

3. Derived table/CTE. For example:

select PrepaidAmount, CollectAmount,
	 CASE WHEN [currency] = 'USD' THEN PrepaidAmount * 1.82 END AS PrepaidANG
FROM
(	SELECT CASE WHEN [Collect/Prepaid] = 1 THEN [rate] * [qtyPer] END AS PrepaidAmount, 
		CASE WHEN [Collect/Prepaid] = 0 THEN [rate] * [qtyPer] END AS CollectAmount,
		currency
	FROM         dbo.tblChargeBolImp
) anyderivedtablealiasyoulike

4. SELECT only "ingredients" - atomic columns (Collect, Prepaid, rate, qtyPer, currency) and do calculations client-side

5. copy & paste .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Pampers]

Tnx Vongrunt, got it.
So is it fare to say that in this case JET-SQL is more flexible then TRANS-SQL??


Pampers
Just let it go...

 

[vongrunt]

> So is it fare to say that in this case JET-SQL is more flexible then TRANS-SQL??

Yup, Access allows that while taking care about eventual circular references (a la a=b+c, b=a*2). Column alias reuse in GROUP BY clause is different story - both Jet and T-SQL won't let you do that... if I remember correctly.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Pampers]

allright, tnx for the info

Pampers
Just let it go...