This discussion is archived
1 Reply Latest reply: Nov 13, 2012 9:30 PM by jeneesh RSS

syntax error?

952526 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points