Home » SQL & PL/SQL » SQL & PL/SQL » CASE WHEN problems (9i)
CASE WHEN problems [message #304222] Tue, 04 March 2008 10:18 Go to next message
digigeek
Messages: 6
Registered: February 2008
Junior Member
I have a query that was inspired by Barbara Boehmer (thanks Barbara!) that compiled earlier this morning and then stopped compiling after adding a seemingly innocent "maxrecs" parameter.

I'm getting errors in the CASE WHEN blocks, but I'm sure this worked this morning and compiled fine....I have a simpler example that works just fine. I can't post the whole procedure, but here is a relevant snippet:

PROCEDURE GetSalesDetail
(
    pivSalesView IN NUMBER,
    pivPartKey IN NUMBER,
    pivProfitCenter IN VARCHAR2,
    pivLvl4Code IN VARCHAR2,
    pivIndustry IN VARCHAR2,
    pivSoldToCountry IN VARCHAR2,
    pivFiscalYears IN VARCHAR2,
    pivCurrency IN VARCHAR2,
    pivCurrencyRates IN NUMBER,
    pivWWNumber IN VARCHAR2,
    pivMaxRecs IN NUMBER,
    povResults OUT Refcur
) 
  AS
      sql_query_string  VARCHAR2 (32767);
  BEGIN
     -- dynamically build sql_query_string:
      CASE pivSalesView
          WHEN 1 THEN
              -- Fiscal Year and Month
              sql_query_string := 'SELECT CP.tyco_electronics_corp_Part_nbr AS PARTNBR,';
              sql_query_string := sql_query_string || 'INITCAP(B.PART_DESC) AS PARTDESC, SUBSTR(S.TYCO_YEAR_ID || LPAD(S.TYCO_MONTH_OF_YEAR_ID,2,0),1,6) AS ByValue,';
...edited

              IF pivWWNumber != '' THEN
                  sql_query_string := sql_query_string || 'DM4.DM4033_CUSTOMER_HIER_DMN_V D, ';
                  sql_query_string := sql_query_string || 'GBL_PRIOR_MONTH_END.GBL_CUSTOMER_WORLDWIDE W, ';
              ELSE IF pivIndustry <> '0' THEN
                  sql_query_string := sql_query_string || 'DM4.DM4033_CUSTOMER_HIER_DMN_V D, ';
              END IF;
  
...edited  
              sql_query_string := sql_query_string || 'WHERE S.Part_Key_Id = B.Part_Key_Id ';
              sql_query_string := sql_query_string || 'AND S.Part_Key_Id = CP.Part_Key_ID ';
              sql_query_string := sql_query_string || 'AND S.Part_Key_Id = ' || pivPartKey || ' ';
              
              IF pivMaxRecs > 0 THEN
                  sql_query_string := sql_query_string || 'AND ROWNUM <= ' || pivMaxRecs || ' ';
              END IF;
              
...edited  
              sql_query_string := sql_query_string || 'AND (S.S_SHIPPED_LOCAL_CURRENCY_AMT <> 0 OR S.S_SHIPPED_QTY <> 0 OR S.O_ORDER_LOCAL_CURRENCY_AMT <> 0 OR S.O_ORDER_QTY <> 0) ';
              sql_query_string := sql_query_string || 'AND S.TRADE_INTERCO_IND = ''T'' ';
              sql_query_string := sql_query_string || 'AND (TO_CHAR(S.TYCO_YEAR_ID) ''' || pivFiscalYears || ''')'' ';
              sql_query_string := sql_query_string || 'GROUP BY CP.tyco_Electronics_corp_Part_nbr, B.PART_DESC, SUBSTR(S.TYCO_YEAR_ID || LPAD(S.TYCO_MONTH_OF_YEAR_ID,2,0),1,6) ';
              sql_query_string := sql_query_string || 'ORDER BY SUBSTR(S.TYCO_YEAR_ID || LPAD(S.TYCO_MONTH_OF_YEAR_ID,2,0),1,6) ';            
          WHEN 2 THEN
              -- Company - Fiscal Year and Month
... another similar block goes here...


This works perfectly fine
  PROCEDURE GetTest
  (
      pivSalesView IN NUMBER,
      pivOrderBy IN NUMBER,
      povResults OUT Refcur
  ) 
  AS
      sql_query_string  VARCHAR2 (32767);
  BEGIN
     -- dynamically build sql_query_string:
      CASE pivSalesView
          WHEN 1 THEN
            sql_query_string := 'SELECT ISO_CTRY_CODE, ISO_CTRY_CODE || ''-'' || ISO_CTRY_NAME AS SoldToCtryName' || pivSalesView || ' ';
            sql_query_string := sql_query_string || 'FROM GBL_PRIOR_MONTH_END.GBL_ISO_COUNTRY ';
            
            IF pivOrderBy > 0 THEN
                sql_query_string := sql_query_string || 'ORDER BY ' || pivOrderBy;
            END IF;
          WHEN 2 THEN
            sql_query_string := 'SELECT ISO_CTRY_CODE, ISO_CTRY_CODE || ''-'' || ISO_CTRY_NAME AS SoldToCtryName' || pivSalesView || ' ';
            sql_query_string := sql_query_string || 'FROM GBL_PRIOR_MONTH_END.GBL_ISO_COUNTRY ';
            
            IF pivOrderBy > 0 THEN
                sql_query_string := sql_query_string || 'ORDER BY ' || pivOrderBy;
            END IF;
      END CASE;
  
      OPEN povResults FOR sql_query_string;
  
  END GetTest;


What am I doing wrong?
Re: CASE WHEN problems [message #304225 is a reply to message #304222] Tue, 04 March 2008 10:21 Go to previous messageGo to next message
digigeek
Messages: 6
Registered: February 2008
Junior Member
Oops. I should really add the error:

Quote:

Error(430,11): PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: begin case declare end exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe The symbol "case" was substituted for "WHEN" to continue.



I get that on every WHEN <num> THEN line after the first one.

James
Re: CASE WHEN problems [message #304263 is a reply to message #304225] Tue, 04 March 2008 12:37 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
PL/SQL uses
elsif
not
else if
.
Re: CASE WHEN problems [message #304274 is a reply to message #304263] Tue, 04 March 2008 13:03 Go to previous message
digigeek
Messages: 6
Registered: February 2008
Junior Member
Doh! Funny. Thanks for opening the eyes.
Previous Topic: Using Dual to Generate Rows - Virtual Table
Next Topic: no data found !!
Goto Forum:
  


Current Time: Sat Dec 10 04:46:04 CST 2016

Total time taken to generate the page: 0.08921 seconds