-1

I have code to create a view, for one column (TERMINAL_DEGREE) I am trying to cast the value of a query which contains a WITH clause. Any help is greatly appreciated.

I get the following error:

[Error] Execution (306: 1): ORA-00936: missing expression

Here is my pseudo code (The query inside of the cast() for TERMINAL_DEGREE works perfectly fine outside of the create view statement):

            CREATE OR REPLACE FORCE VIEW CURRENT_FSYR
            ( .....
              TERMINAL_DEGREE,
              .......)
            BEQUEATH DEFINER
            AS
            WITH
                src
            AS
            SELECT AS_OF_DATE
                                      AS_OF_DATE,
                                  PERSON_SKEY
                                      PERSON_SKEY 
                                  ........
                             FROM REPORT_DETAILS
                            
                         GROUP BY AS_OF_DATE,
                                  PERSON_SKEY
                                   .........)
        SELECT (
         ....,                                          --OTHER COLUMNS                  
         CAST (WITH HIGHEST_DEG_VALUE AS                --THE COLUMN WITH THE ISSUE
                 (                
                   ....)
         SELECT CASE 
              .....
               END
          FROM HIGHEST_DEG_VALUE 
         AS VARCHAR2(20))
        TERMINAL_DEGREE,
        ......                                     --OTHER COLUMNS
    
FROM ...
    ;
Anderson
  • 117
  • 2
  • 14
  • 1
    Won't it work if you wrap the part starting with "WITH" and ending before "AS VARCHAR2(20)" in parentheses? At least the simple example seems to work on fiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d3297961f44b426c9ae6c48cf78332b5 – Dornaut Jul 30 '20 at 18:27
  • 1
    ORA-00936 is a syntax error. We can't spot it if you don't post the whole code. – APC Jul 30 '20 at 18:43

1 Answers1

1

Yes, you can:

CREATE OR REPLACE FORCE VIEW CURRENT_FSYR
( id, string_value )
BEQUEATH DEFINER
AS
WITH data AS (                            -- need a bracket here
  SELECT rownum AS id, dummy FROM DUAL
)
SELECT id,
       CAST(
         (
           WITH src AS (
             SELECT 1 AS value
             FROM   DUAL
           )
           SELECT CASE value
                  WHEN 1 THEN value || value
                  ELSE NULL
                  END
           FROM   src
         )
         AS VARCHAR2(20)
       ) AS string_value
FROM   data

(You are missing most of your code so I made up the missing bits.)

Then:

SELECT * FROM current_fsyr

Outputs:

ID | STRING_VALUE
-: | :-----------
 1 | 11

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117