topic Oracle SQL query works in SQL Developer but not QlikView in QlikView

Oracle SQL query works in SQL Developer but not QlikView

sifatnabil — Mon, 05 Oct 2015 03:21:13 GMT

Hi all,

On Oracle SQL Developer, this query runs successfully:

select * from table

where msg_date_time BETWEEN to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 05:00:00', 'DD-MON-YY hh24:mi:ss')

AND to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 18:00:00', 'DD-MON-YY hh24:mi:ss')

But when I put this in the QlikView script, I get a "Not a valid month" error. Any ideas on why this would happen?

Re: Oracle SQL query works in SQL Developer but not QlikView

SreeniJD — Mon, 05 Oct 2015 03:35:32 GMT

Hi There,

QV supports its native SQL which is almost similar to SQL. To change a date format, you can use DATE# function instead of to_date .. Also To_timestamp should be replaced with TIMESTAMP and BETWEEN shuldbe replaced with ~ INTERVALMATCH...

Let me know if you want this query in QV format.

HTH,

Sreeni

Re: Oracle SQL query works in SQL Developer but not QlikView

sifatnabil — Mon, 05 Oct 2015 03:38:09 GMT

Hi, I want to use this query in the SQL SELECT portion of the Qlikview script, i.e.

testtable:

load *;

SQL SELECT * FROM TABLE

where msg_date_time BETWEEN to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 05:00:00', 'DD-MON-YY hh24:mi:ss')

AND to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 18:00:00', 'DD-MON-YY hh24:mi:ss')

It's the bolded portion of the query that works on SQL Developer but not in the SQL SELECT statement of the QV script.

Re: Oracle SQL query works in SQL Developer but not QlikView

priyarane — Mon, 05 Oct 2015 03:51:51 GMT

try to change from MON to MMM

Re: Oracle SQL query works in SQL Developer but not QlikView

settu_periasamy — Mon, 05 Oct 2015 03:53:56 GMT

Hi,

Have you tried without to_date? Like

to_timestamp('23-SEP-15 05:00:00', 'DD-MON-YY HH24:MI:SS')

Re: Oracle SQL query works in SQL Developer but not QlikView

SreeniJD — Mon, 05 Oct 2015 03:56:25 GMT

try this..

TIMESTAMP#('23-SEP-15','DD-MMM-YY HH:MM:SS')

Re: Oracle SQL query works in SQL Developer but not QlikView

sasiparupudi1 — Mon, 05 Oct 2015 08:46:22 GMT

I think it is something to do with the timestamp format..

you shall have to execute the following sql before you can use your select. In SQL developer it is already set and therefore the sql works striaghtaway.

alter session set NLS_DATE_FORMAT='yyyy-MON-dd hh24:mi:ss';

hth

Sasi

Re: Oracle SQL query works in SQL Developer but not QlikView

sifatnabil — Mon, 05 Oct 2015 10:07:04 GMT

Thanks for all your replies guys. It looks like to_date doesn't work here. to_char works in place of it though. Using alter session set NLS_DATE_FORMAT='yyyy-MON-dd hh24:mi:ss'; also doesn't work. Essentially, date conversions inside to_timestamp() don't seem to work in QV.

So the query that works is:

where msg_date_time BETWEEN to_timestamp('23/09/2015' || ' 05:00:00', 'dd/mm/yyyy hh24:mi:ss')

AND to_timestamp('23/09/2015' || ' 18:00:00', 'dd/mm/yyyy hh24:mi:ss')

Or if using sysdate, use to_char:

where msg_date_time BETWEEN to_timestamp(to_char(sysdate,'dd/mm/yyyy') || ' 05:00:00', 'dd/mm/yyyy hh24:mi:ss')

AND to_timestamp(to_char(sysdate,'dd/mm/yyyy') || ' 18:00:00', 'dd/mm/yyyy hh24:mi:ss')