Error while using 'Date' as column name in ODBC stage

[durgaps]

Hi,

I am trying to extract a row having three columns from an application called HiPort. I am using an ODBC stage. The Three columns are col1 varchar(4),col2 varchar(10), date varchar(10). I have used these same column definitions in my datastage job. When I execute the following sql

select col1, col2, date from PERFORMANCE where col1 ='a1' AND date = '2006-08-08'

I get an error stating that col1, col2, date does not exist.

When i remove the date column and execuet the sql

select col1, col2 from PERFORMANCE where col1 ='a1' AND date = '2006-08-08'

, i get the desired results.

I even tried using an alias 'Date as Dt' but to no avail. Any idea how I can used the date column name in my select query in the ODBC stage?

PS: I am able to use the Date column name as mentioned in the first sql in other applications(non-datastage) to retrieve the results. This is quite puzzling. Please help.

Thanks,

[kumar_s]

Avoide column name as Keyword. Is tehere a way you can create a view for that table with different column name.
Pls post the exact message you get when you handle this.

[Mike]

Second that. Using SQL keywords as table or column names is bad practice. You can create a view as Kumar suggested or sometimes enclosing the column name in double quotes will work (e.g. SELECT col1, col2, "date" FROM).

Mike

[durgaps]

Thanks for the suggestion guys. I tried using "Date", but no success. The query I am using is:

select
FundCode, PerfCode, Portfolio, Date from
PERFORMANCE where CurrencyCode = 'AUD' and Date >= '2006-06-06';

Here PERFORMANCE is a DRO(Data Retrieval Object), a sort of wrapper used to access a system called HiPort through ODBC by other applications. The access method used by application developers is SQL.

There were 2 observations here:

1. When I remove Date from the Select stmt but remains intact in the where clause then the desired results are obtained.

2. When I execute the same query(quoted above) through other ODBC applications like I-Sql I get the returned records.

The err message recieved is:

##E TOPK 000000 11:13:10(001) <_PEEK_IDENT_> Input dataset does not have field: "FundCode"
##E TOPK 000000 11:13:10(002) <_PEEK_IDENT_> Input dataset does not have field: "PerfCode"
##E TOPK 000000 11:13:10(003) <_PEEK_IDENT_> Input dataset does not have field: "Portfolio"
##E TOPK 000000 11:13:10(004) <_PEEK_IDENT_> Input dataset does not have field: "Date"
##E TFSR 000019 11:13:10(006) <main_program> Could not check all operators because of previous error(s)
##E TCOS 000029 11:13:10(015) <main_program> Creation of step finished with status = FAILED

Any other alternatives for this.

Thanks.

[keshav0307]

try this:

select
FundCode, PerfCode, Portfolio, "DATE" from
PERFORMANCE where CurrencyCode = 'AUD' and Date >= '2006-06-06';

Make sure the column name in table is "DATE" not "Date", if the column name in table is "Date" then try

select
FundCode, PerfCode, Portfolio, "Date" from
PERFORMANCE where CurrencyCode = 'AUD' and Date >= '2006-06-06';

[keshav0307]

sorry, i missed the double quote in the where clause

select
FundCode, PerfCode, Portfolio, "DATE" from
PERFORMANCE where CurrencyCode = 'AUD' and "DATE" >= '2006-06-06';