Script Error - Field not found

[#1]

Hi...

I am trying to load a table into qlikview but getting field not found error. The fields exist in the database.

Can someone help me with what I am missing out here?

Orders_Tab:
LOAD

date(floor(order_date)) AS link_Date,
date(floor(order_date)) AS Order_Date, //Load it a second time for making date range selections

order_Date - yearstart(order_Date) + 1 AS OrderDayWithinYear,
order_Date - monthstart(order_Date) + 1 AS OrderDayWithinMonth,

OID as OrderId,
scp_oid as SCP_Id,
entity_oid as Entity_Id;

SQL
SELECT oid,order_date,scp_oid,entity_oid from orders_tab ot
where TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY')
AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') and ot.order_status>1;

-----------------------Script Error-------------------------------------------------------

Field not found - <order_date>
SQL
SELECT oid,order_date,scp_oid,entity_oid from orders_tab ot
where TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY')
AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') and ot.order_status>1

Thank You.

Regards

Kumar

[Miguel_Angel_Baeyens · Accepted solution]

Hello Kumar,

Make sure you are loading the name of the field properly, as fields are case sensitive (order_date is not equal to Order_Date). As the field has the underscore, make sure also that it doesn't have to be enclosed in some way.

Did you try the sql query as it is and it returned results?

Regards.

[#3]

You change the case of the "d" in order_date on the 3rd and 4th fields in the load:

order_Date - yearstart(order_Date) + 1 AS OrderDayWithinYear,
order_Date - monthstart(order_Date) + 1 AS OrderDayWithinMonth

should be

order_date - yearstart(order_date) + 1 AS OrderDayWithinYear,
order_date - monthstart(order_date) + 1 AS OrderDayWithinMonth

so that the case is the same as the SQL select

[#4]

I changed it to the following:

Orders_Tab:
LOAD
order_date,
date(floor(order_date)) AS link_Date,
date(floor(order_date)) AS Order_Date, //Load it a second time for making date range selections

order_date - yearstart(order_date) + 1 AS OrderDayWithinYear,
order_date - monthstart(order_date) + 1 AS OrderDayWithinMonth,

OID as OrderId,
scp_oid as SCP_Id,
entity_oid as Entity_Id;

SQL
SELECT oid,trunc(ot.order_date) order_date,ot.scp_oid scp_oid,ot.entity_oid entity_oid from orders_tab ot
where TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY')
AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') and ot.order_status>1;

Still getting the same error...

[llauses243]

Hi,

Wait help you ...

When I use select with AS400 upper or lower case no important, but at load must be upper case

Then to CHANGE order_date by ORDER_DATE in script, then test

good luck, Luis

[#6]

Thanks for your inputs Miguel & Cowboy: I dint know that the script was case sensitive

Thanks IIauses: Your trick worked...

[#7]

As a side note and not really related to the issue but if you are using the pl/sql function trunc() on your date then you shouldnt really need the QV floor() function in the load as trunc by default should only return the date, seems like double work.