Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

ODBC read failed

Hello Developers,

    My this Query Run fine on my TOAD but not work properly on QlikView .

SELECT  TO_TimeStamp(A.TIME_OUT, 'DD-MM-YY hh24:mi') - TO_TimeStamp(A.TIMING, 'DD-MM-YY hh24:mi') AS DIFF_HOURS

FROM            EMPLOYEE E INNER JOIN

                         ATTENDANCE_DETAIL A ON E.BRANCH_ACC_ID = A.BRANCH_ACC_ID

Thanks in advance.

Tags (2)
9 Replies
oknotsen
Honored Contributor III

Re: ODBC read failed

Even if it works perfectly on your database, I would suggest focusing on loading the fields into Qlik and do that calculation you are currently doing in your select in the PrecedingLoad in Qlik.

So, start with removing the calculation and see if it still does not work.

If it still does not work, load both tables in Qlik and do both the join and the calculation in Qlik.

May you live in interesting times!
laujerry
Contributor

Re: ODBC read failed

is there any error message?

Not applicable

Re: ODBC read failed

this is an error Message ..''' ODBC Read Failed '''   #Jerry

oknotsen
Honored Contributor III

Re: ODBC read failed

You are 100% sure there is a connection string for the database these tables are in before this statement?

May you live in interesting times!
MVP
MVP

Re: ODBC read failed

I would start in Qlik with a simple query, like this

SELECT  A.TIME_OUT, A.TIMING

FROM            ATTENDANCE_DETAIL A

Do you get the error?

Then add the 2nd table, then the to_timestamp function etc...

Do you get the error?

maxim_senin
Contributor III

Re: ODBC read failed

Hi Mouzam,

It's better to provide more details since it's not quite clear whether you app connects to ODBC source in proper manner.

Personally I faced with situations when some SQL statements do not work via ODBC whilst they normally work via direct connection to DB (it was Oracle). So I suggest to simplify the statement first in order to make sure your connection is esteblished normally, then you can add function by function in order to understand which one fails.

Best regards,

Maxim

Not applicable

Re: ODBC read failed

Error is     To_timestamp()    without this this function Query work properly...

Digvijay_Singh
Honored Contributor III

Re: ODBC read failed

Try this script one by one, before that ensure connection string is working properly -

//Load Employee table

Source:

Load Timestamp#(TIMEOUT,'DD-MM-YY hh:mm') as TIMEOUT,

  Timestamp#(TIMING,'DD-MM-YY hh:mm') as TIMING,

  BRANCH_ACC_ID;

SQL Select

  TIME_OUT,

  TIMING,

  BRANCH_ACC_ID

From

  EMPLOYEE;

//Inner join ATTENDANCE_DETAIL with Employee table( Employee table is loaded into Qlik table 'Source')

// Check the field names required to be loaded, if more than one fields have same name, records will be loaded

//only if all fields are matching in both the tables as we are using Inner join here.

Inner Join(Source)

Load Timestamp#(TIMEOUT,'DD-MM-YY hh:mm') as TIMEOUT,

  Timestamp#(TIMING,'DD-MM-YY hh:mm') as TIMING,

  BRANCH_ACC_ID;

SQL Select

  TIME_OUT,

  TIMING,

  BRANCH_ACC_ID

From

  ATTENDANCE_DETAIL;

//Calculating difference hours. 'Final' will be the output table having desired result. Check if * needs

//to be replaced with specific fields to avoid unused fields.

Final:

Load *,

  Interval(TIMEOUT-TIMING,'D hh:mm') as DIFF_HOURS

Resident Source;

Drop table Source;

MVP
MVP

Re: ODBC read failed

you can use a preceding load

read the fields from Oracle and then calculate the difference in Qlik using Qlik functions (bold) timestamp# and (maybe) timestamp

Replace ???????? with the format code of yur data

Load

     timestamp#(TIMEOUT, '???????') - timestamp#(TIMEOUT, '???????')

;

SELECT  A.TIME_OUT, A.TIMING

FROM            EMPLOYEE E INNER JOIN

                         ATTENDANCE_DETAIL A ON E.BRANCH_ACC_ID = A.BRANCH_ACC_ID

Community Browser