Clarity PPM

Expand all | Collapse all

NSQL start_date Parameter Issue

Jump to Best Answer
  • 1.  NSQL start_date Parameter Issue

    Posted 09-29-2015 07:17 AM

    Hi all,

     

    I am trying to grab results from the table PRTIMEPERIOD based on the input of the user, but it does not work.

    An direct SQL statement on the db gives results. I guess that the date picker input from the user is not compatible with the date type in the db?

     

    Original

    SELECT @SELECT:TP.PRID:PRID@,

    @SELECT:TP.PRUID:UNIQUE_CODE@,

    @SELECT:TP.PRISOPEN:PRISOPEN@,

    @SELECT:TP.PRFINISH:PRFINISH@,

    @SELECT:TP.PRSTART:PRSTART@,

    @SELECT:TO_CHAR(TP.PRSTART,'DD/MM/YYYY') || ' - ' || TO_CHAR(TP.PRFINISH-1,'DD/MM/YYYY'):TIMEPERIOD@

    FROM   PRTIMEPERIOD TP

    WHERE  TP.PRISOPEN <> 3

    and    (@WHERE:PARAM:USER_DEF:DATE:START_DATE@ IS NULL OR TP.PRSTART >=

        @WHERE:PARAM:USER_DEF:DATE:START_DATE@) AND

    (@WHERE:PARAM:USER_DEF:DATE:END_DATE@ IS NULL OR TP.PRSTART <=

        @WHERE:PARAM:USER_DEF:DATE:END_DATE@)

    AND    @FILTER@

    Modification

    SELECT @SELECT:TP.PRID:PRID@,

    @SELECT:TP.PRUID:UNIQUE_CODE@,

    @SELECT:TP.PRISOPEN:PRISOPEN@,

    @SELECT:TP.PRFINISH:PRFINISH@,

    @SELECT:TP.PRSTART:PRSTART@,

    @SELECT:TO_CHAR(TP.PRSTART,'DD/MM/YYYY') || ' - ' || TO_CHAR(TP.PRFINISH-1,'DD/MM/YYYY'):TIMEPERIOD@

    FROM   PRTIMEPERIOD TP

    WHERE  TP.PRISOPEN <> 3

    and    (TP.PRSTART BETWEEN TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'DD/MM/YYYY')

    AND TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:END_DATE@, 'DD/MM/YYYY'))

    AND    @FILTER@

     

    It's about the bold part. The user gives a start date and end date. I have tried different things but all with no results.

    I also tried:

    WHERE TP.PRSTART BETWEEN @WHERE:PARAM:USER_DEF:DATE:START_DATE@ AND

    @WHERE:PARAM:USER_DEF:DATE:END_DATE@

    This is also not working:

    WHERE TP.PRSTART > @WHERE:PARAM:USER_DEF:DATE:START_DATE@


    I'll guess that the user input, a date picker, is not compatible with the date field in the database?

    When I execute the query directly on the db then I get results.

     

    Any help is much appreciated, thank you in advance.



  • 2.  Re: NSQL start_date Parameter Issue

    Posted 09-29-2015 07:26 AM

    TP.PRSTART BETWEEN TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'DD/MM/YYYY')

    AND TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:END_DATE@, 'DD/MM/YYYY'))

     

    The above should be

     

    TP.PRSTART BETWEEN TO_DATE(@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'DD/MM/YYYY')

    AND TO_DATE(@WHERE:PARAM:USER_DEF:DATE:END_DATE@, 'DD/MM/YYYY'))


    Check if this helps

     

    NJ



  • 3.  Re: NSQL start_date Parameter Issue

    Posted 09-29-2015 08:10 AM

    Thanks NJ, but I have tried it to change it to to_date but unfortunately it doesn't work.

    I also tried:

    WHERE TP.PRSTART > @WHERE:PARAM:USER_DEF:DATE:START_DATE@


    Somehow it doesn't take the user defined start date correctly, because when I type the query directly then it works:

    WHERE TP.PRSTART > '31-8-2015' (this gives results)




  • 4.  Re: NSQL start_date Parameter Issue
    Best Answer

    Posted 09-29-2015 07:38 AM

    Why not drop the parameters, and just set the PRSTART Field Element Type to "Date Range"? Isn't that what you want to accomplish?



  • 5.  Re: NSQL start_date Parameter Issue

    Posted 09-29-2015 08:11 AM

    Thanks Andrew but this is a portlet which will be used by all users. I find the date picker more user-friendly. I think that the date range results in more questions.



  • 6.  Re: NSQL start_date Parameter Issue

    Posted 09-29-2015 08:29 AM

    I don't think you have understood the advice you were given ; a "Date Range" filter parameter would work exactly like what it appears you are trying to accomplish with two user-parameter date fields. Just add PRSTART coulm to the filter section of your portlet and change its display type (in the List Filter Section / Fields) - to "Date Range" (from "Date")



  • 7.  Re: NSQL start_date Parameter Issue

    Posted 09-29-2015 08:52 AM

    I understand it now. Thanks for the clarification. I have used PRSTART as colum in the filter section and that works perfect!

     

    Thank you for the help all.