Date Parameter in a Command object (Oracle)

[sylvar]

Ok,
I am trying to parameterize the Start and End dates for a PL/SQL call.

It Runs with the values hard coded like this:
where ad_date between '3-NOV-2004' and '31-DEC-2005'

It returnd nothing when parameterized and passing the same date through the Parameters....Like this:
where ad_date between to_date({?StartDate}, 'dd/mm/yyyy') and to_date({?EndDate}, 'dd/mm/yyyy')

Here is the entire SQL call with Parameters in place (they are in the bottom section):

select curr_month,
ProdLine,
classification,
severity,
count(distinct new_bugs) new_bugs,
count(distinct prior_open_bugs) prior_open_bugs,
count(distinct new_open_bugs) new_open_bugs,
count(distinct open_now_closed_later_bugs) open_now_closed_later_bugs,
count(distinct new_now_closed_later_bugs) new_now_closed_later_bugs,
count(distinct closed_bugs) closed_bugs
from (select curr_month,
bg_user_29 ProdLine,
bg_user_03 classification,
bg_severity severity,
decode (to_number(to_char(bg_detection_date, 'yyyymm')), curr_month, bg_bug_id, NULL) new_bugs,
CASE
WHEN (bg_detection_date IS NOT NULL AND
to_number(to_char(bg_detection_date, 'yyyymm')) < curr_month) AND
(bg_closing_date IS NULL)
THEN bg_bug_id
ELSE NULL
END prior_open_bugs, -- detected_prior_and_still_open_bugs
CASE
WHEN (bg_detection_date IS NOT NULL AND
to_number(to_char(bg_detection_date, 'yyyymm')) = curr_month) AND
(bg_closing_date IS NULL)
THEN bg_bug_id
ELSE NULL
END new_open_bugs, --detected_this_month_and_still_open_bugs,
CASE
WHEN (bg_detection_date IS NOT NULL AND
to_number(to_char(bg_detection_date, 'yyyymm')) < curr_month) AND
(bg_closing_date IS NOT NULL AND
to_char(bg_closing_date, 'yyyymm') > curr_month
)
THEN bg_bug_id
ELSE NULL
END open_now_closed_later_bugs, --detected_prior_open_now_closed_later_bugs
CASE
WHEN (bg_detection_date IS NOT NULL AND
to_number(to_char(bg_detection_date, 'yyyymm')) = curr_month) AND
(bg_closing_date IS NOT NULL AND
to_char(bg_closing_date, 'yyyymm') > curr_month
)
THEN bg_bug_id
ELSE NULL
END new_now_closed_later_bugs, --detected_now_open_now_closed_later_bugs
decode (to_number(to_char(bg_closing_date, 'yyyymm')), curr_month, bg_bug_id, NULL) closed_bugs
from bug a,
(select distinct to_number(to_char(ad_date, 'yyyymm')) curr_month
from all_dates
where ad_date between to_date({?StartDate}, 'dd/mm/yyyy') and to_date({?EndDate}, 'dd/mm/yyyy')
) b
where bg_user_29 = '{?ProductLine}'
)
group by curr_month, ProdLine, classification, severity
order by 1,2



So....How does one use date parameters for Oracle SQL?

Thanks in Advance!

Shane

 

[synapsevampire]

Your mask on the to_date looks suspicious to me compared to the hard coded values, and you don't state what the data type is in Oracle, nor the parameter.

Anyway, it appears that you're using a Command for the query

The hard coded values show the month with a 3 char month, the parmeter is masked at 2 chars.

What happens if you eliminate the to_date function and just use a date parameter?

My knowledge is limited here because I would create a View in Oracle and include the date as a field being returned and just use the conventional Crystal GUI to use the View in Crystal, that way the View is available to other processes, and a Crystal date parm will automatically be passed in the proper format.

-k