OraFAQ Forum: Reports & Discoverer » Help with Date Parameters

Home » Developer & Programmer » Reports & Discoverer » Help with Date Parameters (Discoverer 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with Date Parameters [message #392640]

Wed, 18 March 2009 13:34

tboneangel
Messages: 3
Registered: March 2009
Location: Monrovia, CA

Junior Member

Hi all, this is probably a basic question but one that has me stumped.

I am writing a Birthday Report in Discoverer. I would like setup a parameter that the user can enter the beginning of a month to the end of the month and grab all employees who have a birthday in that month.

How do I get the parameters to look at Month & Day and ignore year? Example - I want all employees who have Birthday in the month of March.

Thanks for your help.

[Updated on: Wed, 18 March 2009 13:40]

Report message to a moderator

Re: Help with Date Parameters [message #392668 is a reply to message #392640]

Wed, 18 March 2009 16:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why wouldn't you rather enter month as a parameter (instead of two values - beginning and the end of the month)? Doing so, the WHERE clause would be rather simple:

... WHERE TO_CHAR(birth_date, 'MM') = :parameter_month_in_MM_format

Here's an example based on Scott's schema:

SQL> select ename, hiredate
  2  from emp
  3  where to_char(hiredate, 'mm') = &parameter_month;
Enter value for parameter_month: 12

ENAME      HIREDATE
---------- ----------
SMITH      17.12.1980
SCOTT      09.12.1982
JAMES      03.12.1981
FORD       03.12.1981

SQL>

Report message to a moderator

Re: Help with Date Parameters [message #392669 is a reply to message #392668]

Wed, 18 March 2009 16:52

tboneangel
Messages: 3
Registered: March 2009
Location: Monrovia, CA

Junior Member

Thanks - I didn't consider entering in just the month - makes complete sense!

Report message to a moderator

Re: Help with Date Parameters [message #392768 is a reply to message #392640]

Thu, 19 March 2009 04:19

swayamji
Messages: 15
Registered: March 2009
Location: Washington DC

Junior Member

SELECT *
FROM emp
WHERE TO_NUMBER(TO_CHAR(hiredate,'mm')) >= TO_NUMBER( TO_CHAR(:from_date,'mm'))
AND TO_NUMBER(TO_CHAR(hiredate,'mm')) <= TO_NUMBER( TO_CHAR(:TO_DATE,'mM'))

Regards
Swayamprakash Chiluveru

Report message to a moderator

Re: Help with Date Parameters [message #392919 is a reply to message #392640]

Thu, 19 March 2009 11:35

mytfein
Messages: 8
Registered: March 2009

Junior Member

Hi,

I had a similar date criteria requirement, but had trouble
this is what i did:

a) TO_CHAR(date, 'MON') = :parMonth

bec, I wanted user to enter "MAR" for March.

this query ran endlessly, and i think aborted with row id error.

b) it only worked when we did:

TO_CHAR(date, 'MON') LIKE :parMonth

and for parmMonth entered '%MAR%'

c) Anyone know why (a) using = did not work, but using LIKE
with wildcards worked?

c) ALSO:
my query runs a long time - any ideas?

thx for your help, Sandra

Report message to a moderator

Re: Help with Date Parameters [message #392920 is a reply to message #392640]

Thu, 19 March 2009 11:43

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

Post full query and an explain plan for it.

Sounds like oracle is generating a dodgy plan.
Have you gathered stats for your tables recently?

Report message to a moderator

Re: Help with Date Parameters [message #392939 is a reply to message #392640]

Thu, 19 March 2009 13:30

mytfein
Messages: 8
Registered: March 2009

Junior Member

Hi CM,

sql is below, i have to run query and then give you the plan
and the query takes awhile, so will post again.
thx, Sandra

SELECT DISTINCT o100321.last_name || '_' || o100321.first_name || '.jpg',
                TRUNC (o100290.start_date), TRUNC (o100290.end_date),
                TO_CHAR (o100290.start_date, 'MON'), o100384.academic_period,
                o100384.academic_year, o100071.advisor_first_name,
                o100071.advisor_last_name, o100071.advisor_type, o100322.city,
                o100389.course_number, o100389.course_reference_number,
                o100389.course_section_number, o100389.course_title_short,
                o100290.end_date, o100321.first_name,
                o100265.instructor_first_name, o100265.instructor_last_name,
                o100321.last_name, o100323.person_uid, o100322.postal_code,
                o100265.primary_ind, o100389.registration_status,
                o100290.section, o100290.start_date, o100322.state_province,
                o100322.street_line1, o100322.street_line2,
                o100386.student_attribute, o100384.student_status,
                o100290.subject, o100389.subject, o100290.title_short_desc
           FROM odsmgr.advisor o100071,
                odsmgr.instructional_assignment o100265,
                odsmgr.meeting_time o100290,
                odsmgr.person o100321,
                odsmgr.person_address o100322,
                odsmgr.person_detail o100323,
                odsmgr.student o100384,
                odsmgr.student_attribute o100386,
                odsmgr.student_course o100389
          WHERE (    (    o100389.course_reference_number = o100265.course_reference_number(+)
                      AND o100389.academic_period = o100265.academic_period(+)
                     )
                 AND (o100389.person_uid = o100321.person_uid)
                 AND (o100323.person_uid = o100322.person_uid)
                 AND (    o100384.person_uid = o100071.person_uid(+)
                      AND o100384.academic_period = o100071.academic_period(+)
                     )
                 AND (    o100384.person_uid = o100386.person_uid(+)
                      AND o100384.academic_period = o100386.academic_period(+)
                     )
                 AND (    o100384.person_uid = o100389.person_uid
                      AND o100384.academic_period = o100389.academic_period
                     )
                 AND (    o100389.academic_year = o100290.academic_year
                      AND o100389.academic_period = o100290.academic_period
                      AND o100389.course_reference_number =
                                               o100290.course_reference_number
                     )
                 AND (o100265.person_uid = o100323.person_uid)
                )
            AND ((TO_CHAR (o100290.start_date, 'MON')) LIKE :"Parm_StartMo")
            AND (o100290.sub_academic_period = 'MS4')
            AND (o100384.academic_period = :"term")
            AND (o100265.primary_ind(+) = 'Y')
            AND (o100071.advisor_type(+) = 'CLIN')
            AND (o100384.student_status = 'AS')
            AND (o100071.primary_advisor_ind(+) = 'Y')
            AND (o100389.registration_status IN ('RE', 'RW'))
            AND (o100386.student_attribute IN ('M4', 'M3'))
            AND (o100389.college = 'MD')
       ORDER BY o100290.start_date ASC;

[EDITED by LF: I have formatted a query and applied [code] tags]

[Updated on: Fri, 20 March 2009 01:08] by Moderator

Report message to a moderator

Re: Help with Date Parameters [message #392942 is a reply to message #392640]

Thu, 19 March 2009 13:40

mytfein
Messages: 8
Registered: March 2009

Junior Member

Hi CM,

I don't know ...

today the query failed on invalid row id

Any ideas on this error msg? pls advise, thx, Sandra

Report message to a moderator

Re: Help with Date Parameters [message #392951 is a reply to message #392640]

Thu, 19 March 2009 15:28

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's one ugly looking query! Please tell me that's auto-generated by some software tool.

Ok a few points
1) If you want people to read queries that large you really need to format them. Have a read of the how to format your post section of the orafaq forum guide and re-post that with code tags.
2) You don't need to run the query to get an explain plan. In SQLplus do the following:

EXPLAIN PLAN FOR <your select statement>;

SELECT * FROM table(dbms_xplan.display);

3) You really need to specify the oracle error number if you want us to diagnose it.

Report message to a moderator

Re: Help with Date Parameters [message #392954 is a reply to message #392951]

Thu, 19 March 2009 16:04

mytfein
Messages: 8
Registered: March 2009

Junior Member

Hi CM,

Thx for the advice and the code.

I spent the whole day getting

cmdline export discoverer to xls file to work

I have had nice success with it and am ready to call it a day.

I hope to reply more tommorrow.

thx again, Sandra

p.s. i have another question on the forum asking how to
send a query variable-parameters instead of hard-coding
the parameters in the command line statement.

i would like to get the parameter value from another source:
oracle, access, or excel - that the user would provide
and pop the value into the cmdline statement

do you have advice.

(My background is as an intermediate Microsoft Access developer, took pl/sql years ago. I have
sql developer on my pc, so i'll ask IT how to run the code you provided.)

thx, Sandra

Report message to a moderator

Re: Help with Date Parameters [message #392955 is a reply to message #392640]

Thu, 19 March 2009 16:06

mytfein
Messages: 8
Registered: March 2009

Junior Member

hi cm,

sql was generated by Discoverer... do you use that tool?

thx, Sandra

Report message to a moderator

Re: Help with Date Parameters [message #392965 is a reply to message #392640]

Thu, 19 March 2009 18:14

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

I've used Discoverer a few times but I'm more a reports person.

Doubt it matters for this issue though.

If you've got sql developer you should have sql plus as well.
Just start it up, enter login details for your database and type the commands as stated - there's nothing complicated.

Report message to a moderator

Previous Topic:	Batch Discoverer export - revisited
Next Topic:	Package help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 06:22:31 CST 2024