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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #392768 is a reply to message #392640] |
Thu, 19 March 2009 04:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #392939 is a reply to message #392640] |
Thu, 19 March 2009 13:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #392954 is a reply to message #392951] |
Thu, 19 March 2009 16:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #392965 is a reply to message #392640] |
Thu, 19 March 2009 18:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 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.
|
|
|
Goto Forum:
Current Time: Sat Aug 31 00:19:46 CDT 2024
|