Crystal Report Date Parameter Usage with Oracle

[reportbuilder]

Hi all,

I'm new to Crystal Reports. I'm encountering the below error when attempting to use Crystal Report 11's date parameter within my sql statement against an Oracle database date column.

Oracle Char Set:
NLS_CHARACTERSET - WE8MSWIN1252
NLS_DATE_FORMAT - DD-MON-RR

Error:
Details: 22018:[Oracle][ODBC][Ora]ORA-01722: invalid number
[Database Vendor Code: 1722

Table/column
SHIP_DATE_ACTUAL - DATE

Query:
WHERE
msi.organization_id = 2 and
msi.inventory_item_id = dil.inventory_item_id and
msi.attribute14 like 'FG%' and
msi.segment1 < '99' and
rc.customer_class_code = 'RET' and
rc.customer_id = dih.bill_to_customer_id and
dil.line_type = 'LINE' and
dil.invoice_id = dih.invoice_id and
to_date(to_char(dih.ship_date_actual,'mm/dd/yyyy'),'mm/dd/yyyy') <
to_date(to_char('?DateParam','mm/dd/yyyy'),'mm/dd/yyyy')
GROUP BY
msi.attribute15, dih.ship_date_actual
order by dih.ship_date_actual desc

 

[Turkbear]

Hi,
First I would eliminate the ToDate(ToChar(
functions for the

to_date(to_char(dih.ship_date_actual,'mm/dd/yyyy'),'mm/dd/yyyy')

line, use
{dih.ship_date} since it already is a DATE type field, why convert to text then to date?

I always use string parameters for dates, but your method for the < part should work for converting to an Oracle date. Are you sure that is the line that causes the error? Check the Parameter value to be sure it is passing a valid date and in the correct format to your ToText function..









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[reportbuilder]

I agree with you statement. To simplify the issue, I created a quick query stated below as a test. Given that I defined the parameter ?DateParam and field ship_date_actual as type DATE, I surprise to determine that I'm receiving the error "ORA-1858: a non-numeric character found". The is why I'm working with the to_char and to_date. I'm not sure if this may be related to the date character set for server



SELECT
dih.ship_date_actual
FROM
ra_customers rc,
mtl_system_items msi,
dw_invoice_lines dil,
dw_invoice_headers dih
WHERE
msi.organization_id = 2 and
msi.inventory_item_id = dil.inventory_item_id and
msi.attribute14 like 'FG%' and
msi.segment1 < '99' and
rc.customer_class_code = 'RET' and
rc.customer_id = dih.bill_to_customer_id and
dil.line_type = 'LINE' and
dil.invoice_id = dih.invoice_id and
dih.ship_date_actual < '?DateParam'
GROUP BY
dih.ship_date_actual
order by dih.ship_date_actual desc

 

[lbass]

If you are creating the query in a CR command, you should remove the single quotes around the date parm, and use curly brackets (assuming you've also created the parameter within the command), so that the line looks like:

dih.ship_date_actual < {?DateParam}

-LB

 

[inncoggnitto]

i had similar problems w/oracle 9i timestamp field and cr10. i found a patch (upgrade/sp) that fixed the problem for me.