How to use fields alias name in sql query?

[zubairkhan]

Hi friends,

I wrote following query where i summed a field billed amount as amount, when i am trying to use this alias in other calculation, query returns error " Invalid column name amount". How i can re-use field alias name in sql query.

.Open "select b.trdate,e.jobno,e.jobdesc,p.partyname,b.manualbillid as BillNo , " & _
            "sum(b.billedqty) as Quantity,b.billedrate as Rate,round(sum(b.billedamount),0) as Amount, " & _
            "round(amount*15/100,'0') as GSt,round(sum(b.billedamount*15/100),0) + round(sum(b.billedamount),0) as TotValue " & _
            "FROM enquiry e,parties p, bills b " & _
            " " & FilterText & " " & _
            "GROUP BY b.trdate,e.jobno,e.jobdesc,p.partyname,b.manualbillid, " & _
            "b.billedrate order by p.partyname,b.manualbillid,b.trdate "

second, as u see in my query that i added a lot fields in group, because i have in need individual information of each job and simultaneously ccumulated information of each job, no doubt i got the desired result but i am not satisfy, my suspect is, this junk of fields in group my slow down the query result, please check out this query and suggest what should be the possible smart query for same desired result

[simonm]

You can't use the alias in another calculation.

[szlamany]

As simonm said you cannot use the "alias" in another equation in another column...

Simply put the same "SUM" in both columns - the query should not take a negative hit because of that - it should realize while parsing the query that it's the same "sub-equation".

What database?

[zubairkhan]

Well, it is working in access why not in sql server 2000

[si_the_geek]

Even tho they are both database systems by the same company, they are very different products, and have several SQL syntax differences (such as: joins, wildcards, temp tables, ...).

[zubairkhan]

Does it not a weakness of sql server 2000, once we got a complicated calculation resulte in a coloumn, which might be 2 or three row in length does it not better to reuse this coloumn again where other calcuation may base upon its result. Besides this we have to apply same formula for each time.

[biswajitdas]

Hi the alias as name you mantion in the recordset.
means you fatche the alias name through the qury .
but you can not generate the alias name in other query , you have to store the value in a temp table or you have to make it a cursor


a alias name local to that query or thread

[zubairkhan]

i want to re-use alias name in same query.

[szlamany]

As was already said, you cannot do this.

SQL offers STORED PROCEDURES and TABLE VARIABLES and other ways to accomplish this.

[kaffenils]

Does it not a weakness of sql server 2000, once we got a complicated calculation resulte in a coloumn, which might be 2 or three row in length does it not better to reuse this coloumn again where other calcuation may base upon its result. Besides this we have to apply same formula for each time.

SQL Server has UDF (User Defined Function) that will help you with this problem. UDFs will also make it easier to read queries with complex calculations since you don't see the calculation in the final query, therefore making it easier to get a good overview. If you want to see how a specific calculation is executed you view the UDF.

[szlamany]

Kaffenils - I agree - UDF's make for taking complex query equations and puts them in a layer below - very appropriate.

Just keep in mind that doing table I/O in a UDF is usually recommended against - as the query optimizer cannot see that I/O for determining execution plan.

[kaffenils]

Just keep in mind that doing table I/O in a UDF is usually recommended against - as the query optimizer cannot see that I/O for determining execution plan.

Good point!