Solved

Refer SQL column alias for calculation in other column

Posted on 2008-10-24
11
1,481 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can't connect to new installation of SQL Server 2016 6 31
Add '#' to end of file 2 30
SQL Field Length for Email Address 3 17
MS SQL Pivot table help 4 14
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

21 Experts available now in Live!

Get 1:1 Help Now