2 Replies Latest reply: Jan 2, 2013 8:23 PM by Frank Kulash RSS

    SQL query concat - numeric or value error

    SeshuGiri
      I am getting numeric or value error when concatenating sql script..

      Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)

      V_SAMPLEQUERY VARCHAR2(2000);
           
      firstpart:_

      V_SAMPLEQUERY := '      
      SELECT

      AB.ABCDID AS ABCDID,
      CD.MEMBERID AS MEMBERID,
      CD.FNAME AS FNAME,
      CD.LNAME AS LNAME,
      CD.ACCOUNT AS ACCOUNT,
      EF.ZIP AS ZIP,
      AB.INSERTTIME AS INSERTTIME,
      AB.INSERTBY AS INSERTBY,
      ES.NAME AS STATUS,
      MYFUNCTION(E.ABCDID) AS AMOUNT,
      PA.NAME AS SOUSENAME,
      R.CODE AS BUDGETCODE,
      WX.ACCOUNTID as ACCOUNTID,

      AB.SOMETYPE AS SOMETYPE,
      AB.ACCTREP,
      ED.TYPE AS TYPE,
      AB.NOTES AS NOTES,
      GH.AREACODE || '''' || GH.PHONE_NUMBER AS PHONENUMBER,
      ED.REQUESTEDDATE,
      ED.BOOKED,

      ED.CALLERINFO

      FROM PEOPLES AB,
      LOCATIONS CD,
      LOCATIONTYPES EF,
      ACCOUNTS GH,
      ACCOUNTdetails IJ,
      ACCOUNTstatus KL,
      ACCOUNTMEMBERDETAILS MN,
      MEMBERS OP,
      BUDGET QR,

      BCODES ST,
      ACCOUNTSYSTEM UV,
      INFOCALL WX,
      PHONE_NUMBERS YZ

      WHERE AT.NAME = ''SAVINGS''

      andCD.MEMBERID = PH.MEMBERID
      andCD.MEMBERID =EF.MEMBERID
      and AT.ADDRESSTYPEID =EF.ADDRESSTYPEID

      andCD.MEMBERID =AB.MEMBERID
      andAB.STATUS = ES.STATUS(+)
      andAB.ABCDID = ED.ABCDID
      andAB.ABCDID = EP.ABCDID(+)
      and EP.MEBER_ID = PA.MEBER_ID(+)
      andAB.BID = R.BID(+)

      andAB.SAMPLEID = PR.SAMPLEID(+)
      andAB.ABCDID = BSG.ABCDID(+)
      andAB.INFOCALLID = C.INFOCALLID(+)
      and YZ.SOMETYPEID = ''10000''



      ANDAB.INSERTTIME > SYSDATE - 35
      ANDAB.INSERTTIME < ''
      AND WX.NAME IN (''ABCD'',
      ''EFGH'',
      ''IJKL'',
      ''MNOP'',
      ''QRST'',
      ''UVWX'')
      AND MN.NAME = ''FINISHED'' '

      secondpart:_ ***ERROR PART***

      V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error
        • 1. Re: SQL query concat - numeric or value error
          sb92075
          V_SAMPLEQUERY VARCHAR2(4000);

          does doing as above change anything?
          • 2. Re: SQL query concat - numeric or value error
            Frank Kulash
            Hi,

            Wheneve you have a problem, prlease post a complete test script that people can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for any tables used, abnd the results you want it to produce.
            SeshuGiri wrote:
            I am getting numeric or value error when concatenating sql script..
            Are you really getting the error when you concatenate, or when you try to execute the resulting string?
            Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)

            V_SAMPLEQUERY VARCHAR2(2000);
            Is that big enough? It looks like the string is right around 1000 characters; depending on your character set, that could be more than 2000 bytes. VARCHAR2s in PL/SQL can hold up to 32767 bytes.
                 
            firstpart:_

            V_SAMPLEQUERY := '      
            SELECT

            AB.ABCDID AS ABCDID,
            CD.MEMBERID AS MEMBERID,
            CD.FNAME AS FNAME,
            CD.LNAME AS LNAME,
            CD.ACCOUNT AS ACCOUNT,
            EF.ZIP AS ZIP,
            AB.INSERTTIME AS INSERTTIME,
            AB.INSERTBY AS INSERTBY,
            ES.NAME AS STATUS,
            MYFUNCTION(E.ABCDID) AS AMOUNT,
            PA.NAME AS SOUSENAME,
            R.CODE AS BUDGETCODE,
            WX.ACCOUNTID as ACCOUNTID,

            AB.SOMETYPE AS SOMETYPE,
            AB.ACCTREP,
            ED.TYPE AS TYPE,
            AB.NOTES AS NOTES,
            GH.AREACODE || '''' || GH.PHONE_NUMBER AS PHONENUMBER,
            ED.REQUESTEDDATE,
            ED.BOOKED,

            ED.CALLERINFO

            FROM PEOPLES AB,
            LOCATIONS CD,
            LOCATIONTYPES EF,
            ACCOUNTS GH,
            ACCOUNTdetails IJ,
            ACCOUNTstatus KL,
            ACCOUNTMEMBERDETAILS MN,
            MEMBERS OP,
            BUDGET QR,

            BCODES ST,
            ACCOUNTSYSTEM UV,
            INFOCALL WX,
            PHONE_NUMBERS YZ

            WHERE AT.NAME = ''SAVINGS''

            andCD.MEMBERID = PH.MEMBERID
            andCD.MEMBERID =EF.MEMBERID
            and AT.ADDRESSTYPEID =EF.ADDRESSTYPEID

            andCD.MEMBERID =AB.MEMBERID
            You need some whitespace after the keyword "and", in the line above and also several places below.
            andAB.STATUS = ES.STATUS(+)
            andAB.ABCDID = ED.ABCDID
            andAB.ABCDID = EP.ABCDID(+)
            and EP.MEBER_ID = PA.MEBER_ID(+)
            andAB.BID = R.BID(+)

            andAB.SAMPLEID = PR.SAMPLEID(+)
            andAB.ABCDID = BSG.ABCDID(+)
            andAB.INFOCALLID = C.INFOCALLID(+)
            and YZ.SOMETYPEID = ''10000''



            ANDAB.INSERTTIME > SYSDATE - 35
            ANDAB.INSERTTIME < ''
            Did something get cut off here?
            AND WX.NAME IN (''ABCD'',
            ''EFGH'',
            ''IJKL'',
            ''MNOP'',
            ''QRST'',
            ''UVWX'')
            AND MN.NAME = ''FINISHED'' '
            It looks like you're constructing a string for EXECUTE IMMEDIATE, but why not just use a static query?
            secondpart:_ ***ERROR PART***

            V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error
            USER is the name of a built-in function, which means it's not a good name for your own column. Try user_name instead.