Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2015
    Posts
    5

    Unanswered: Invalid character found in a character string aurgument of the function "Integer"

    Hi, below is the sql query. I get the below error while I try to refresh the database.

    Invalid character found in a character string aurgument of the function "Integer"

    SQL Query:

    with rdate as( select '2015-06-24' dt from sysibm.sysdummy1), fcid as( select max(fc_id) fc_id from backlog.forecast a join rdate on 1=1 where t<=timestamp(rdate.dt||'-23.59.00') group by proj_id, finl_ctry_nm, fc_year ), exrate as( select iso_crncy_cd, Case Month(rdate.dt) when 1 then jan when 2 then feb when 3 then mar when 4 then apr when 5 then may when 6 then jun when 7 then jul when 8 then aug when 9 then sep when 10 then oct when 11 then nov when 12 then dec end exrt from backlog.v_curexrate join rdate on 1=1 where Int(fc_year) = Year(rdate.dt) ) select b.PROJ_ID, b.LGL_CNTRCT_ID, b.CUST_NM, b.CUST_NUM, b.PROJ_DESC, b.KEY_PLAY, b.SECTOR, b.CMR_ISU_NM, b.VVSPL, b.OFFRG_CMPNT_CD, b.OPPORTUNITYNO, b.VSVC_OEM, b.CURR_CVRG_ID, b.PRODUCTID, b.BP_NAME, b.BILL_TO_CUST_NUM, b.OWNG_ORG_CD2, b.CHANNEL_NM, b.SGNG_DATE, b.CNTRCT_STRT_DT, b.CNTRCT_END_DT, b.BUS_MEASMT_DIV_ID, b.LDGR_CD, b.ISU_CD, b.ISU_NM, b.CONTRACTNUMBER, b.BUSINESSTYPE, b.CHARGECODE, b.SBEXCEPTIONCODE, b.SIGNINGSIND, b.MAJOR_ACCT_NUM, b.MINOR_ACCT_NUM, b.ISO_CRNCY_CD, b.SGNG_CMPNT_$K, b.SGNG_CTRY_$K, case when coalesce(b.SGNG_PLNRT_US_$K,0)=0 then b.sgng_cmpnt_$k/d.jan else b.SGNG_PLNRT_US_$K end SGNG_PLNRT_US_$K, b.FINL_CTRY_NM, b.ACTIVE, b.UNAME, b.WINS_ID, b.NET, b.ANNUITY, b.GP, INT(A.FC_YEAR) FC_YEAR, a.jan/(case when month(rdate.dt)<2 and year(rdate.dt)<=int(a.fc_year) then exrt else d.jan end) jan, a.feb/(case when month(rdate.dt)<3 and year(rdate.dt)<=int(a.fc_year) then exrt else d.feb end) feb, a.mar/(case when month(rdate.dt)<4 and year(rdate.dt)<=int(a.fc_year) then exrt else d.mar end) mar, a.apr/(case when month(rdate.dt)<5 and year(rdate.dt)<=int(a.fc_year) then exrt else d.apr end) apr, a.may/(case when month(rdate.dt)<6 and year(rdate.dt)<=int(a.fc_year) then exrt else d.may end) may, a.jun/(case when month(rdate.dt)<7 and year(rdate.dt)<=int(a.fc_year) then exrt else d.jun end) jun, a.jul/(case when month(rdate.dt)<8 and year(rdate.dt)<=int(a.fc_year) then exrt else d.jul end) jul, a.aug/(case when month(rdate.dt)<9 and year(rdate.dt)<=int(a.fc_year) then exrt else d.aug end) aug, a.sep/(case when month(rdate.dt)<10 and year(rdate.dt)<=int(a.fc_year) then exrt else d.sep end) sep, a.oct/(case when month(rdate.dt)<11 and year(rdate.dt)<=int(a.fc_year) then exrt else d.oct end) oct, a.nov/(case when month(rdate.dt)<12 and year(rdate.dt)<=int(a.fc_year) then exrt else d.nov end) nov, a.dec/(case when month(rdate.dt)=1 and year(rdate.dt)<=int(a.fc_year)+1 then exrt else d.dec end) dec, Case pd.revtreatment when 'POC' then pd.revtreatment when 'ANN' then pd.revtreatment else c.shortname end shortname, date(a.t) t from backlog.forecast a join backlog.project b on a.proj_id=b.proj_id and a.finl_ctry_nm=b.finl_ctry_nm join backlog.revschedule c on coalesce(b.annuity,0)=c.revschedid join backlog.v_curexrate d on a.fc_year=d.fc_year and b.iso_crncy_cd=d.iso_crncy_cd join fcid b on a.fc_id=b.fc_id join exrate on exrate.iso_crncy_cd=b.iso_crncy_cd join rdate on 1=1 left outer join backlog.projectdetails pd on b.proj_id=pd.proj_id and b.finl_ctry_nm=pd.finl_ctry_nm where ( coalesce(A.jan,0)<>0 or coalesce(A.feb,0)<>0 or coalesce(A.mar,0)<>0 or coalesce(A.apr,0)<>0 or coalesce(A.may,0)<>0 or coalesce(A.jun,0)<>0 or coalesce(A.jul,0)<>0 or coalesce(A.aug,0)<>0 or coalesce(A.sep,0)<>0 or coalesce(A.oct,0)<>0 or coalesce(A.nov,0)<>0 or coalesce(A.dec,0)<>0 ) and int(a.fc_year)>=year(rdate.dt) and b.vvspl<>'SSMA' AND (B.FINL_CTRY_NM='SINGAPORE' OR 'ASEAN'='SINGAPORE') and coalesce(pd.sr,0)<>1


    What could have gone wrong?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess is bad data. Run:
    Code:
    SELECT *
       FROM backlog.forecast AS a
       WHERE  Convert(a.fc_year, SIGNED INTEGER) IS NULL
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2015
    Posts
    5

    Invalid character found in a character string aurgument of the function "Integer"

    Thanks Pat! Im new to SQL. Just to understand your answer clearly.

    SELECT *
    FROM backlog.forecast AS a
    WHERE Convert(a.fc_year, SIGNED INTEGER) IS NULL

    Does the above mean that I have to add "AS" to the code "FROM backlog.forecast a" as was mentioned in my original code?

    & do I need to replace INT(A.FC_YEAR) with WHERE Convert(a.fc_year, SIGNED INTEGER)?

    IS NULL - is this a part of the SQL code?

    Regards,
    Nischal.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use of AS is recommended, but not required yet.

    The query that I provided will "ferret out" the rows with bad data. Otherwise it is unrelated to your original query which I didn't evaluate well enough to offer much of an opinion.

    No, leave the INT(A.FC_YEAR) as is... It performs one function which is similar to but not completely interchangeable with the code that I provided to help you find the bad data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2015
    Posts
    5

    Invalid character found in a character string aurgument of the function "Integer"

    Quote Originally Posted by Pat Phelan View Post
    Use of AS is recommended, but not required yet.

    The query that I provided will "ferret out" the rows with bad data. Otherwise it is unrelated to your original query which I didn't evaluate well enough to offer much of an opinion.

    No, leave the INT(A.FC_YEAR) as is... It performs one function which is similar to but not completely interchangeable with the code that I provided to help you find the bad data.

    -PatP
    Thanks Pat! But would it be possible for you to go a bit detail into my query pertaining to invalid character found under argument of the function "Integer"?

    Any help would be very helpful for us to move forward! The problem is we are end users here & are stuck up with this which no one here is able to figure it out.

    Regards,
    Nischal.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In the A.FC_YEAR reference there are three important parts. The period is a reserved character within SQL which indicates that this is a qualified name. The A before the period designates which table name or alias is involved, and in this particular case the A is the alias for the backlog.forecast table or view. The FC_YEAR part of the reference is the name of a column within the backlog.forecast table or view.

    There is apparently one or more characters in one or more rows of backlog.forecast.FC_YEAR which is not valid, meaning that it might be something like 'bob' instead of '2013'. When you run the query that I posted earlier in this thread, it will display the rows which have data in them that can't be successfully converted to an integer form. You will need to either add logic to your SELECT statement to exclude those rows, or fix the offending data by replacing it with correct data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2015
    Posts
    5

    Invalid character found in a character string aurgument of the function "Integer"

    Quote Originally Posted by Pat Phelan View Post
    In the A.FC_YEAR reference there are three important parts. The period is a reserved character within SQL which indicates that this is a qualified name. The A before the period designates which table name or alias is involved, and in this particular case the A is the alias for the backlog.forecast table or view. The FC_YEAR part of the reference is the name of a column within the backlog.forecast table or view.

    There is apparently one or more characters in one or more rows of backlog.forecast.FC_YEAR which is not valid, meaning that it might be something like 'bob' instead of '2013'. When you run the query that I posted earlier in this thread, it will display the rows which have data in them that can't be successfully converted to an integer form. You will need to either add logic to your SELECT statement to exclude those rows, or fix the offending data by replacing it with correct data.

    -PatP
    Thanks for your detailed explanation Pat! Im some what able to relate to this now. So, to check the bad run I need to run the below query that you suggested.

    SELECT *
    FROM backlog.forecast AS a
    WHERE Convert(a.fc_year, SIGNED INTEGER) IS NULL

    I had one more question relating to testing, where do I place the above code to get the data tested for bad sectors, or which are not integers? & is there any changes to the above code that I would need to do before I run the query?

    Regards,
    Nischal.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code that I provided to find the problem rows is related to but independent of the code that you provided which is reporting the invalid characters. My code ought to be run "as is" and by itself, most probably in a separate file or window.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2015
    Posts
    5

    Invalid character found in a character string aurgument of the function "Integer"

    Thanks Pat! So, the existing query which throws this error is placed under excel - data - connections.

    Do I place your query too under excel or is there a different database where I need to place your code?

    & if at all it has to be placed under excel like my original code, how do I execute it? Just click on the "refresh all" button available under excel - data - connections?

    & where does the bad data result throw? Under the excel file?

    Sorry, my questions may be irrelevant & frustrating.

    Regards,
    Nischal.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm making a lot of assumptions about your environment. If you have a DBA or at least a help desk, you'd really be much better off asking them.

    I'd create a whole new sheet in Excel, and use the Data | Other Sources drop down to build a connection to your MySQL database and paste in the query that I sent you.

    This raises a whole different set of questions about your MySQL database, how you connect to it, how you edit data in in, whether you should just filter out the offending rows, etc. Your problem is expanding past the point where someone like me (outside of your organization and office) can be much help. I'll gladly offer my insight and opinions, but we're standing at the edge of where I'm comfortable advising you... You really need to ask someone with authority and technical insight in your own office or organization to help you. Just following my suggestions past this point could get both of us into trouble!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •