Where condition in SQL statement

[#1]

I want to filter the data I load into Qlikview by date and this command runs on Oracle fine but Qlikview gets stuck when I execute this query.

SalesHeader:

LOAD "CUSTOMER_ACCOUNT" as CustCode,

    "FIRST_ORDER",

    "ORDER_COST" as Cost,

    "ORDER_DATE" as Date,

    "ORDER_VALUE" as Value,

    "SALES_DOCUMENT_NUM" as DocumentNo,

    "ORDER_VALUE"-"ORDER_COST" as Margin,

    "ORDER_DATE" as SaleDate;

SQL SELECT "CUSTOMER_ACCOUNT",

    "FIRST_ORDER",

    "ORDER_COST",

    "ORDER_DATE",

    "ORDER_VALUE",

    "SALES_DOCUMENT_NUM"

FROM TEST."SALES_HEADER"

where order_date >=1072915200;

Please help.

thanks in advance.

Lavanya

[pat_agen]

hi,

try and cast the order_date and your test using the to_date() function from Oracle. It is probably the the odbc layer that is throwing up the error.

[Miguel_Angel_Baeyens]

Hello Lavanya,

Although QlikView date format is a numeric value as well, you may need to do some transformation in your dates when pulling info from Oracle, something like

SalesHeader:
LOAD "CUSTOMER_ACCOUNT" as CustCode,
    "FIRST_ORDER",
    "ORDER_COST" as Cost,
    "ORDER_DATE" as Date,
    "ORDER_VALUE" as Value,
    "SALES_DOCUMENT_NUM" as DocumentNo,
    "ORDER_VALUE"-"ORDER_COST" as Margin,
    "ORDER_DATE" as SaleDate;
SQL SELECT "CUSTOMER_ACCOUNT",
    "FIRST_ORDER",
    "ORDER_COST",
    "ORDER_DATE",
    "ORDER_VALUE",
    "SALES_DOCUMENT_NUM"
FROM TEST."SALES_HEADER"
where TO_DATE(ORDER_DATE, 'DD/MM/YYYY') >= TO_DATE('01/01/2010', 'DD/MM/YYYY');

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Thanks for your reply Miguel.

Unfortunately, the values in Order_date is a 10 digit number which is calculated as

trunc(to_date('01-Jan-1970')+("ORDER_DATE"/86400))

Here ORDER_DATE is some date like 31Jul2009

In Oracle data base typical order_date value is stored as 1248999003 which is equal to 31-Jul-2009.

I wanted to filter the data greater than 1072915200.

I shall try to_date option if it works anyway.

[#5]

I made the changes as below but still the same problem. Qlikview gets into NotResponding mode.

SalesHeader:

LOAD "CUSTOMER_ACCOUNT" as CustCode,

    "FIRST_ORDER",

    "ORDER_COST" as Cost,

    "ORDER_DATE" as Date,

    "ORDER_VALUE" as Value,

    "SALES_DOCUMENT_NUM" as DocumentNo,

    "ORDER_VALUE"-"ORDER_COST" as Margin,

    "ORDER_DATE" as SaleDate;

SQL SELECT "CUSTOMER_ACCOUNT",

    "FIRST_ORDER",

    "ORDER_COST",

    "ORDER_DATE",

    "ORDER_VALUE",

    "SALES_DOCUMENT_NUM"

FROM TEST."SALES_HEADER" where

trunc(to_date('01-Jan-1970')+("ORDER_DATE"/86400)) >=to_date('01-Jan-2004');