Announcement

Announcement Module
Collapse
No announcement yet.

Update with "order by" and "fetch first 1 rows only"

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Update with "order by" and "fetch first 1 rows only"

    I did not get a chance to test the whole thing but try this... update test1 a set a.zona12 = (select coalesce(b.zona22,a.zona12) from test2 b where a.zona11 = b.zona21 AND b.zona23 = (select Max(c.zona23) from test2 c where a.zona21 = c.zona21 and b.zona22=c.zona22)) Let me know how that works out...

  • #2
    Update with "order by" and "fetch first 1 rows only"

    "Result of SELECT more than one row"...

    Comment


    • #3
      Update with "order by" and "fetch first 1 rows only"

      try... update test1 a set a.zona12 = (select coalesce(b.zona22,a.zona12) from test2 b where a.zona11 = b.zona21 AND b.zona23 = (select Max(c.zona23) from test2 c where a.zona21 = c.zona21 and b.zona22=c.zona22) fetch first 1 rows only) or update test1 a set a.zona12 = (select coalesce(min(b.zona22) ,a.zona12) from test2 b where a.zona11 = b.zona21 AND b.zona23 = (select Max(c.zona23) from test2 c where a.zona21 = c.zona21 and b.zona22=c.zona22))

      Comment


      • #4
        Update with "order by" and "fetch first 1 rows only"

        thank you - i tried them both. the first one says "Keyword FETCH not expected" (same problem as with my query), and the second one doesn't update the file as I want. i'm not good in explaining what i want, so i just wrote a little program that does the job (code below) - but I still want to use SQL instead
        Code

        Comment


        • #5
          Update with "order by" and "fetch first 1 rows only"

          I'll take a look at this when I have more time to recreate your file structure and get you a tested syntax. Sorry for not having it right (yet).

          Comment


          • #6
            Update with "order by" and "fetch first 1 rows only"

            thank you for your time - of course it's not an urgent matter. My real problem is that I'm not allowed to put RPG code in production (I only use it to make tools in the development environment), as the "official" programming language of the company where I am in this moment is Cobol. And I HATE writing Cobol code after getting used to RPG )

            Comment


            • #7
              Update with "order by" and "fetch first 1 rows only"

              OK not that I am biased or anything.... But COBOL? I admit 15 years ago RPG was not THAT much better than COBOL. But RPG has evolved since then. And COBOL... What exactly is the draw?

              Comment


              • #8
                Update with "order by" and "fetch first 1 rows only"

                this box uses an application written a long time ago (maybe a little less than 15 years ) and since then they bring only consultants that are familiar with Cobol. so I'm the only RPG programmer among 10 Cobol developers that don't understand RPG maybe I should just leave

                Comment


                • #9
                  Update with "order by" and "fetch first 1 rows only"

                  Comment


                  • #10
                    Update with "order by" and "fetch first 1 rows only"

                    I agree with the principle, but Cobol doesn't give me much appetite ) Otherwise, I didn't built well my example, sorry for that (it's 23:35 here in France...). The unique key on the second file is ZONE21 & ZONE23. I post below the complete and correct info (2 files & 1 program), waiting for the replacement of these 16 lignes of code with one SQL statement, rather than 100 lines of Cobol code...
                    Code

                    Comment


                    • #11
                      Update with "order by" and "fetch first 1 rows only"

                      Try... update test1 a set a.zona12 = (select coalesce(Max(b.zona22),a.zona12) from test2 b where a.zona11 = b.zona21 and b.zona23 = (select max(c.zona23) from Test2 C Where B.zona21 = C.zona21)) It worked for me with the sample data I had in the files.

                      Comment


                      • #12
                        Update with "order by" and "fetch first 1 rows only"

                        thank you, it does what I needed! I am going to test this at work on monday with the real files (pretty large ones) to see how much resources it takes.

                        Comment


                        • #13
                          Update with "order by" and "fetch first 1 rows only"

                          This was a hard one and I tried building it in pieces: 1) SELECT MAX(C.ZONA23) FROM TEST2 AS C GROUP BY C.ZONA21 2) SELECT MIN(B.ZONA22) AS MINZONA22 FROM TEST2 AS B WHERE (B.ZONA23 = (SELECT MAX(C.ZONA23) AS MAXZONA23 FROM TEST2 AS C WHERE (C.ZONA21 = B.ZONA21) GROUP BY C.ZONA21)) GROUP BY B.ZONA21 3) UPDATE TEST1 SET ZONA12 = (SELECT MIN(B.ZONA22) FROM TEST2 AS B WHERE ((B.ZONA21 = ZONA11) AND (B.ZONA23 = (SELECT MAX(C.ZONA23) FROM TEST2 AS C WHERE (C.ZONA21 = B.ZONA21) GROUP BY C.ZONA21))) GROUP BY B.ZONA21) 4) UPDATE TEST1 SET ZONA12 = COALESCE((SELECT MIN(B.ZONA22) FROM TEST2 AS B WHERE ((B.ZONA21 = ZONA11) AND (B.ZONA23 = (SELECT MAX(C.ZONA23) FROM TEST2 AS C WHERE (C.ZONA21 = B.ZONA21) GROUP BY C.ZONA21))) GROUP BY B.ZONA21), NULL)
                          Code

                          Comment


                          • #14
                            Update with "order by" and "fetch first 1 rows only"

                            thank you, BXSGolfer! obviously, the solution is not at fingertips by SQL... at least not for me.

                            Comment


                            • #15
                              Update with "order by" and "fetch first 1 rows only"

                              Hi everybody, Here's an example of what I want to do: I have 2 files, TEST1 and TEST2 (with the structures pasted below) and I need to update the column ZONA12 of the first file with the value of the zone ZONA22 of the second file that corresponds to the maximum value of the zone ZONA23 of the second file. I tried this and SQL doesn't accept the syntax: update test1 a set a.zona12 = coalesce( (select b.zona22 from test2 b where a.zona11 = b.zona21 order by b.zona23 desc fetch first 1 rows only ), a.zona12 ) Can this somehow be done by one SQL command, or do I need to write a program only for this little update? Thanks in advance.
                              Code

                              Comment

                              Working...
                              X