locked
Report error - Invalid Number RRS feed

  • Question

  • I am passing multiple value to a oracle sql query from the report. SQL looks like

    columna in ( :inparam)
    In the report I have multi value parameter and expression defined 
    =JOIN(Parameters!inparam.Value,",")
    When I execute the report I am getting an error ora-01722 invalid number. Any idea on how to fix this?


    Thursday, January 3, 2013 4:01 AM

Answers

  • Hi SGeek,

    I agree with Santosh that the issue should occur because the mismatch between the data types of the parameter values passed to the query and the target column. If you have specified the data type of the parameter to Integer, please try to modify the WHERE clause of the query as follows:

    WHERE columna in (" +  Join(Parameters!inparam.Value,", ") + ")"

    References:

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Tuesday, January 15, 2013 1:12 AM
    Friday, January 4, 2013 2:19 AM
  • Hi SGeek,

    I'm not an Oracle guy so please pardon my ignorance here, but this issue could be due to data mismatch in parameters, e.g. if you trying to assign a string value to a numeric parameter.

    In order to narrow down your analysis, could you please hard code the values and check the SQL which is getting fired against your Oracle database. If you see the problem, you might need to write dynamic SQL so that it passes the right value to your Oracle engine after getting parameters from the SSRS's JOIN expression.

    HTH

    Regards,
    Santosh


    http://microsoftbizintel.wordpress.com/

    • Marked as answer by Mike Yin Tuesday, January 15, 2013 1:12 AM
    Thursday, January 3, 2013 11:16 AM

All replies

  • Hi SGeek,

    I'm not an Oracle guy so please pardon my ignorance here, but this issue could be due to data mismatch in parameters, e.g. if you trying to assign a string value to a numeric parameter.

    In order to narrow down your analysis, could you please hard code the values and check the SQL which is getting fired against your Oracle database. If you see the problem, you might need to write dynamic SQL so that it passes the right value to your Oracle engine after getting parameters from the SSRS's JOIN expression.

    HTH

    Regards,
    Santosh


    http://microsoftbizintel.wordpress.com/

    • Marked as answer by Mike Yin Tuesday, January 15, 2013 1:12 AM
    Thursday, January 3, 2013 11:16 AM
  • Hi SGeek,

    I agree with Santosh that the issue should occur because the mismatch between the data types of the parameter values passed to the query and the target column. If you have specified the data type of the parameter to Integer, please try to modify the WHERE clause of the query as follows:

    WHERE columna in (" +  Join(Parameters!inparam.Value,", ") + ")"

    References:

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Tuesday, January 15, 2013 1:12 AM
    Friday, January 4, 2013 2:19 AM