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

[K.Forsythe]

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...

[Guest.Visitor]

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

[K.Forsythe]

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))

[Guest.Visitor]

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

[K.Forsythe]

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).

[Guest.Visitor]

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 )

[K.Forsythe]

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?

[Guest.Visitor]

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

[K.Forsythe]

[Guest.Visitor]

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