Solved

Refer SQL column alias for calculation in other column

Posted on 2008-10-24
11
1,507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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