Obtain a single data from two Oracle queries

Question

Obtain a single data from two Oracle queries

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I need to make a query, in which if a subquery returns a value null execute a second subquery which guarantees me to return a value the two subqueries return the same value.

FIRST_VALUE (pac1.pac_name)
--------------------------
|Oct/2012 - Feb/2013     |
--------------------------

the first query would be if

 SELECT DISTINCT
       FIRST_VALUE (pac1.pac_name)
          OVER (ORDER BY pac1.pac_final_date DESC)
  FROM    matricula mac
       INNER JOIN
          periodo pac1
       ON mac.pac_id = pac1.pac_id
 WHERE mac.ent_id = 26172 AND mac.mac_estado IN (8072, 10221)

and the second one

SELECT DISTINCT
          FIRST_VALUE (pac1.pac_name)
             OVER (ORDER BY pac1.pac_final_date DESC)
     FROM  registro rea
          INNER JOIN
             periodo pac1
          ON rea.pac_id = pac1.pac_id
    WHERE rea.ent_id = 26172

There are two cases:

Case -1 - > when I run the first subquery it returns a value and it will not be necessary to execute the second subquery

Result

FIRST_VALUE (pac1.pac_name)
--------------------------
|Oct/2012 - Feb/2013     |
--------------------------

Case -2 - > when you run the first query and the result value is null then you must run the second subquery

Result

FIRST_VALUE (pac1.pac_name)
--------------------------
|Oct/2012 - Feb/2013     |
--------------------------

sql oracle query

asked by Byron 27.09.2017 в 22:36

source

2 answers

Save data from a .txt [closed] How to check the content of readValue

score 0 · Answer 1

The easiest thing to do would be to use Oracle's DECODE function. The syntax of the function is:

DECODE(expr1,val1,ret1,val2,ret2,...,valn,retn,default)

What the function does is evaluate the expression expr1 . If the value matches with val1 , it returns ret1 , if it matches val2 it returns ret2 , and so on. If the value does not correspond to any of the valX , it returns a value default .

In your case, the sentence you are looking for would be something like this:

SELECT decode(DISTINCT
   FIRST_VALUE (pac1.pac_name)
      OVER (ORDER BY pac1.pac_final_date DESC),null,
         (SELECT DISTINCT
            FIRST_VALUE (pac1.pac_name)
            OVER (ORDER BY pac1.pac_final_date DESC)
               FROM  registro rea
               INNER JOIN
               periodo pac1
               ON rea.pac_id = pac1.pac_id
            WHERE rea.ent_id = 26172)
   FROM    matricula mac
   INNER JOIN
      periodo pac1
   ON mac.pac_id = pac1.pac_id
   WHERE mac.ent_id = 26172 AND mac.mac_estado IN (8072, 10221)

score 0 · Answer 2

You could also use the WITH clause and then it would look something like this:

WITH C1 AS 
(
 SELECT DISTINCT
       FIRST_VALUE (pac1.pac_name) 
          OVER (ORDER BY pac1.pac_final_date DESC) PACNAME
  FROM    matricula mac
       INNER JOIN
          periodo pac1
       ON mac.pac_id = pac1.pac_id
 WHERE mac.ent_id = 26172 AND mac.mac_estado IN (8072, 10221) 
),
C2 AS 
(
  SELECT DISTINCT
          FIRST_VALUE (pac1.pac_name) 
             OVER (ORDER BY pac1.pac_final_date DESC) PACNAME
     FROM  registro rea
          INNER JOIN
             periodo pac1
          ON rea.pac_id = pac1.pac_id
    WHERE rea.ent_id = 26172 
),
C3 AS 
(
SELECT (SELECT * FROM C1) C1PACNAME, (SELECT * FROM C2) C2PACNAME FROM DUAL 
)
SELECT NVL(C1PACNAME,C2PACNAME) PACNAME FROM C3 ;

This I have not been able to prove well because I do not have your tables, but you can vary a bit as it suits you.