CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
Neeraj
Neeraj

Reputation: 4489

How do I store Subquery result in variable and use in master query

I need to store subquery result in variable and use into master query to save execution time of query otherwise i have to write again same subquery for that. The code is given below :

select DISTINCT
convert(datetime,substring(o.TransactTime, 0,9), 112) + CONVERT(datetime, substring(o.transacttime,10,LEN(o.transacttime)), 114),
o.clordid,
ack.strategyid,
o.Account, 
o.AllocAccount, 
o.symbol,
o.price,
ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty) orderqty,

o.InClOrdID,
(SELECT  ISNULL(ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty)-sum(ex.LastShares),ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty)) from order_msgs_incoming<TblIndx> ex where
    o.Clordid = ex.clordid 
) RemainingQuantity
from order_msgs_incoming1 o 
/*picking ack rows to update strategyId since StrategyId is null for 43 but populated for 46*/
left outer join order_msgs_incoming1 ack on o.clordid = ack.clordid and ack.msg_id=46
where 
/*row for new order only since that contains all columns values*/
o.msg_id = 43

I need like this 

select DISTINCT
variableValue=ISNULL((Select Top 1 L.orderqty from order_msgs_incoming1 L where o.clordid=L.clordid and L.msg_id in (17,18,19,22,59,44) order BY L.transacttime DESC),o.orderqty) orderqty,

o.InClOrdID,
(SELECT  ISNULL(variableValue-sum(ex.LastShares),variableValue) from order_msgs_incoming<TblIndx> ex where
    o.Clordid = ex.clordid 
) RemainingQuantity
from order_msgs_incoming1 o

Please help me .

Upvotes: 2

Views: 16101

Answers (1)

Vaibhav
Vaibhav

Reputation: 7008

You can declare a variable and assign it a value using SELECT clause. Then use the variable in your master query. Please find the code below:

DECLARE @variableValue bit;

SELECT @variableValue = ISNULL(
                        (
                        SELECT TOP 1 L.orderqty 
                        FROM order_msgs_incoming1 L 
                        WHERE o.clordid=L.clordid
                        AND L.msg_id in (17,18,19,22,59,44) 
                        ORDER BY L.transacttime DESC
                        )
                        ,o.orderqty
                    )
FROM order_msgs_incoming1 o

SELECT DISTINCT
@variableValue AS orderqty,
o.InClOrdID,
(
    SELECT  ISNULL(variableValue-sum(ex.LastShares)
    ,variableValue) 
    FROM order_msgs_incoming<TblIndx> ex 
    WHERE o.Clordid = ex.clordid 
) RemainingQuantity
FROM order_msgs_incoming1 o

You can also use the following code in order to change the subquery so that you can use it in a variable:

SELECT TOP 1 L.orderqty 
FROM order_msgs_incoming1 L 
INNER JOIN order_msgs_incoming1 o 
    ON o.clordid=L.clordid
    AND L.msg_id in (17,18,19,22,59,44) 
ORDER BY L.transacttime DESC

Upvotes: 1

Related Questions