DSXchange

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,

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.

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

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.

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';

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';