Re: SQL 2005 Reporting Services querying an Oracle DB

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 - 08:04:45 CDT