topic ODBC read failed in QlikView

ODBC read failed

Sat, 02 Jul 2016 07:26:14 GMT

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.

Re: ODBC read failed

oknotsen — Sat, 02 Jul 2016 09:08:56 GMT

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.

Re: ODBC read failed

laujerry — Sat, 02 Jul 2016 14:10:03 GMT

is there any error message?

Re: ODBC read failed

Sun, 03 Jul 2016 05:00:15 GMT

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

Re: ODBC read failed

oknotsen — Sun, 03 Jul 2016 14:51:36 GMT

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

Re: ODBC read failed

maxgro — Sun, 03 Jul 2016 20:36:49 GMT

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?

Re: ODBC read failed

maksim_senin — Sun, 03 Jul 2016 22:38:29 GMT

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

Re: ODBC read failed

Mon, 04 Jul 2016 04:46:14 GMT

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

Re: ODBC read failed

Digvijay_Singh — Mon, 04 Jul 2016 05:17:02 GMT

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;

Re: ODBC read failed

maxgro — Mon, 04 Jul 2016 07:04:38 GMT

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