passing date parameters to sql script 1

[Macho123]

Hi I am using crystal reports 11 and I have a problem when I am trying to pass the date parametrs in to the sql script at the add command facility. I am getting the following error.

Failet to retrive data from the Database,
Missing right parantesis.

but when I run the same query in oracle it is working fine. could anybody help me out with this.

Thanks,
krant

 

[lbass]

You should NOT be adding a record selection formula in the Crystal GUI, but instead, add this within the command like:

select id, last_name, start_date, city
from employee
where
(
(
{?p1} <> date(2999,9,9) and
start_date >= {?p1}
) or
(
{?p1) = date(2999,9,9)and
start_date >= dateserial(year({fn NOW()}),month({fn NOW()})-1,1)
)
) and
(
(
{?p2} <> date(2999,9,9) and
start_date < {?p2} + 1
) or
(
{?p2) = date(2999,9,9) and
start_date < dateserial(year({fn NOW()}),month({fn NOW()}),1)
)
)

I'm not sure whether the NOW function applies to your database, but you should substitute whatever function does the equivalent.

-LB

 

[Macho123]

I am using oracle Db and it is not working at all. it says no mapped fields and nothing can be seen in th ereport not even the column names.

thanks...

 

[lbass]

Try pasting the following command into a new report command area. I'm not sure about the punctuation--when I use Oracle, I have to put double quotes around table and field names. I just changed the hard dates below.

select id, last_name, start_date, city
from employee
where
(
(
{?p1} <> to_date('2999-9-9') and
start_date >= {?p1}
) or
(
{?p1) = to_date('2999-9-9') and
start_date >= dateserial(year({fn NOW()}),month({fn NOW()})-1,1)
)
) and
(
(
{?p2} <> to_date('2999-9-9') and
start_date < {?p2} + 1
) or
(
{?p2) = to_date('2999-9-9') and
start_date < dateserial(year({fn NOW()}),month({fn NOW()}),1)
)
)

-LB

 

[Macho123]

This is not working too it sayd data can not be retrived from Db invalid charecter. help me please.

Thanks...

 

[lbass]

You can try posting your actual command, but I'm not sure I can help from here. You probably have to experiment. I'm guessing that dateserial and year and month functions probably aren't allowed either.

Starting over, why do you need to use a command in the first place? I don't see anything you are doing that would really require a command. Why not just add your table to a new report, and then use the Crystal GUI. You would then just add the following to report->selection formula->record:

(
(
{?p1} <> date(2999,9,9) and
{table.start_date} >= {?p1}
) or
(
{?p1) = date(2999,9,9)and
{table.date} >= dateserial(year(currentdate),month(currentdate)-1,1)
)
) and
(
(
{?p2} <> date(2999,9,9) and
{table.start_date} < {?p2} + 1
) or
(
{?p2) = date(2999,9,9) and
{table.start_date} < dateserial(year(currentdate),month(currentdate),1)
)
)

-LB

 

[Macho123]

Actually the query i posted here is very simple and the original query is very big and has lot of joins and groupings and if i have to do all that directly in the reports with out the command it is taking lot of time to retrive data in to the report and then perfor the groupings and joins. I am using oracle9 and crystal 11.

Thanks...

 

[Macho123]

Lbass nay clues or sugggestions on how to do this. because with out this i could not schedule the report and scheduling is very important for this report.


Thanks...

 

[lbass]

I'm sorry, but I'm all out of suggestions. If you want someone to troubleshoot the command, you would need to show the actual command, not just your simplification of it.

-LB

 

[Macho123]

lbass i can not put the code directly here but if the code what you give works for the above query then i can put it in my original code it is only changing the field names. so please look in to it.

 

[Macho123]

hi lbass finally got and thanks to my brotherinlaw rasheed. he told me this and the query is something like this.

select id, last_name, start_date, city
from employee
where start_date between
TRUNC (ADD_MONTHS (SYSDATE, -1),'month')
AND LAST_DAY (TRUNC (ADD_MONTHS (SYSDATE,-1),'month'))

i added this in the command directly with out any parameters on the crystal side and this is working fine to generate the data for the previous month.

Thanks to you for putting lot of efforts and being at paitence with me.

Thanks...