Help with creating view in SQL Developer

[CrystalizeCanada]

Hi there,

I have created a Pivot query in Oracle 11g SQL developer and it runs great.

I now want to save as a view (I have rights to do this) and when I get an error I check for erros with the same SQL (I'm just copying and pasting).
I'm just copying it and pasting it so I'm puzzled. Am I not allowed to create a view with this syntax?


WITH pivot_data AS (
SELECT ITEM_CODE, URULE_CODE, RULEIT_TEXT
FROM Lego.RULEIT
WHERE URULE_CODE LIKE 'IInk%'
)
SELECT * FROM pivot_data
PIVOT (
MAX(RULEIT_TEXT)
FOR URULE_CODE
IN (
'IInk10Com',
'IInk10CoPl'
))

any help would be greatly appreciated.

Thanks
Gord

Gordon BOCP
Crystalize

 

[SantaMufasa]

Gord,

I believe you will have success if you simply place CREATE VIEW WHATEVER AS... in front of your code:

CREATE VIEW WHATEVER AS
WITH pivot_data AS (
 SELECT ITEM_CODE, URULE_CODE, RULEIT_TEXT
 FROM Lego.RULEIT
 WHERE URULE_CODE LIKE 'IInk%'
)
SELECT * FROM pivot_data
PIVOT (
MAX(RULEIT_TEXT)
FOR URULE_CODE
IN (
'IInk10Com', 
'IInk10CoPl'
))

Here is a proof of concept that I just tried:

SQL> create view v as with x as (select * from s_emp) select * from x;

View created.

Let us know your findings,.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalizeCanada]

Worked great
Tx very much

Gordon BOCP
Crystalize