ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL help please

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL help please

    I currently have this statement:

    Code:
    select max(rpjob) as Job, max(rpseq) as Seq, max(rpstat) as RPStatus, max(date(rpts)) as RPDate
    from myLib.myFile
    group by rpjob
    But this isn't exactly what I want. What I really want is the fields that are associated with the most recent sequence number (RPSEQ) for each RPJOB. So the max value of RPSEQ for each RPJOB, and then all the fields that are associated with that record. Instead what I'm currently getting is the max RPSEQ, the max RPSTAT, and the max date, but these may come from different records for the same RPJOB.

    Thanks in advance!

  • #2
    Not sure if I understand correctly what you want.
    If you want the MAX(RPSEP) for each RPJOB, you first have to determine this informat in a Common Table Expression (CTE) or a nested Sub-Select.
    This result just be joined with the Table itself.
    Something linke this
    Code:
    With x as (Select RPJOB as xRPJOB, Max(RPSEQ) as xRPSEQ
                 From MyLib.MyFile
                 Group By RPJOB)
    Select a.*
      From x Join MyLib.MyFile a on RPJOB = xRPJOB and RPSEQ = xRPSEQ;
    Birgitta

    Comment


    • #3
      Birgitta, yes, I think you understood correctly what I'm after. To clarify, if I had the following records in my file, these circled ones are the ones I would want:

      Click image for larger version

Name:	LatestJobSeq.JPG
Views:	231
Size:	69.8 KB
ID:	154721

      I see that your code gives me that, using a CTE. What I didn't mention previously is that my code above was already a CTE as part of a larger SQL statement... I just need the latest activity record (based on Seq) for each job which I then use in my larger statement.

      I don't think I can nest CTE's, can I...?

      Comment


      • #4
        You can not nest CTEs but you can use a CTE in another CTE!
        A CTE is nothing else than a temporary table/view that is only valid for the current SELECT statement. But you can define as much CTEs as you want

        Comment


        • #5
          ...you can use a CTE in another CTE!
          This sounds like nesting to me...? I just tried your code above (which works fine by itself) within another CTE and it didn't like that second WITH. Here's what I tried:

          Code:
          With LastActivity as (
            With x as (Select RPJOB as xRPJOB, Max(RPSEQ) as xRPSEQ
                       From myLib.jobactivity
                       Group By RPJOB)
            Select a.*
              From x Join myLib.jobactivity a on RPJOB = xRPJOB and RPSEQ = xRPSEQ
          )
          Select j.rpjob as Job, j.rpcust as Customer, a.rpseq as Seq, a.rpstat as RPStatus, date(a.rpts) as RPDate
          from myLib.jobs j
          left join LastActivity on j.rpjob = a.rpjob
          I think maybe I'm not imagining correctly what you're suggesting. You can probably tell from this non-working code what I'm trying to do... I ultimately want to get some fields from each job in my JOBS file, and just the latest activity (seq, status, and date) for each of those jobs.

          Do you have an example of using a CTE in another CTE as you suggest? Thanks!

          Comment


          • #6
            Birgitta means you can define CTE's next to each other, but then use them inside each other. E.g.:

            Code:
            with x as (select * from def where a = b).
                 y as (select uvw, ghi from x inner join abc on c=d)
            select * from y where a=h
            See how CTE Y references CTE X?

            I think this would be how your code works with that:
            Code:
              With x as (Select RPJOB as xRPJOB, Max(RPSEQ) as xRPSEQ
                         From myLib.jobactivity
                         Group By RPJOB),
            LastActivity as (
              Select a.*
                From x Join myLib.jobactivity a on RPJOB = xRPJOB and RPSEQ = xRPSEQ
            )
            Select j.rpjob as Job, j.rpcust as Customer, a.rpseq as Seq, a.rpstat as RPStatus, date(a.rpts) as RPDate
            from myLib.jobs j
            left join LastActivity on j.rpjob = a.rpjob

            Comment


            • #7
              Thank you for explaining that and showing it. My program now works and produces the correct data. Utilizing multiple CTE's will be useful in other programs too, going forward.

              From your code above, I just had to adjust slightly to reference the fields as LastActivity.rpseq instead of a.rpseq, LastActivity.rpstat instead of a.rpstat, etc. I was thinking they should be a.fieldname because that CTE has "Select a.*", but the fields needed to be qualified with LastActivity, not a.

              Thanks Birgitta and Vectorspace for the info and help!

              Comment


              • #8
                You can qualify the fields from the LastActivity CTE with an a. by adding the a to the last line of the statement:

                left join LastActivity a on j.rpjob = a.rpjob

                Comment

                Working...
                X