ORA-00905: Missing Keyword????

[sskrobe]

version: Crystal Reports 9.0

I'm trying to add an SQL query with parameters int eh add command window and I keep getting the message:

Failed to open a rowset.
Details: HY000:[Oracle][ODBC][Ora]ORA-00905: missing keyword.

Below is my SQL statement:

Select
h.whse_ind, h.instance_ind, h.ord_no, h.cust_no, h.cust_name, h.dest_id, h.wght, h.delivery, r.division, r.prod_fam, r.product_line, r.product_type, SUM(r.wght), SUM(r.act_qty)
From
v_rh_ordhead h, v_rh_orddetl r
where
h.last_ship_date between '{?startdate}' and '{?enddate}'
and h.rh_ord_cntrl_no = r.rh_ord_cntrl_no
group by
h.whse_ind, h.instance_ind, h.ord_no, h.cust_no, h.cust_name, h.dest_id, h.wght, h.delivery, r.division, r.prod_fam, r.product_line, r.product_type

 

[Turkbear]

Hi, Check the Sql for blank spaces or CRs before the Select or From ...

 

[sskrobe]

I just tried putting it in a block form:

Select h.whse_ind, h.instance_ind, h.ord_no, h.cust_no, h.cust_name, h.dest_id, h.wght, h.delivery, r.division, r.prod_fam, r.product_line, r.product_type, SUM(r.wght), SUM(r.act_qty) From v_rh_ordhead h, v_rh_orddetl r Where h.last_ship_date between '{?startdate}' and '{?enddate}' and h.rh_ord_cntrl_no = r.rh_ord_cntrl_no group by h.whse_ind, h.instance_ind, h.ord_no, h.cust_no, h.cust_name, h.dest_id, h.wght, h.delivery, r.division, r.prod_fam, r.product_line, r.product_type

and still nothing :-(

Thanks for the tip though!

 

[Turkbear]

Hi,
I suspect the quoted date parameters are what is causing the problem..Try hard-coding 2 dates into the query and see what happens..

Where h.last_ship_date between
to_date('20040101','YYYYMMDD')
and to_date(20040901','YYYYMMDD')

 

[sskrobe]

THANKS!!! yeah, the hardcoded dates ran.

So how can I get the params to take??


Woohoo!! Forward progress!!!

 

[sskrobe]

On a hunch, I tried:

.....
where
(h.last_ship_date between to_date('{?startdate}' ,'YYYYMMDD')
and to_date('{?enddate}' ,'YYYYMMDD'))
and ......

But I get the message:

ORA-00907: missing right parenthesis


I also tried:

.....
where
h.last_ship_date between (to_date('{?startdate}' ,'YYYYMMDD')
and to_date('{?enddate}' ,'YYYYMMDD'))
and ......


and I got the same error message

w00t????

 

[Turkbear]

Hi, Did you try it without using your parameter fields?

I am not sure you can use Crystal Parameters like that in a Sql command object..

 

[sskrobe]

Yeah, the hardcoded dates ran fine.

So how can I get the date params to take??

 

[Turkbear]

Hi,
The only way I see to do it is to create the command object without the Date criteria and place them instead in the Record Selection Criteria section.
CR 'sees' the command object like any other data source so it should work just like it would if you were using a Table or View..
So your where/group by clause in the command object would be:

Where h.rh_ord_cntrl_no = r.rh_ord_cntrl_no group by h.whse_ind, h.instance_ind, h.ord_no, h.cust_no, h.cust_name, h.dest_id, h.wght, h.delivery, r.division, r.prod_fam, r.product_line, r.product_type

And in the Report Designer add this to your Record Selection criteria..

h.last_ship_date between {?startdate} and {?enddate}


Assumes that last_ship_date is an actual Date field and that the parameter is defined as a Date type - otherwise use the Date() function in CR to convert the parameter string to a date.

 

[sskrobe]

Yeah, I tried that because I was the thinking the same thing.

The problem is that this query pulls back SOOO much data, that when I leave the date range out, it get the TEMP tablespace size issue.


*sigh*


I only have one patch of hair left and I'm about ready to pull it out :-!

I appreciate the help, any other ideas?

 

[Turkbear]

Hi,
Is the parameter a Date or String type?

If a string use Oracle conversion function:
Where h.last_ship_date between to_date({?startdate},'<FORMATHERE>' and to_date({?enddate},'<SAMEFORMAT>')

Use the Show Sql Query ( if possible) to see if all the where clause is being sent to Oracle for handling..Also be sure to have the Perform Grouping on the Server option checked..