1 Reply Latest reply: Nov 13, 2012 11:30 PM by jeneesh RSS

    syntax error?

    952526
      hi, why I got ORA-00907: missing right parenthesis in the folowing while trying to create this table ?


      CREATE TABLE TN_PROVISION_MONTHLY_TEST AS
      SELECT /*+ USE_HASH(P T L U) */ ADD_MONTHS((SELECT TRUNC(SYSDATE,''MM'') FROM DUAL),-1) REPORT_MONTH,
      T.USER_ID,
      REGEXP_SUBSTR(L.LOGIN_NAME,''[0-9]+'') PTN,
      P.PRODUCT_CODE,
      T.BILLING_ID,
      T.ORDER_NUMBER,
      P.PRICE,
      U.CARRIER_ID,
      P.CREATE_TIME START_TIME,
      P.EXPIRATION_DATE END_TIME
      FROM TN_PRODUCT P,
      TN_TRANSACTION T,
      TN_LOGIN L,
      TN_USER U
      WHERE L.USER_ID = T.USER_ID
      AND T.BILLING_ID = P.BILLING_ID
      AND T.USER_ID = U.USER_ID
      AND P.CREATE_TIME < (SELECT TRUNC(SYSDATE,''MM'') FROM DUAL)
      AND P.EXPIRATION_DATE >= ADD_MONTHS((SELECT TRUNC(SYSDATE,''MM'') FROM DUAL),-1)';
        • 1. Re: syntax error?
          jeneesh
          949523 wrote:
          hi, why I got ORA-00907: missing right parenthesis in the folowing while trying to create this table ?


          CREATE TABLE TN_PROVISION_MONTHLY_TEST AS
          SELECT /*+ USE_HASH(P T L U) */ ADD_MONTHS((SELECT TRUNC(SYSDATE,''MM'') FROM DUAL),-1) REPORT_MONTH,
          You dont need to put two single quotes. One single quote is enough
          AND P.CREATE_TIME < (SELECT TRUNC(SYSDATE,''MM'') FROM DUAL)
          No need to select from dual here
          AND P.EXPIRATION_DATE >= ADD_MONTHS((SELECT TRUNC(SYSDATE,''MM'') FROM DUAL),-1)';
          And here

          Something like (Not tested)
          CREATE TABLE TN_PROVISION_MONTHLY_TEST AS
          SELECT /*+ USE_HASH(P T L U) */ 
               ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1) REPORT_MONTH,
               T.USER_ID,
               REGEXP_SUBSTR(L.LOGIN_NAME,'[0-9]+') PTN,
               P.PRODUCT_CODE,
               T.BILLING_ID,
               T.ORDER_NUMBER,
               P.PRICE,
               U.CARRIER_ID,
               P.CREATE_TIME START_TIME,
               P.EXPIRATION_DATE END_TIME
          FROM      TN_PRODUCT P,
               TN_TRANSACTION T,
               TN_LOGIN L,
               TN_USER U
          WHERE L.USER_ID = T.USER_ID
          AND T.BILLING_ID = P.BILLING_ID
          AND T.USER_ID = U.USER_ID
          AND P.CREATE_TIME < TRUNC(SYSDATE,'MM')
          AND P.EXPIRATION_DATE >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1);