ODBC Oracle Wire Protocol driver - ORA-01861

[#1]

hello,

i want to load data from an oracle view.

this view contains a restriction to some years.

SELECT

ID (integer),

measure (number),

Year  (date)

FROM dbo.my_view

The view works in sql-developer, but fails when i want to load into qlik-sense.

LOAD ID, measure, Year;

[my_table]:

SELECT

ID,

measure,

Year

FROM dbo.my_view

Error: QVX_UNEXPECTED_END_OF_DATA: ERROR [HY000] [Qlik][ODBC Oracle Wire Protocol driver][Oracle]ORA-01861: literal does not match format string

What's going on there?

The log file finds the columns correctly - then the error is raised - execution failed.

What's wrong in my setup`?

Thx! in advance.

[Anil_Babu_Samineni]

Can you show View?

[#3]

CREATE OR REPLACE FORCE VIEW myview

(

   ID,

   Measure_1

   START_DATE,

   MONTH,

   YEAR

)

AS

   SELECT ID,

          CASE

             WHEN ...

             THEN

                ... etc

             END Measure_1,

          tbl_one.START_DATE,

          TRUNC (tbl_one.START_DATE, 'MM') month,

          TRUNC (tbl_one.START_DATE, 'YYYY') year

     FROM tbl_1,

       tbl_2,

        tbl_3

    WHERE   several joins...

       AND tbl_one.START_DATE >= '01.01.2016'

          AND tbl_one.START_DATE <= '01.01.2026';

Output:

ID | Measure_1 | Start_Date | Month | Year

1 | 10000 | 01/01/2016 | 01/01/2016 | 01/01/2016

Main in script editor:

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

I also tried to changed this to DD/MM/YYYY - but no effect.

Error message still available....Any ideas?

[Anil_Babu_Samineni]

Try like below

Load ID , Measure_1 , Start_Date , Month , Year;

SELECT * From tbl_1;

[#5]

I found that QS has problems with the WHERE-Clause of the view.

WHERE   several joins...

       AND tbl_one.START_DATE >= '01.01.2016'

          AND tbl_one.START_DATE <= '01.01.2026';

When commenting out, the error disappears....

Why does QS interpret my view in a different way ?

[Anil_Babu_Samineni]

WHERE   several joins...

       AND tbl_one.START_DATE >= '01.01.2016'

          AND tbl_one.START_DATE <= '01.01.2026';

From here it will return >= '01.01.2016' <= '01.01.2016' -- Only one date it will return due to same arbitrary

You can use this

WHERE   several joins...

       AND tbl_one.START_DATE = '01.01.2016;

[#7]

Well, thanks - this was a typo.

Now I restricted the View to the start_date = '01.01.2016', the views shows all rows in sql-editor, which is nice.

But when I try to load it in Qlik Sense via ODBC zero rows are returned.

Where is my data lost ?

[Anil_Babu_Samineni]

Glad it worked, I would highly request to mark Helpful. BEcause, next time who land up the same issue they will direct look on it. Anyway, How do you load in Qliksense. I don't have that much offer in QS but as i assume type of loads are similar. I may offer you if you provide more / Just copy the code and use it in QS.