1 Reply Latest reply: May 30, 2016 7:17 AM by Simone Trabattoni RSS

    Data load editor - From ODBC where date is after x

    Jeffrey Fokker

      Hi all,

       

      I am new to this platform and hope to gain and share a lot of knowledge related to QS overtime

       

      Now I'm facing a challenge when it comes to create a piece of code where I want to load some data via a ODBC connection where we will define a WHERE statement when the date should be after 1.1.2016 00:00:00, but it seems like that it stuck at SQL select and actually not knowing what's wrong since it seems that it worked for other people. The code looks as follows;

       

      LIB CONNECT TO 'ODBClive (accdom_jfo010)';

      left join (W1_first_counted_materials)

      LOAD "VM_PROD_ITEM" as Material,

          "VM_PROD_DESCR",

          "VM_PROD_LAST_PROD_CC" as "Last counted WCS";

      SQL SELECT "VM_PROD_ITEM",

          "VM_PROD_DESCR",

          "VM_PROD_LAST_PROD_CC"

      FROM "CUSTOM"."VW_PRODUCT" WHERE (Date(Date#(VM_PROD_LAST_PROD_CC,'M-D-YYYY hh:mm:ss')) > Date(Date#('1-1-2016 00:00:00', 'M-D-YYYY hh:mm:ss')));

       

      Does anyone of you knows what I should change??

       

      Thanks a lot for your help guys!

       

      Kind regards,

      Jeffrey !

        • Re: Data load editor - From ODBC where date is after x
          Simone Trabattoni

          Hi Jeffrey!

           

          you could try this a bit out of the box:

           

          LIB CONNECT TO 'ODBClive (accdom_jfo010)';

           

          table.name:

          load

          "VM_PROD_ITEM" as Material

          ,"VM_PROD_DESCR"

          , "VM_PROD_LAST_PROD_CC" as "Last counted WCS"

          ;

          sql select

          "VM_PROD_ITEM"

          ,"VM_PROD_DESCR"

          ,"VM_PROD_LAST_PROD_CC"

          from "CUSTOM"."VW_PRODUCT"

          where

          year(VM_PROD_LAST_PROD_CC)>2016 and

          month(VM_PROD_LAST_PROD_CC)>1 and

          day(VM_PROD_LAST_PROD_CC)>1 and

          DATEPART(hour,VM_PROD_LAST_PROD_CC)>0 and

          DATEPART(mi,VM_PROD_LAST_PROD_CC)>0 and

          DATEPART(ss,VM_PROD_LAST_PROD_CC>0 and

          // if needed

          //DATEPART(millisecond,VM_PROD_LAST_PROD_CC)>0

          ;

           

          change > if you want >= and you should look at performance also!

          Hoping to be helpful!