Clarity

Expand all | Collapse all

param_startdate is not taking any values

Jump to Best Answer
  • 1.  param_startdate is not taking any values

    Posted 02-08-2017 03:51 AM

    Hello Everyone

    i am working on some portlet issue, where the background query for this portlet is having the code like this SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual.

    :param_startdate is not taking any values given in the format like this: 02062017, 06022017, 06-FEB-2017, 020617, 060217and 06-FEB-17. this giving me an error.

    ORA-01722: invalid number
    01722. 00000 -  "invalid number"
    *Cause:    The specified number was invalid.
    *Action:   Specify a valid number.

     

    checked this ora-01722 in google, its saying :The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

    not sure where i am missing any value/type/format.

    waiting for your wonderful solutions:)

     

    Thanks

    Daya



  • 2.  Re: param_startdate is not taking any values

    Posted 02-08-2017 03:59 AM

    You code should look like this;

     

    SELECT trunc(NVL(@WHERE:PARAM:USER_DEF:DATE:startdate@, sysdate), 'DY') + 1 cur_monday FROM dual

    not

    SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual



  • 3.  Re: param_startdate is not taking any values

    Posted 02-08-2017 04:04 AM

    HI Dave,

     

    Sorry i did not tell you that i am running this query in Oracle SQL Developer.

    and the query its working fine in the clarity query/portlet section. to check the result i took out by clicking the preview and stuck at this line to get the result.

    SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual

     

    Thanks

    Daya



  • 4.  Re: param_startdate is not taking any values
    Best Answer

    Posted 02-08-2017 04:12 AM

    So that will depend on whether you have set a date format in your SQL*Developer or not (when it converts your string input to a date datatype)

     

    But if all you are doing is "debugging" then perhaps do this sort of thing;

    SELECT trunc(NVL(to_date(:param_startdate,'dd/mm/yyyy'), sysdate), 'DY') + 1 cur_monday FROM dual

    then you know how the string to date conversion is going to work



  • 5.  Re: param_startdate is not taking any values

    Posted 02-08-2017 04:29 AM

    HI Dave

     

    thanks. i am getting the result.

    Thanks Again

    Daya