Qlik Community

QlikView Documents

Documents for QlikView related information.

Some nuances working with Oracle

mov
Esteemed Contributor III

Some nuances working with Oracle

1. Using preceding load and aliases.

This script will fail in Oracle:
LOAD
ID,
Amount;
SQL SELECT
ID,
AMT as Amount
FROM MY_ORACLE_TABLE;
The reason - alias Amount in SQL actually will be created in upper case - AMOUNT, and the LOAD doesn't find the field Amount.
There are two ways to fix it:

a) Use alias in the LOAD part:
LOAD
ID,
AMT as Amount;
SQL SELECT
ID,
AMT
FROM MY_ORACLE_TABLE;

b) Use quotes:
LOAD
ID,
Amount;
SQL SELECT
ID,
AMT as "Amount"
FROM MY_ORACLE_TABLE;

2. Date format.

Sometimes we want to limit data load based on a date:
LOAD
ID,
AMT as Amount;
SQL SELECT
ID,
AMT
FROM MY_ORACLE_TABLE
WHERE DATEFIELD <= '$(StartDate)';
This will work fine in SQL Server, but not in Oracle. We have to format the date explicitly, for example:

LOAD
ID,
AMT as Amount;
SQL SELECT
ID,
AMT
FROM MY_ORACLE_TABLE
WHERE DATE_KEY <= TO_DATE('$(StartDate)', 'MM/DD/YYYY HH24:MISmiley FrustratedS');

Certainly it helps to have the StartDate variable in the same format, here it will be 'MM/DD/YYYY hh:mm:ss'.


Enjoy Oracling,
Michael S

Labels (1)
Comments
Not applicable
Very Useful information
MVP
MVP

This is standard Oracle behavior. Why you would need mixed case field names?

richard_abmsyst
New Contributor

Thanks Michael - fixed up my Oracle date comparison issue nicely.

Version history
Revision #:
1 of 1
Last update:
‎12-10-2009 03:01 AM
Updated by:
mov