9 Replies Latest reply: Jul 4, 2016 3:04 AM by Massimo Grossi RSS

    ODBC read failed

    Mouzam Basheer

      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
          Onno van Knotsenburg

          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
            Jerry Lau

            is there any error message?

            • Re: ODBC read failed
              Massimo Grossi

              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
                  Mouzam Basheer

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

                    • Re: ODBC read failed
                      Massimo Grossi

                      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

                  • Re: ODBC read failed
                    Maxim Senin

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