Home » Developer & Programmer » Reports & Discoverer » Help with Date Parameters (Discoverer 10g)
Help with Date Parameters [message #392640] Wed, 18 March 2009 13:34 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: Help with Date Parameters [message #392669 is a reply to message #392668] Wed, 18 March 2009 16:52 Go to previous messageGo to next message
tboneangel
Messages: 3
Registered: March 2009
Location: Monrovia, CA
Junior Member
Thanks - I didn't consider entering in just the month - makes complete sense!
Re: Help with Date Parameters [message #392768 is a reply to message #392640] Thu, 19 March 2009 04:19 Go to previous messageGo to next message
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
Re: Help with Date Parameters [message #392919 is a reply to message #392640] Thu, 19 March 2009 11:35 Go to previous messageGo to next message
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
Re: Help with Date Parameters [message #392920 is a reply to message #392640] Thu, 19 March 2009 11:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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?
Re: Help with Date Parameters [message #392939 is a reply to message #392640] Thu, 19 March 2009 13:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Help with Date Parameters [message #392951 is a reply to message #392640] Thu, 19 March 2009 15:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
/forum/fa/450/0/

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.
Re: Help with Date Parameters [message #392954 is a reply to message #392951] Thu, 19 March 2009 16:04 Go to previous messageGo to next message
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



Re: Help with Date Parameters [message #392955 is a reply to message #392640] Thu, 19 March 2009 16:06 Go to previous messageGo to next message
mytfein
Messages: 8
Registered: March 2009
Junior Member
hi cm,

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

thx, Sandra
Re: Help with Date Parameters [message #392965 is a reply to message #392640] Thu, 19 March 2009 18:14 Go to previous message
cookiemonster
Messages: 13917
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.
Previous Topic: Batch Discoverer export - revisited
Next Topic: Package help
Goto Forum:
  


Current Time: Thu Mar 28 14:43:53 CDT 2024