Solved

Refer SQL column alias for calculation in other column

Posted on 2008-10-24
11
1,477 Views
Last Modified: 2011-10-19
Please refer my following query. In Line 3, I want to use:

B.Quantity * Item_MRP

Please note that Item_MRP is an alias name for a query in Line 2. How to use this way?

I also want to know how I can improve the performance of the following query.
SELECT C.CATEGORYNAME, A.ITEMNAME, B.QUANTITY,

        (SELECT MRP FROM STOCKENTRY WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID)) AS ITEM_MRP,

        B.QUANTITY * (SELECT MRP FROM STOCKENTRY 

        WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID))  AS VALUE

        FROM ITEMMASTER A, STOCKENTRY B, CATEGORYMASTER C

        WHERE A.ITEMID=B.ITEMID AND 

        C.CATEGORYID = (SELECT CATEGORYID FROM ITEMMASTER WHERE ITEMID = A.ITEMID) AND

        B.INVOICEDATE BETWEEN @DATE1 AND @DATE2

        GROUP BY C.CATEGORYNAME, B.INVOICEDATE, A.ITEMNAME

Open in new window

0
Comment
Question by:rpkhare
  • 5
  • 5
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22798805
You can't do that.  You could put the whole thing into a derived table like this.



select *,quantity*item_mrp from (
SELECT C.CATEGORYNAME, A.ITEMNAME, B.QUANTITY,
        (SELECT MRP FROM STOCKENTRY WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID)) AS ITEM_MRP,
        B.QUANTITY * (SELECT MRP FROM STOCKENTRY
        WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID))  AS VALUE
        FROM ITEMMASTER A, STOCKENTRY B, CATEGORYMASTER C
        WHERE A.ITEMID=B.ITEMID AND
        C.CATEGORYID = (SELECT CATEGORYID FROM ITEMMASTER WHERE ITEMID = A.ITEMID) AND
        B.INVOICEDATE BETWEEN @DATE1 AND @DATE2
        GROUP BY C.CATEGORYNAME, B.INVOICEDATE, A.ITEMNAME)a

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22804803
As Brandon has indicated cannot do it in T-SQL.  But perhaps you meant to only post in the MySQL zone and not in the MS SQL Server zone.  It may be possible in MySQL.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22807828
Is this mysql or mssql.  I have to argue that the solution is not only available but provided in http:#22798805.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22807879
MySQL
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22808235
The same thing should work in mysql.  Have you tried what I posted?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 8

Author Comment

by:rpkhare
ID: 22810222
Brandon,

thanks for the concern. Attached is the image of the output columns. But I want in this way:

Category_Name, Item_Name, Quantity, Item_MRP, Value (Qty * Item_MRP)

Your query is returning Value and (Quantity * MRP), two redundant columns.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22810274
Attached image
Stock-Entry-Result.JPG
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22811820
That was merely an example of how to reference the computed column by name.  How am I to know what columns you want to display?  If you want less columns, change it from select * to the columns you want.
select Category_Name, Item_Name, Quantity, Item_MRP,Qty * Item_MRP as Value
 
 

from (

SELECT C.CATEGORYNAME, A.ITEMNAME, B.QUANTITY,

        (SELECT MRP FROM STOCKENTRY WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID)) AS ITEM_MRP,

        B.QUANTITY * (SELECT MRP FROM STOCKENTRY 

        WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID))  AS VALUE

        FROM ITEMMASTER A, STOCKENTRY B, CATEGORYMASTER C

        WHERE A.ITEMID=B.ITEMID AND 

        C.CATEGORYID = (SELECT CATEGORYID FROM ITEMMASTER WHERE ITEMID = A.ITEMID) AND

        B.INVOICEDATE BETWEEN @DATE1 AND @DATE2

        GROUP BY C.CATEGORYNAME, B.INVOICEDATE, A.ITEMNAME)a

Open in new window

0
 
LVL 8

Author Comment

by:rpkhare
ID: 22812523
Thanks. One more thing I want to know is how to optimize the above query for performance.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22812553
That would require knowledge of indexes (and suggestions of) and restructuring the query.  I would keep this on topic for the PAQ which is about referencing a column by it's alias and open a new question for optimization.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22812559
Ok. Thanks. I am rating and closing this question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now