CODEWITHSUNDEEP
sqloracle-databasepivot
AM_86
AM_86

Reputation: 115

Getting missing comma error when using Pivot function on a table

I am creating a simple pivot but I get the ORA-00917: missing comma error on the line where the pivot function exists (third line). Please help.

Not sure what else to try as the syntax seems to be correct as I am new to the pivot function.

SELECT venue, notional
FROM ABC
pivot (sum(notional) FOR (venue) IN ('A' as A1 , 'B' as B1));

The expected result would be A1 and B1 showing their respective notionals.

Upvotes: 0

Views: 565

Answers (2)

MT0
MT0

Reputation: 168232

You do not need a sub-query. The columns venue and notional in the ABC table have been pivoted and no longer exist in the output result set so the SELECT clause will not find those columns.

Instead SELECT the a1 and b1 columns you have pivoted:

Test Data:

CREATE TABLE abc ( id, venue, notional ) AS
SELECT 1, 'A', 1 FROM DUAL UNION ALL
SELECT 1, 'A', 2 FROM DUAL UNION ALL
SELECT 1, 'A', 3 FROM DUAL UNION ALL
SELECT 1, 'B', 1 FROM DUAL UNION ALL
SELECT 1, 'B', 2 FROM DUAL UNION ALL
SELECT 2, 'B', 3 FROM DUAL;

Query:

SELECT id, a1, b1      -- you can't use venue or notional here as they've been pivoted.
FROM ABC
pivot (sum(notional) FOR (venue) IN ('A' as A1 , 'B' as B1));

Output:

ID |   A1 | B1
-: | ---: | -:
 1 |    6 |  3
 2 | null |  3

db<>fiddle here

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

It seems you miss the sub-query before PIVOT function - 

SELECT *
FROM (SELECT venue, notional
      FROM ABC
     )
PIVOT (
        SUM(notional) FOR (venue) IN ('A' as A1 , 'B' as B1)
      );

Upvotes: 0

Related Questions