Date Parameter help needed for CR XI 1

[satinsilhouette]

Hi,

I am for the first time having to write pure PL/SQL and need to include a parameter to drive a report based off a query in Oracle. I need it to be a range, start date and end date entered by the user.

date = startdate to enddate.

Can someone provide me with this?

Thanks

 

[SantaMufasa]

Satin,

Please specify the PL/SQL "venue" you want us to program for you:

1) plain, "anonymous" PL/SQL script within which you simply want to use scripted prompt values that we then embed and replace at the appropriate spot(s) in your code, or

2) PL/SQL user-defined procedure, or

3) PL/SQL user-defined function.

The default presumption is that you will be running your PL/SQL from within the SQL*Plus environment. Please confirm that, as well, or specify some other environment if you are not using SQL*Plus. (If you are not using SQL*Plus, then option 1, above, does not apply.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[satinsilhouette]

Yes and No.

I am able to take PL/SQL or any other type of SQL and place that within an object in CR XI and by pass the CR Query Engine. I do this a lot, this is the first time I have directly used an Oracle interface and native driver.

Here is the code I ahve so far, I need to adjust this to accept a range on the post date for a start and end date filled in by the user.

select account_id, account_name,name,post_date, tx_amount
from account left outer join transactions
on account.account_id = transactions.account_id
where post_date ={?Post_Date}(CR XI parameter fed into PL/SQL)

 

[SantaMufasa]

Satin,

The sample code that you posted above, is not PL/SQL per se; it is straight SQL. Since many contributors here (including me) do not have access to Crystal Reports, I propose that we get your code running first in Oracle's text-based default SQL interface, SQL*Plus (that resides by default on all Oracle client and server installations), then we make the simple jump across to CR.

Also, to clarify, your sample code says:

...where post_date =...

Your original post indicates that you want your result set to include the rows where the post_date is between some prompted start date and a prompted end date. Although I don't know the CR syntax to make that happen, the SQL*Plus code would look something like this:

accept beg_date prompt "Enter the start date (DD-MON-YYYY): "
accept end_date prompt "Enter the end date (DD-MON-YYYY): "
SELECT ...
...where post_date [b]between[/b] to_date('&beg_date','DD-MON-YYYY')
                       and to_date('&end_date','DD-MON-YYYY')

Let us know what you would like to do going forward.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[satinsilhouette]

Thank you Mufasa, you gave me enough information to change the code to get it to through the two calendars for start and end date!