SQL Subselect: Soup to Nuts

[MCWebsite.Staff]

** This thread discusses the article: SQL Subselect: Soup to Nuts **
** This thread discusses the Content article: SQL Subselect: Soup to Nuts **
0

[T.Grove]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
Delete Duplicates (does need to be run multiple times) DELETE FROM PGM_TAG/SCIPGMS C WHERE RRN(C) IN (SELECT MIN(RRN(B)) FROM PGM_TAG/SCIPGMS B WHERE B.SCIFIL||B.SCILIB||B.SCIMBR||B.SCITXT IN (SELECT A.SCIFIL||A.SCILIB||A.SCIMBR||A.SCITXT FROM PGM_TAG/SCIPGMS A GROUP BY A.SCIFIL, A.SCILIB, A.SCIMBR, A.SCITXT HAVING COUNT(A.SCIMBR) > 1) GROUP BY B.SCIFIL||B.SCILIB||B.SCIMBR||B.SCITXT) This example was used over a database of Source Library, File, Member, and Text fields of program source.

[T.Grove]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
How's this look:

---

Code

[T.Grove]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
This one was fun. It looked for Duplicate radiology account numbers, and moves them to a seperate file.

---

Code

[Guest.Visitor]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
What should become readily apparent is that it takes significantly less lines of code to do this in SQL than in a high-level language like RPG, Cobol, C, or Java. Nice article. But I have to chuckle at this statement. It dredged up some old college LISP memories (Lots of Iritating Stupid Parentheses). You could write very compact code in LISP but the code was very hard to interpret (well, my brain anyway) and debug. I will take readability and easiness to debug over "less lines of code" anyday. It is very easy to debug a CHAIN or READ in RPG, just use the debugger and step through it. But SQL does not have a line-by-line debugger. You run the whole SQL statement at once - the whole enchilada, and check the before/after snapshots of data. And the SQL might make sense to you (current programmer) but the next programmer might have to study it quite a bit to understand it and modify it. And if the SQL is embedded in RPG, you have to copy/paste the statement to STRSQL with values substituted and run it there to try to debug it. Don't get me wrong, I use SQL if it has a feature that RPG does not, like LIKE. Chris

[SoftwareTrend]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
The odd examples given in this thread are more the extreme than the rule. Most SQL code (try 95% +) written is short and simple. Complexity only comes in where either the database schema design is not rationalised correctly, the request is extreme (the one-off "let's see what the sales figures a like if we ...") or the supporting application layer needs some work to render/use the results. But you can get a lot of bang for the buck.

[T.Grove]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
SoftwareTrend is correct my examples were on the extreme side. However, that was what I wanted to show from experience: That you could write extremely complex solutions if you needed them. My second one (the duplicate radiology numbers example) would have been a lot larger RPG program and would have been pretty intensive when run, and was a once-off. Most of my embedded SQL statements are similar to SELECT * FROM FILE WHERE (CONDITION), and are pretty straightforward.

[mlafountain]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
This will also delete all of the duplicate records without the code complexity.

---

Code

[PlexPro]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
Hi Mike, See code window for my question. Regards, Rene V.

---

Code

[mlafountain]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
Oops, your right. Those AND's should be =. I've been using this method for a long time & know it works. This was just a typo because I couldn't test the statement directly.

[T.Grove]

SQL Subselect: Soup to Nuts

** This thread discusses the article: SQL Subselect: Soup to Nuts **
Mike, your version would work better. But, if I wanted to keep the most recent I would change it to the code below. There's more than one way to skin a cat, but it always takes something sharp and a cat.

---

Code