1 Reply Latest reply: Aug 6, 2009 4:17 PM by robbies RSS

    Need help integrating SQL statement into QV

      I am not very familiar with SQL, but have been given a query that was designed here by some developers and am trying to build a document in QV. I need to integrate the following SQL's into my QV Script, either as a SQL Select or rewritten in the Load statement. Here is what I have:

       

      LOAD
      "W_GPA_ID AS [IHR GPA ID],
      ACTIONDTTM,
      "W_GPA_STATUS" AS [IHR GPA Status];
      SQL SELECT * FROM SYSADM."PS_W_GPA_STS_TBL";


      Here are the SQL's that I need to integrate somehow:

       

      (SELECT MAX(actiondttm)
      FROM sysadm.ps_w_gpa_sts_tbl
      WHERE w_gpa_id = A.w_gpa_id
      AND w_start_oprid = d.w_approver1
      AND w_gpa_status = 'IHV' ) "Approval 1 Date"


      and also

       

      (SELECT MAX(actiondttm)
      FROM sysadm.ps_w_gpa_sts_tbl
      WHERE w_gpa_id = A.w_gpa_id
      AND w_start_oprid = d.w_approver2
      AND w_gpa_status = 'CO' ) "Approval 2 Date"


      'A' refers to PS_W_GPA_STAT_CHG

      'D' refers to PS_W_PA_APPROVER

      I hope this makes sense! I've been puzzling over this since yesterday and keep getting various Oracle error messages each time I reload (everything from "SQL Command not Ended Correctly", "Invalid Table Name", and "Missing Right Parentheses") depending on my formatting.

       



        • Need help integrating SQL statement into QV
          robbies

           

          Hi Jennifer, maybe something like this.

          Don't know for sure I understood your question. ;-)

           

          I'm not sure but are these table names?

          'A' refers to PS_W_GPA_STAT_CHG

          'D' refers to PS_W_PA_APPROVER

           



          Approval1:
          Load
          "W_GPA_ID",
          Max(ACTIONDTTM) As "Approval 1 Date"
          Where "W_GPA_STATUS" = 'IHV'
          Group by "W_GPA_ID";
          SQL Select *
          From SYSADM."PS_W_GPA_STS_TBL";


          Approval2:
          Load
          "W_GPA_ID",
          Max(ACTIONDTTM) As "Approval 2 Date"
          Where "W_GPA_STATUS" = 'CO'
          Group by "W_GPA_ID";
          SQL Select *
          From SYSADM."PS_W_GPA_STS_TBL";