I have a query that runs correctly from SQL Developer but bombs when attempting to use it as a command-text query with parameters in Crystal Reports 2008.
The Query (hard-coded dates; runs correctly as Oracle Query and CR 2008 Command Text)
SELECT *
FROM (SELECT PERSON.DISPLAYNAME AS NAME
, LABOR.LA20 AS LABORRECEMPLOYEENUM
, LABOR.LABORCODE AS PERSONLABORCODE
FROM LABOR
LEFT OUTER JOIN PERSON
ON ( LABOR.LABORCODE = PERSON.PERSONID )
WHERE LABOR.LA20 IS NOT NULL
AND LABOR.ACTIVE = 1
AND PERSON.DISPLAYNAME IS NOT NULL
AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%kimball%'
AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%electrico%'
AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%misc labor cost adj%'
AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%brossoit'
AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%brossiot')PERSONINFO
LEFT OUTER JOIN (SELECT *
FROM LABTRANS
WHERE LABTRANS.STARTDATE BETWEEN to_date('03/01/2011', 'mm/dd/yyyy') AND to_date('04/01/2011', 'mm/dd/yyyy')) REDUCEDLABORRANGE
ON ( PERSONINFO.PERSONLABORCODE = REDUCEDLABORRANGE.LABORCODE )
WHERE REDUCEDLABORRANGE.LABORCODE IS NULL;
What I'd like to do
Replace the hard-coded values:
WHERE LABTRANS.STARTDATE BETWEEN to_date('03/01/2011', 'mm/dd/yyyy') AND to_date('04/01/2011', 'mm/dd/yyyy')
With Crystal Reports parameters in command text.
What I've tried (and errors)
WHERE LABTRANS.STARTDATE BETWEEN to_date({?BeginDate}, 'mm/dd/yyyy') AND to_date({?EndDate}, 'mm/dd/yyyy')
Yields the error: A non-numeric character was found where a numeric was expected [Database Vendor Code: 1858]
WHERE LABTRANS.STARTDATE BETWEEN to_date('{?BeginDate}', 'mm/dd/yyyy') AND to_date('{?EndDate}', 'mm/dd/yyyy')
Yields the error: Missing Right Parenthesis [Database Vendor Code 907]
Potential Keys to the solution
- I can't figure out what Crystal is actually sticking into the parameter values, or how they're passed on to the query.
