Reload problem. Where Clause causing field to go null

[Anonymous]

I am loading the inventory history transaction table from IFS (PL/SQL) using the following script:

select

     transaction_code,

     date_created,

     cost

from IFSAPP.INVENTORY_TRANSACTION_HIST_TAB;

this loads the whole table which is massive so I have been trying to restrict it to one year:

select

     transaction_code,

     date_created,

     cost

from IFSAPP.INVENTORY_TRANSACTION_HIST_TAB

where TO_CHAR(DATE_CREATED,'YYYY') = '2018';

this works except the cost field shows only null values.

Has anyone else encountered this problem?

[shiveshsingh]

Do you have values in cost column for 2018?

[sunny_talwar]

Are there rows in year 2018 where cost is not null?

[Peter_Cammaert]

That's more of a question for an Oracle PL/SQL forum.

All SQL statements are sent as-is through the ODBC/OLEDB driver to the database engine for analysis and execution. The results are then sent back and stored in a resident table. The latter part doesn't filter data, so if there is no data in column cost, then that probably means that Oracle isn't sending back any.

What happens if you run this SQL statement in the standard Oracle command line tool (SQL*Plus)? Do you get any values in cost?

[wdchristensen]

select

     Tbl.transaction_code,

     Tbl.date_created,

     Tbl.cost

from IFSAPP.INVENTORY_TRANSACTION_HIST_TAB as Tbl

where TO_CHAR(DATE_CREATED,'YYYY') = '2018';

[Anonymous]

Yes there are values for 2018

[wdchristensen]

"Cost" may be a reserved word which means you would need to qualify it.

[Anonymous]

Already tried with alias, doesn't work unfortunately.

[wdchristensen]

If you run

Select top 1 Tbl.*

From IFSAPP.INVENTORY_TRANSACTION_HIST_TAB as Tbl

where Tbl.cost > 0

Do you even have a column for cost?

[Peter_Cammaert]

Cost is not a reserved word in Oracle SQL. See Oracle SQL Reserved Words