Re: SQL 2005 Reporting Services querying an Oracle DB

Hi David,

Thanks for the suggestion - it failed though unfortunately. Got a "[BC30004] Character constant must contain exactly one character" error.

This happened before being able to even pick a date range from the report.

Thanks,
David

fitzjarrell_at_cox.net wrote:
> djclothier_at_gmail.com wrote:
> > Hi.
> >
> > I'm not a DBA by any stretch, but will try and explain as best i can
> > here:
> >
> > I'm writing some reports in SQL 2005 Reporting Services - hitting an
> > Oracle DB. It's quite a simple report, but I'm running into the
> > "ORA-01036: illegal variable name/number" error. For the report
> > Parameters I've defined startDate and endDate, and for the Dataset
> > parameters I have defined the same.
> >
> > But for the query below I still get the error. any ideas?
> >
> > = "SELECT LOG_TRANSACTION_DAILY_VIEW.LOG_DATE,
> > LOG_TRANSACTION_DAILY_VIEW.CHANNEL,
> > LOG_TRANSACTION_DAILY_VIEW.OPERATOR,
> > LOG_TRANSACTION_DAILY_VIEW.CELLECTIVITY_AFFILIATE_ID,
> > LOG_TRANSACTION_DAILY_VIEW.CELLECTIVITY_AFFILIATE_ID,
> > LOG_TRANSACTION_DAILY_VIEW.VENDOR_NAME,
> > LOG_TRANSACTION_DAILY_VIEW.APPLICATION,
> > LOG_TRANSACTION_DAILY_VIEW.SUB_TYPE,
> > LOG_TRANSACTION_DAILY_VIEW.TRANSACTION_TYPE,
> > LOG_TRANSACTION_DAILY_VIEW.TOTAL, LOG_TRANSACTION_DAILY_VIEW.COUNT
> > FROM INCELLIGENCE.LOG_TRANSACTION_DAILY_VIEW LOG_TRANSACTION_DAILY_VIEW
> >
> > WHERE to_date(LOG_TRANSACTION_DAILY_VIEW.LOG_DATE,'MM/DD/YYYY') BETWEEN
> > to_date('" & Parameters!startDate.Value & "','MM/DD/YYYY') AND
> > to_date('" & Parameters!endDate.Value & "','MM/DD/YYYY')
> > ORDER BY LOG_TRANSACTION_DAILY_VIEW.LOG_DATE DESC;"
> >
> > Thanks in advance,
> > David
>
> I expect it's LOG_TRANSACTION_DAILY_VIEW.COUNT causing your problems.
> Either enclose the column name in quotes
> (LOG_TRANSACTION_DAILY_VIEW."COUNT") or have the view rebuilt with a
> column name which is not a reserved word.
>
>
> David Fitzjarrell
Received on Mon Jun 05 2006 - 10:41:21 CDT