"AS" aggregaton error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"AS" aggregaton error

Author

Topic

titan_ae78
Starting Member

9 Posts

Posted - 2009-08-03 : 11:34:55

Hi,
I've this query that works fine in Access.. I want to convert it to SQL SERVER but I've many errors:

SELECT Top 1 prezzounitario+((prezzounitario*scontomaggiorazione1)/100) AS tot1,
tot1+((tot1*scontomaggiorazione2)/100) AS tot2,
tot2+((tot2*scontomaggiorazione3)/100) as PrezzoUnitarioScontatoMaggiorato,
DataDocumento
FROM Documenti_Righe
WHERE IDInfo_articoli=9 AND left(IDInfo_documenti,4) in ('0010','0011','0012')
UNION ALL
SELECT Top 1 prezzounitario+((prezzounitario*scontomaggiorazione1)/100) AS tot1,
tot1+((tot1*scontomaggiorazione2)/100) AS tot2,
tot2+((tot2*scontomaggiorazione3)/100) as PrezzoUnitarioScontatoMaggiorato,
DataDocumento
FROM Documenti_Righe_Movimenti_Magazzino
WHERE IDInfo_articoli=9 AND left(IDInfo_documenti,4) in ('0010','0011','0012') Order by DataDocumento Desc

I think I can't use tot1 and tot2 for sql operation.

Thanks in advance...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-03 : 11:43:33

Yes - correct. You can't refer to a column alias in the same query/statement that defines the alias. You would need to either repeat the expression or define them in a derived table (sub-query). It would make it much easier for us to read if you formatted the code

Be One with the Optimizer
TG

titan_ae78
Starting Member

9 Posts

Posted - 2009-08-03 : 12:29:32

can you show me how can I do this with derived table?
Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-03 : 13:30:57

Here is a simple example of a derived table:


select myDerivedTable.myExpression + 1 as [myColumn]
from   (
       select (1+1+1+1)/3.0 as myExpression
       ) as myDerivedTable

since you have tot2 relying on tot1 and another column relying on tot2 then it may be better for you to simply repeat these epressions rather than creating multiple levels of derived tables.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 02:30:40

<<
You can't refer to a column alias in the same query/statement that defines the alias
>>

Except it is referred in the Order by Clause

Madhivanan

Failing to plan is Planning to fail

titan_ae78
Starting Member

9 Posts

Posted - 2009-08-04 : 06:28:56

Thanks for your reply. I'm not sure to understand..

SELECT prezzounitario+((prezzounitario*scontomaggiorazione1)/100) AS tot1,
tot1+((tot1*scontomaggiorazione2)/100) AS tot2
FROM Documenti_Righe

With this part of query, how can I use derivated table?

I've try to do this but I've some errors:
SELECT (SELECT prezzounitario+((prezzounitario*scontomaggiorazione1)/100) as tot1 FROM Documenti_Righe) as derivedtable1
derivedtable1.tot1+((derivedtable1.tot1*scontomaggiorazione2)/100) AS tot2,
FROM Documenti_Righe

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 07:12:01

select tot1,tot1+((tot1*scontomaggiorazione2)/100) AS tot2
from
(
SELECT prezzounitario+((prezzounitario*scontomaggiorazione1)/100) AS tot1,
FROM Documenti_Righe
) as t

Madhivanan

Failing to plan is Planning to fail

titan_ae78
Starting Member

9 Posts

Posted - 2009-08-04 : 11:30:03

Thank you madhivanan.

I try to understand hot it works but when I add new expression from tot2 I've problems:

select top 1 tot1,tot1+((tot1*scontomaggiorazione2)/100) AS tot2,
tot2+((tot2*scontomaggiorazione3)/100) as PrezzoUnitarioScontatoMaggiorato
from
(SELECT prezzounitario+((prezzounitario*scontomaggiorazione1)/100) AS tot1, scontomaggiorazione2,scontomaggiorazione3 FROM Documenti_Righe) as t

Should I create new subquery for tot2?

Have you got some link to guide reference so I can study it and do not submit new post..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-04 : 13:04:43

Please re-read my 2nd post about simply repeating the expression rather than creating multi-level derived tables.


select tot2 + 25 as col1
from   (
       select tot1,tot1+((tot1*scontomaggiorazione2)/100) AS tot2
       from
              (
              SELECT prezzounitario+((prezzounitario*scontomaggiorazione1)/100) AS tot1,
              FROM Documenti_Righe
              ) as t
       ) as t2

Be One with the Optimizer
TG

titan_ae78
Starting Member

9 Posts

Posted - 2009-08-05 : 04:10:06

Thank you very much.. now it get across.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources