Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

9 Replies
oknotsen
Master III
Master III

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
Creator
Creator

is there any error message?

Not applicable
Author

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

oknotsen
Master III
Master III

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!
maxgro
MVP
MVP

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?

maksim_senin
Partner - Creator III
Partner - Creator III

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
Author

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

Digvijay_Singh

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;

maxgro
MVP
MVP

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