topic Reload problem. Where Clause causing field to go null in QlikView

Reload problem. Where Clause causing field to go null

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Reload problem. Where Clause causing field to go null

shiveshsingh — Tue, 20 Mar 2018 16:53:40 GMT

Do you have values in cost column for 2018?

Re: Reload problem. Where Clause causing field to go null

sunny_talwar — Tue, 20 Mar 2018 16:54:23 GMT

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

Re: Reload problem. Where Clause causing field to go null

Peter_Cammaert — Tue, 20 Mar 2018 16:54:59 GMT

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?

Re: Reload problem. Where Clause causing field to go null

wdchristensen — Tue, 20 Mar 2018 16:57:28 GMT

select

Tbl.transaction_code,

Tbl.date_created,

Tbl.cost

from IFSAPP.INVENTORY_TRANSACTION_HIST_TAB as Tbl

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

Re: Reload problem. Where Clause causing field to go null

Anonymous — Tue, 20 Mar 2018 16:57:54 GMT

Yes there are values for 2018

Re: Reload problem. Where Clause causing field to go null

wdchristensen — Tue, 20 Mar 2018 16:58:27 GMT

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

Re: Reload problem. Where Clause causing field to go null

Anonymous — Tue, 20 Mar 2018 16:59:00 GMT

Already tried with alias, doesn't work unfortunately.

Re: Reload problem. Where Clause causing field to go null

wdchristensen — Tue, 20 Mar 2018 17:01:39 GMT

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?

Re: Reload problem. Where Clause causing field to go null

Peter_Cammaert — Tue, 20 Mar 2018 17:02:34 GMT

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

Re: Reload problem. Where Clause causing field to go null

wdchristensen — Tue, 20 Mar 2018 17:20:23 GMT

If you pull the data for a different year is it populated? Is so, maybe the cost field has not been populated yet for 2018.

Re: Reload problem. Where Clause causing field to go null

shiveshsingh — Tue, 20 Mar 2018 17:26:39 GMT

What's the o/p in sql developer? Are you getting values?

select

transaction_code,

date_created,

cost

from IFSAPP.INVENTORY_TRANSACTION_HIST_TAB

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

Re: Reload problem. Where Clause causing field to go null

sunny_talwar — Tue, 20 Mar 2018 17:28:25 GMT

May be try this

SELECT

transaction_code,

date_created,

cost,

TO_CHAR(DATE_CREATED,'YYYY') year

from IFSAPP.INVENTORY_TRANSACTION_HIST_TAB;

Do you see year 2018 and in those rows does cost have any value?