LOCAL VARIABLES

Question

How do I assign the selected UNIT_PRICE AS OLD_UNITPRICE to the local variable?

DECLARE @OLD_UNITPRICE decimal;


    SELECT TOP 1 Q.ID AS QUOTE_ID 
          ,Q.QUOTE_DATE AS QUOTE_DATE 
          ,QL.LINE_NO AS QUOTE_LINE_NO 
          ,QP.QTY AS QUOTE_QTY 
          ,QP.UNIT_PRICE AS OLD_UNITPRICE 
    FROM    TIC.dbo.QUOTE_LINE QL 
    LEFT JOIN IC.dbo.QUOTE_PRICE QP 
           ON QL.QUOTE_ID = QP.QUOTE_ID 
         AND QL.LINE_NO = QP.QUOTE_LINE_NO 
    LEFT JOIN TIC.dbo.QUOTE Q 
           ON QL.QUOTE_ID = Q.ID 
    WHERE QL.PART_ID = @ticPartID 
      AND LEFT(QL.QUOTE_ID,1) = 'E'  
    ORDER BY Q.QUOTE_DATE DESC

Answer 1

0

Try encapsulating just the result that you want when assigning, like the following

DECLARE @OLD_UNITPRICE decimal;


SELECT @OLD_UNITPRICE =   (
 SELECT TOP 1 QP.UNIT_PRICE 
 FROM    TIC.dbo.QUOTE_LINE QL 
 LEFT JOIN IC.dbo.QUOTE_PRICE QP 
    ON QL.QUOTE_ID = QP.QUOTE_ID 
 AND QL.LINE_NO = QP.QUOTE_LINE_NO 
 LEFT JOIN TIC.dbo.QUOTE Q 
    ON QL.QUOTE_ID = Q.ID 
 WHERE QL.PART_ID = @ticPartID 
   AND LEFT(QL.QUOTE_ID,1) = 'E'  
 ORDER BY Q.QUOTE_DATE DESC 
 )

answered Aug 14 '12 at 03:37 AM

SirSQL
4.8k ● 1 ● 3

Thank you for responding SirSQL.

I was hoping to multiply @OLD_UNITPRICE * @QTY

@QTY is a variable that is part of a cursor.

Is this posible with encapsulation?

Aug 14 '12 at 05:45 AM technette

Just add a second statement, SET @OLD_UNITPRICE = @OLD_UNITPRICE * @QTY;

Aug 14 '12 at 10:34 AM Grant Fritchey ♦♦

(comments are locked)

LOCAL VARIABLES

Follow this question