CODEWITHSUNDEEP
sqloracle-database
kumarb
kumarb

Reputation: 535

Use of Virtual column in SELECT query Oracle DB

I would like to understand how we can use virtual variable created in select query. Like in below example I have created RequestDate and ReceivedDate as part of select query which I would like to use in third select query in same scope.. Is it possible to do so ..? pls advise. 

SELECT MessageId,
(  SELECT 
    xxxx from xxx where xxx
 ) AS RequestDate ,


( SELECT 
xxxx from xxx where xxx
) AS ReceivedDate ,

(
SELECT 
ReceivedDate - RequestDate from xxxx
) as DateDiff
FROM Date

Upvotes: 0

Views: 740

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT MessageId,RequestDate ,ReceivedDate ,
       ReceivedDate - RequestDate as DateDiff
FROM (
       SELECT MessageId,
              (SELECT  xxxx from xxx where xxx) AS RequestDate ,
              (SELECT  xxxx from xxx where xxx) AS ReceivedDate ,
        FROM Date 
     ) As Z

NOTE : Your inner queries (SELECT xxxx from xxx where xxx) and (SELECT xxxx from xxx where xxx) should necessarily return single value for each row returned from DATE table otherwise you will get error

Oracle update: ORA-01427: single-row subquery returns more than one row

Upvotes: 1

Related Questions