CODEWITHSUNDEEP
sqloracle-database
mre
mre

Reputation: 137

How to make pivot query that returns no rows return null instead. (Oracle SQL)

I have a query that is structured like this:

SELECT
                        "result1"                   
                    ,   "result2" 
                    ,   "result3" 

FROM
(    
    SELECT number, position
    FROM values val
    FULL JOIN values2 val2 ON val.id = val2.id
    WHERE val.code = 'example'
    AND val.number IS NOT NULL
)
PIVOT
(
  MAX(number)
  FOR position IN(
    1  AS "result1",
    2  AS "result2",
    3  AS "result3"
  )
);

For the case of it returning no values, I want it to return null-values instead of a empty row.

How would I achieve this? I was not able to make commonly suggested solutions work, because of the pivot.

E:

The result I get:

No row returned:

enter image description here

The result I want:

One row returned with null values

enter image description here

Upvotes: 0

Views: 1375

Answers (2)

MT0
MT0

Reputation: 167981

The problem is not with the PIVOT it is with the sub-query before the PIVOT and your question can be reduced to:

How can I get the query (renaming your identifiers to have legal values):

SELECT num,
       position
FROM   values1 val
       FULL OUTER JOIN values2 val2
       ON val.id = val2.id
WHERE  val.code = 'example'
AND    val.num IS NOT NULL

to always return at least one row?

If you have the test data:

CREATE TABLE values1 ( id, code, num ) AS
SELECT 1, 'example',     NULL FROM DUAL UNION ALL
SELECT 2, 'not_example', 1    FROM DUAL;

CREATE TABLE values2 ( id, position ) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 3 FROM DUAL;

Then there is no row that will match the filter conditions in your WHERE clause and there will be zero rows to PIVOT so the query will always return zero rows. In this case then you can use UNION ALL to add a row:

SELECT num,
       position
FROM   values1 val
       FULL OUTER JOIN values2 val2
       ON val.id = val2.id
WHERE  val.code = 'example'
AND    val.num IS NOT NULL
UNION ALL
SELECT NULL,
       NULL
FROM   DUAL
WHERE  NOT EXISTS (
  SELECT 1
  FROM   value1
  WHERE  code = 'example'
  AND    num IS NOT NULL
)

Which will output:

 NUM | POSITION
---: | -------:
null |     null

And then wrapped with the pivot:

SELECT *
FROM
(    
  SELECT num,
         position
  FROM   values1 val
         FULL OUTER JOIN values2 val2
         ON val.id = val2.id
  WHERE  val.code = 'example'
  AND    val.num IS NOT NULL
  UNION ALL
  SELECT NULL,
         NULL
  FROM   DUAL
  WHERE  NOT EXISTS (
    SELECT 1
    FROM   values1
    WHERE  code = 'example'
    AND    num IS NOT NULL
  )
)
PIVOT ( MAX(num) FOR position IN (
  1  AS "result1",
  2  AS "result2",
  3  AS "result3"
));

Outputs:

result1 | result2 | result3
------: | ------: | ------:
   null |    null |    null

db<>fiddle here

Upvotes: 3

mre
mre

Reputation: 137

I solved my issue by creating a view out of this query and calling the view like this:

SELECT                   NVL(MIN(result1), null)                
                    ,    NVL(MIN(result2), null)    
                    ,    NVL(MIN(result3), null)    
FROM schema.view;

Upvotes: 0

Related Questions