Create Mat View compiles in SQL Window, but not Command?

[Stew Stryker]

This is a weird one, possibly off-topic for PSD, but I'm hoping one of the experts out there can help. I've created a fairly complex materialized view in Oracle 10g R2. I initially created it in a PSD SQL Window and it compiled just fine. But if I compile the same definition from a PSD Command Window, it gives an error. So I'm wondering how the Command Window works differently than the SQL Window.

Here's the error:

ORA-00900: invalid SQL statement

Here's the view definition, if anyone can see a problem (besides that it's flattening a bunch of values). If anyone worries about performance, don't, the table used is pretty small.

CREATE MATERIALIZED VIEW class_participation
  TABLESPACE ADV_SMALL_DATA
  NOLOGGING
  BUILD IMMEDIATE
  USING NO INDEX
  REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE)+1+2/24
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS SELECT class_yog,
           CASE WHEN base_cy > 0
                THEN al_donor_cy / base_cy * 100 ELSE 0 END as partic_cy,
           CASE WHEN base_cy > 0
                THEN al_donor_cash_cy / base_cy * 100 ELSE 0 END as partic_cash_cy,
           CASE WHEN base_ly > 0
                THEN al_donor_ly / base_ly * 100 ELSE 0 END as partic_ly,
           CASE WHEN base_ly > 0
                THEN al_donor_cash_ly / base_ly * 100 ELSE 0 END as partic_cash_ly,
           CASE WHEN base_2yr > 0
                THEN al_donor_2yr / base_2yr * 100 ELSE 0 END as partic_2yr,
           CASE WHEN base_2yr > 0
                THEN al_donor_cash_2yr / base_2yr * 100 ELSE 0 END as partic_cash2yr,
           CASE WHEN base_3yr > 0
                THEN al_donor_3yr / base_3yr * 100 ELSE 0 END as partic_3yr,
           CASE WHEN base_3yr > 0
                THEN al_donor_cash_3yr/ base_3yr * 100 ELSE 0 END as partic_cash_3yr,
           CASE WHEN base_4yr > 0
                THEN al_donor_4yr / base_4yr * 100 ELSE 0 END as partic_4yr,
           CASE WHEN base_4yr > 0
                THEN al_donor_cash_4yr / base_4yr * 100 ELSE 0 END as partic_cash_4yr,
           CASE WHEN base_5yr > 0
                THEN al_donor_5yr / base_5yr * 100 ELSE 0 END as partic_5yr,
           CASE WHEN base_5yr > 0
                THEN al_donor_cash_5yr / base_5yr * 100 ELSE 0 END as partic_cash_5yr
    FROM
        (SELECT class_yog,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() THEN class_credit ELSE 0 END) class_credit_cy,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() THEN class_cash ELSE 0 END) Class_cash_cy,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() THEN base ELSE 0 END) base_cy,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() THEN donor_cnt ELSE 0 END)  al_donor_cy,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() THEN donor_cash_cnt ELSE 0 END) al_donor_cash_cy,

                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 1 THEN class_credit ELSE 0 END) class_credit_ly,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 1 THEN class_cash ELSE 0 END) Class_cash_ly,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy()  -1 THEN base ELSE 0 END) base_Ly,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 1 THEN donor_cnt ELSE 0 END)  al_donor_ly,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 1 THEN donor_cash_cnt ELSE 0 END) al_donor_cash_ly,

                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 2 THEN class_credit ELSE 0 END) class_credit_2yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 2 THEN class_cash ELSE 0 END) Class_cash_2yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 2 THEN base ELSE 0 END) base_2yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 2 THEN donor_cnt ELSE 0 END)  al_donor_2yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 2 THEN donor_cash_cnt ELSE 0 END) al_donor_cash_2yr,

                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 3 THEN class_credit ELSE 0 END) class_credit_3yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 3 THEN class_cash ELSE 0 END) Class_cash_3yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 3 THEN base ELSE 0 END) base_3yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 3 THEN donor_cnt ELSE 0 END)  al_donor_3yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 3 THEN donor_cash_cnt ELSE 0 END) al_donor_cash_3yr,

                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 4 THEN class_credit ELSE 0 END) class_credit_4yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 4 THEN class_cash ELSE 0 END) Class_cash_4yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 4 THEN base ELSE 0 END) base_4yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 4 THEN donor_cnt ELSE 0 END)  al_donor_4yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 4 THEN donor_cash_cnt ELSE 0 END) al_donor_cash_4yr,

                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 5 THEN class_credit ELSE 0 END) class_credit_5yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 5 THEN class_cash ELSE 0 END) Class_cash_5yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 5 THEN base ELSE 0 END) base_5yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 5 THEN donor_cnt ELSE 0 END)  al_donor_5yr,
                SUM(CASE WHEN ccc.fiscal_yr=aeo.get_dcf_current_fy() - 5 THEN donor_cash_cnt ELSE 0 END) al_donor_cash_5yr
        FROM class_credit_by_class ccc
        GROUP BY class_yog);

Thanks,

Stew

 

[Stew Stryker]

Nevermind...

It turns out that it didn't like the blank links between the subquery column definitions that I'd added for visibility! As soon as I trimmed them out, it compiles fine!

Go figure!