'ORA-00936: missing expression - SQL pivot

Getting the above error in the IN statement. Can you anyone help?

ORA-00936: missing expression
00936. 00000 - "missing expression
" *Cause:
*Action:
Error at Line: 32 Column: 

SELECT
    *
FROM
    (
        SELECT
            cus_id
          , month_end_date
          , product_group
        FROM
            table_name
    ) PIVOT (
        MAX ( product_group )
        FOR month_end_date
        IN (
            SELECT
                add_months(last_day(trunc(sysdate)), - level)
            FROM
                dual
            CONNECT BY
                level <= months_between(last_day(trunc(sysdate)), add_months(last_day(trunc(sysdate)), - 6))
        )
    )
sqloraclepivot


Solution 1:[1]

From the documentation:

subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the pivot_in_clause, the subquery produces a single XML string column.

In Oracle SQL you have to specify exact column names in pivot. This is because parser uses them at semantic checks and there no any way to specify dynamic column name in plain SQL. Dynamic pivot in Oracle is quite hard to implement because of the same limitations: even table valued function should have predefined structure of its output. You may check this answer with example of dynamic pivot, but the trick here is the ability of SQL*Plus to print a result of the cursor.

Solution 2:[2]

select 
AR_CREDIT,
AR_DEBIT,
AR_NUMBER,
BILL_GENERATION_DATE,
BILL_NO,
CASHIER_ID,
SUBSTR(CHEQUE_NUMBER,1,3) RVC, 
SUBSTR(CHEQUE_NUMBER,4,4) CHECK_NO, 
NVL(CAST(COVERS as NUMBER),0) COVERS,
DEP_CREDIT,
DEP_DEBIT,
FOLIO_TYPE,
GUEST_COUNTRY_CODE,
GUEST_COUNTRY_NAME,
GUEST_CREDIT,
GUEST_DEBIT,
GUEST_NAME,
GUEST_NAME_ID,
MUN_VAT,
NET,
PAYEE_NAME,
PAYEE_NAME_ID,
PAYEE_NAME_TYPE,
PT,
RESORT,
RESV_NAME_ID,
ROOM,

--TAXELEMMINIC,
--TAXELEMVAT,
TRANS_CODE,
trim(TRX_CODE) TRX_CODE,
TRX_DATE,
rtrim(TRX_DESCR) as TRX_DESCR,
TRX_GROUP,
TRX_SUBGROUP,
TRX_NO,
VAT
from EUROTEL_REPORT_fin_trans
where trx_date between :from_date and :to_date

and
   from EUROTEL_REPORT_RES_WBLKNM
WHERE business_date BETWEEN :datefrom AND :DATEto
--WHERE business_date BETWEEN to_date('01/05/2013','dd/mm/yyyy') AND to_date('31/12/2014','dd/mm/yyyy')

group by  
        RESORT, 
        R_TYPE, 
        ROOM, 
     ---   RESV_NAME_ID, 
       -- GUEST_NAME_ID, 
     ---   GUEST_FIRST_NAME, 
    ----    CUSTOM_REFERENCE, 
        MEAL_PLAN, 
        ARRIVAL_DATE, 
   --     DEPARTURE_DATE, 
        business_date,
        TO_CHAR(BUSINESS_DATE,'yyyy')  ,
        TO_CHAR(BUSINESS_DATE,'MM'),
     -----   TO_CHAR(BUSINESS_DATE,'WW')   ,
        --RT_FACTOR, 
        ROOM_TYPE, 
        ROOM_TYPE_RTC, 
        ROOM_CLASS, 
        MARKET_CODE, 
        SOURCE_CODE, 
        RATE_CODE, 

    -----         GROUP_ID, 
     ---   TRAVEL_AGENT_ID, 
    ---    COMPANY_ID, 
     ---   SOURCE_ID, 
            BOOKED_DATE, 
        --CONFIRMATION_NO, 
        PSUEDO_ROOM_YN, 
        CANCELLATION_DATE, 
        RESV_STATUS, 
        DEDUCT, 
        BLOCK_DEDUCT, 
        
     ---   ALLOTMENT_HEADER_ID, 
        ALLOTMENT_NAME,
        --COUNTRY, 
        --NATIONALITY, 
        COUNTRY_NAME, 
        NATIONALITY_NAME,
        COMPANY,
        TRAVEL_AGENT,
        SOURCE,
ORA-00936: missing expression

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 astentx
Solution 2 eNca

Related Questions