21 Replies Latest reply: Dec 20, 2013 10:38 AM by Srikanth P RSS

    load sql select where part not functioning

      The following script is not working like it should be.

      I tried to change my loading to incremental loading.

      But the moment my script should only load the ID's bigger than my variable it goes wrong.

      He loads everything instead of only the ID's that are bigger.

      And my variable is correctly filled.

      I have the feeling he does not recognize my field : Work_Log_ID

       

      Can anybody help me pls

       

      here the script

       


       

      WORKLOG:

      LOAD
      max(num(Right("Work_Log_ID",12))) as Work_Log_ID
      From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd)
      group by "Work_Log_ID";
      // LET vMaxWorklogID = peek("Work_Log_ID", 0, WORKLOG);
      let vMaxWorklogID
      =

      FieldValue('Work_Log_ID',1);

      // SET vMaxWorklogDate = timestamp(now());
      // LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
      // LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
      // set vWorklogChangeDate = $(vWorklogUpdateDate);

      DROP

      Table WORKLOG;

      WORKLOG:

      LOAD

      "Assign_WorkLog_Flag",

      Description as WorklogDescription,

      "Detailed_Description",

      "Incident_Number" as CaseID,

      num(Right("Work_Log_ID",12)) as Work_Log_ID,

      "Work_Log_Submit_Date",

      "Work_Log_Submitter",

      "Work_Log_Type";

      SQL

      SELECT

      "Assign_WorkLog_Flag",

      Description,

      "Detailed_Description",

      "Incident_Number",

      "Work_Log_ID",

      "Work_Log_Submit_Date",

      "Work_Log_Submitter",

      "Work_Log_Type"

      FROM "HPD_WorkLog"

      Where (Work_Log_ID > '$(vMaxWorklogID)');

      // UNQUALIFY *;
      // WORKLOG:
      // Concatenate LOAD *
      // From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd)
      // where not Exists('Work_Log_ID');
      // STORE WORKLOG into Worklog.qvd(qvd);

        • Re: load sql select where part not functioning
          Colin Albert

          Use PEEK to get vMaxWorklogID

          let vMaxWorklogID = PEEK('Work_Log_ID', -1, 'WORKLOG');

            • Re: load sql select where part not functioning
              Colin Albert

              Also step through the script in Debug mode and you can view the SQL SELECT statement to see how the variable is being passed to SQL.

              • Re: load sql select where part not functioning

                ok i adapted it to peek as you mentioned but it still fetches all lines instead of just those bigger than vMaxWorklogID.

                 

                Here the adapted script

                 

                 

                WORKLOG:

                LOAD
                max(num(Right("Work_Log_ID",12))) as Work_Log_ID
                From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd)
                group by "Work_Log_ID";


                LET vMaxWorklogID
                = peek('Work_Log_ID', -1);

                // let vMaxWorklogID = FieldValue('Work_Log_ID',1);
                // SET vMaxWorklogDate = timestamp(now());
                // LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
                // LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
                // set vWorklogChangeDate = $(vWorklogUpdateDate);

                DROP

                Table WORKLOG;

                WORKLOG:

                LOAD

                "Assign_WorkLog_Flag",

                Description as WorklogDescription,

                "Detailed_Description",

                "Incident_Number" as CaseID,

                num(Right("Work_Log_ID",12)) as Work_Log_ID,

                "Work_Log_Submit_Date",

                "Work_Log_Submitter",

                "Work_Log_Type";

                SQL

                SELECT

                "Assign_WorkLog_Flag",

                Description,

                "Detailed_Description",

                "Incident_Number",

                "Work_Log_ID",

                "Work_Log_Submit_Date",

                "Work_Log_Submitter",

                "Work_Log_Type"

                FROM "HPD_WorkLog"

                Where (Work_Log_ID>'$(vMaxWorklogID)');

                // UNQUALIFY *;
                // WORKLOG:
                // Concatenate LOAD *
                // From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd)
                // where not Exists('Work_Log_ID');
                // STORE WORKLOG into Worklog.qvd(qvd);

                 

                and here the script execution progress

                 

                 

                Connected
                WORKLOG << Worklog 275,608 lines fetched
                WORKLOG << HPD_WorkLog 275,763 lines fetched


                As you can see it should only fetch 155 lines the second fetch

              • Re: load sql select where part not functioning
                Bill Markham

                Koen

                 

                Add

                     Trace Value of vMaxWorklogID is  $(vMaxWorklogID)

                 

                Just after you set the variable so when you can run the script you can confirm what value it has.

                 

                Then run the SQL manually against your data but typing in displayed the Value of vMaxWorklogID to see what that returns.

                 

                Also to be more explicit i'd replace the second  WORKLOG: with Concatenate(WORKLOG)

                 

                Best Regards,     Bill

                • Re: load sql select where part not functioning
                  Jerry Somsen

                  In your first Load statement where you are trying to find the Max value, you have a group by the same Variable in which you are trying to find the MAX value for.  Try removing the Group By Work_Log_ID and see if that helps.

                    • Re: load sql select where part not functioning

                      ok gonna comment out all the rest and just concentrate first on finding correct max value.

                       

                      worklogid example of data is WLG000000124574.

                      With num and rigth i keep 124574 which is correct in table.

                      here the script i use without group by.

                      value still wrong

                       

                      WORKLOG:

                      LOAD
                      num(Right("Work_Log_ID",12)) as Work_Log_ID
                      From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd);
                      LET vMaxWorklogID
                      = peek('Work_Log_ID', -1);

                      Trace Value of vMaxWorklogID is $(vMaxWorklogID);

                      // let vMaxWorklogID = FieldValue('Work_Log_ID',1);
                      // SET vMaxWorklogDate = timestamp(now());
                      // LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
                      // LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
                      // set vWorklogChangeDate = $(vWorklogUpdateDate);
                      // DROP Table WORKLOG;
                      //
                      //WORKLOG:
                      // LOAD
                      // "Assign_WorkLog_Flag",
                      // Description as WorklogDescription,
                      // "Detailed_Description",
                      // "Incident_Number" as CaseID,
                      // num(Right("Work_Log_ID",12)) as Work_Log_ID,
                      // "Work_Log_Submit_Date",
                      // "Work_Log_Submitter",
                      // "Work_Log_Type";
                      //
                      // SQL SELECT "Assign_WorkLog_Flag",
                      // Description,
                      // "Detailed_Description",
                      // "Incident_Number",
                      // "Work_Log_ID",
                      // "Work_Log_Submit_Date",
                      // "Work_Log_Submitter",
                      // "Work_Log_Type"
                      // FROM "HPD_WorkLog"
                      // Where (Work_Log_ID>'$(vMaxWorklogID)');

                      // UNQUALIFY *;
                      // WORKLOG:
                      // Concatenate LOAD *
                      // From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd)
                      // where not Exists('Work_Log_ID');
                      // STORE WORKLOG into Worklog.qvd(qvd);

                    • Re: load sql select where part not functioning

                      Try you it

                       

                      WORKLOG:

                      LOAD
                      max(num(Right("Work_Log_ID",12))) as Work_Log_ID
                      From [C:\Users\m999kbr\Desktop\Worklog.qvd] (qvd);
                      // group by "Work_Log_ID";
                      // LET vMaxWorklogID = peek("Work_Log_ID", 0, WORKLOG);
                      let vMaxWorklogID
                      = Peek('Work_Log_ID',0);

                      // SET vMaxWorklogDate = timestamp(now());
                      // LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
                      // LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
                      // set vWorklogChangeDate = $(vWorklogUpdateDate);

                      DROP

                      Table WORKLOG;

                      WORKLOG:

                      LOAD

                      "Assign_WorkLog_Flag",

                      Description as WorklogDescription,

                      "Detailed_Description",

                      "Incident_Number" as CaseID,

                      num(Right("Work_Log_ID",12)) as Work_Log_ID,

                      "Work_Log_Submit_Date",

                      "Work_Log_Submitter",

                      "Work_Log_Type";

                      SQL

                      SELECT

                      "Assign_WorkLog_Flag",

                      Description,

                      "Detailed_Description",

                      "Incident_Number",

                      "Work_Log_ID",

                      "Work_Log_Submit_Date",

                      "Work_Log_Submitter",

                      "Work_Log_Type"

                      FROM "HPD_WorkLog"

                      Where (Work_Log_ID > '$(vMaxWorklogID)');