1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To_char function in report lexical parameter

Discussion in 'Oracle Forms and Reports' started by shobhit.sharma, Aug 19, 2015.

  1. shobhit.sharma

    shobhit.sharma Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    150
    Location:
    Mumbai
    Hi All,

    My query is to know , how can we put to_char in after report parameter.


    my query in after parameter form is


    :lp_a ='and s.gl_date between (SELECT TO_CHAR((TRUNC(SYSDATE ,'MON')-1),'DD-MON-RRRR') FROM DUAL) and (select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE , 'MON'),-3),'DD-MON-RRRR') from dual)'


    but when i am trying to run report, it is giving error, and seems i can not use commas '',

    for 'MON' or 'DD-MON-RRRR'.

    can any one please help me out, or if any other way to use lexical parameter.

    i want to fetch Invoice data based on the condition of last 3 months , and YTD..

    so how to use these conditions in Lexical parameters.


    <snip>
     
    Last edited by a moderator: Aug 19, 2015
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    s.gl_date - this field of type date or string as format dd-mm-rrrr?
    if s.gl_date is date, then why to use conversion of date to string type?

    if it
    Code (SQL):
    'and s.gl_date between (SELECT TO_CHAR((TRUNC(SYSDATE ,'MON')-1),'DD-MON-RRRR') FROM DUAL) and (select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE , 'MON'),-3),'DD-MON-RRRR') from dual)'
     
    is part of a predicate, I suppose, it is possible to use simpler option :

    Code (SQL):
    s.gl_date BETWEEN add_months(trunc(sysdate,'mm'),-3),add_months(trunc(sysdate,'mm'),-1)
     
    Last edited: Aug 19, 2015
  3. nilesh.dudhane

    nilesh.dudhane Active Member

    Messages:
    42
    Likes Received:
    2
    Trophy Points:
    135
    Location:
    India
    Hi Shobhit,
    Using following code..
    Code (SQL):
    'and s.gl_date between (SELECT TO_CHAR((TRUNC(SYSDATE ,''MON'')-1),''DD-MON-RRRR'') FROM DUAL) and (select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE , ''MON''),-3),''DD-MON-RRRR'') from dual)'
    Thanks
    Nilesh
     
  4. shobhit.sharma

    shobhit.sharma Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    150
    Location:
    Mumbai
    Hi Nilesh ,
    thanks for the update,,,i will try and will update you if need any further help..