3 Replies Latest reply: Jan 16, 2014 4:33 AM by hemu RSS

    ORA-01858: a non-numeric character was found where a numeric was expected

    hemu

      hi

      i have created a page in sample database application (page 9)

      workspace           hrk

      user                    hemukarnik@gmail.com

      password             Hrk!123 

       

      the same query runs perfectly at sql prompt

      i do not get any such  error

      select branch,PRD,
      to_char(sum(a),'9999999999999.99') product,
      TO_CHAR((SUM(A)/(to_date('&P9_TODATE','dd-mon-yyyy')-to_date('&P9_FROMDATE','dd-mon-yyyy'))  ),'9999999999.99') AVG,
      LAST_BAL,
      LAST_CBL_DATE,
      MIN_DATE
      from
        (
      select branch,prd,bal,
      case when days<>0  then bal*days else bal end as a,
      days,LAST_BAL,LAST_CBL_DATE,MIN_DATE
      from
        (
      SELECT LBRCODE branch,PRDACCTID prd,CBLDATE c_date,
       BALANCE4 AS BAL,
      LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) PRE_DATE,
      (LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) - CBLDATE) DAYS,
      LAST_VALUE(BALANCE4) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_BAL,
      LAST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_CBL_DATE,
      FIRST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_DATE
      FROM trn
      WHERE FLOOR(SUBSTR(PRDACCTID,17,8)) <>0
      and trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
      AND cbldate between to_date('&P9_FROMDATE','dd-mon-yyyy') and to_date('&P9_TODATE','dd-mon-yyyy')
      )
      )group by branch,prd,LAST_BAL,LAST_CBL_DATE,MIN_DATE ORDER BY BRANCH,PRD
      /
      

      can somebody help me ??

        • 1. Re: ORA-01858: a non-numeric character was found where a numeric was expected
          Chief Wiggum

          Please replace

           

          to_date('&P9_FROMDATE','dd-mon-yyyy') and to_date('&P9_TODATE','dd-mon-yyyy'

          with

          to_date(:P9_FROMDATE,'dd-mon-yyyy') and to_date(:P9_TODATE,'dd-mon-yyyy'


          and try

          • 2. Re: ORA-01858: a non-numeric character was found where a numeric was expected
            fac586

            hemu wrote:

             

            hi

            i have created a page in sample database application (page 9)

            workspace           hrk

            user                    hemukarnik@gmail.com

            password             Hrk!123

             

            the same query runs perfectly at sql prompt

            i do not get any such  error

            select branch,PRD,
            to_char(sum(a),'9999999999999.99') product,
            TO_CHAR((SUM(A)/(to_date('&P9_TODATE','dd-mon-yyyy')-to_date('&P9_FROMDATE','dd-mon-yyyy'))  ),'9999999999.99') AVG,
            LAST_BAL,
            LAST_CBL_DATE,
            MIN_DATE
            from
              (
            select branch,prd,bal,
            case when days<>0  then bal*days else bal end as a,
            days,LAST_BAL,LAST_CBL_DATE,MIN_DATE
            from
              (
            SELECT LBRCODE branch,PRDACCTID prd,CBLDATE c_date,
             BALANCE4 AS BAL,
            LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) PRE_DATE,
            (LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) - CBLDATE) DAYS,
            LAST_VALUE(BALANCE4) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_BAL,
            LAST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_CBL_DATE,
            FIRST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_DATE
            FROM trn
            WHERE FLOOR(SUBSTR(PRDACCTID,17,8)) <>0
            and trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
            AND cbldate between to_date('&P9_FROMDATE','dd-mon-yyyy') and to_date('&P9_TODATE','dd-mon-yyyy')
            )
            )group by branch,prd,LAST_BAL,LAST_CBL_DATE,MIN_DATE ORDER BY BRANCH,PRD
            /

            can somebody help me ??

            That isn't the code that was used in the report. What I found on looking at the app was:

             

            select branch,PRD,
            to_char(sum(a),'9999999999999.99') product,
            TO_CHAR((SUM(A)/(:P9_TODATE-:P9_FROMDATE)),'9999999999.99') AVG,
            LAST_BAL,
            LAST_CBL_DATE,
            MIN_DATE
            from
              (
            select branch,prd,bal,
            case when days<>0  then bal*days else bal end as a,
            days,LAST_BAL,LAST_CBL_DATE,MIN_DATE
            from
              (
            SELECT LBRCODE branch,PRDACCTID prd,CBLDATE c_date,
            BALANCE4 AS BAL,
            LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) PRE_DATE,
            (LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) - CBLDATE) DAYS,
            LAST_VALUE(BALANCE4) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_BAL,
            LAST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_CBL_DATE,
            FIRST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_DATE
            FROM trn
            WHERE FLOOR(SUBSTR(PRDACCTID,17,8)) <>0
            and trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
            AND cbldate between :P9_FROMDATE and :P9_TODATE
            )
            )group by branch,prd,LAST_BAL,LAST_CBL_DATE,MIN_DATE ORDER BY BRANCH,PRD
            
            

             

            Maybe someone else changed it? If so, they probably want to remain anonymous, as that can't work. All APEX item values are stored as VARCHAR2, therefore when an item value is referenced as NUMBER, DATE, or any other non-character type in SQL or PL/SQL, it must be explicitly converted:

             

            select branch,PRD,
            to_char(sum(a),'9999999999999.99') product,
            TO_CHAR((SUM(A)/(to_date(:P900_TODATE, 'DD-MON-YYYY')-to_date(:P900_FROMDATE, 'DD-MON-YYYY'))),'9999999999.99') AVG,
            LAST_BAL,
            LAST_CBL_DATE,
            MIN_DATE
            from
              (
            select branch,prd,bal,
            case when days<>0  then bal*days else bal end as a,
            days,LAST_BAL,LAST_CBL_DATE,MIN_DATE
            from
              (
            SELECT LBRCODE branch,PRDACCTID prd,CBLDATE c_date,
            BALANCE4 AS BAL,
            LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) PRE_DATE,
            (LEAD(CBLDATE,1,cbldate) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE) - CBLDATE) DAYS,
            LAST_VALUE(BALANCE4) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_BAL,
            LAST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_CBL_DATE,
            FIRST_VALUE(CBLDATE) OVER(PARTITION BY LBRCODE,PRDACCTID ORDER BY LBRCODE,CBLDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_DATE
            FROM trn
            WHERE FLOOR(SUBSTR(PRDACCTID,17,8)) <>0
            and trim(substr(prdacctid,1,8)) in ('CC','CDOD','DDPOD')
            AND cbldate between to_date(:P900_FROMDATE, 'DD-MON-YYYY') and to_date(:P900_TODATE, 'DD-MON-YYYY')
            )
            )group by branch,prd,LAST_BAL,LAST_CBL_DATE,MIN_DATE ORDER BY BRANCH,PRD
            
            

             

            See fixed version in copy of region on page 900.

             

            Also note that bind variable syntax (":P1_XYZ") should be used when referencing item values in SQL and PL/SQL inside APEX. Only use static text substitution ("&P1_XYZ.") for lexical substitution (and only use it with extreme caution in SQL and PL/SQL because of the risk of SQL injection).