SQL question - one view instead of two

[K.Forsythe]

So there is no need to build the first view ahead of time, you can define it on the fly, as you create sqlview2.

---

Code

[Guest.Visitor]

Since I am so new to SQL I am not certain why it did not work. I cut and pasted your example into the interactive SQL screen and it fails on the fourth line from the bottom. The statement reads "as SQLVIEW1 on (c6aenb=aenb". The interactive SQL return is complaining about aenb in the "c6aenb=aenb". I feel that it would also complain about the references to dccd and cvnb as well. In my example, when I created view SQLVIEW1, fields aenb, dccd, cvnb, and swdate are all created as a part of that first view. I did not see that in your example and I am not certain how to work it into your code. The other half of this is that my two SQL statements are already imbedded into an RPG program. The statements are both run on the fly and create the two views only when needed by the users. I was looking to see if I could eliminate one of the views. Steve

[K.Forsythe]

Your first view can be eliminated by adding the select statement you used to create it, directly into the second create view. It seems I forgot to put "SW" in front of a few field names, try the updated code sample. The key part of the statement is included here as well: join (select swaenb, swdccd, swcvnb, min(swdate) as swdate from domsw100 where swdate <> 0 group by swaenb, swdccd, swcvnb) as SQLVIEW1 on (c6aenb=SWaenb and c6dccd=SWdccd and c6cvnb=SWcvnb)) This illustrates joining to a SELECT statement rather than a permanent file/view. I don't understand your comment about "the other half of my problem". My example shows you how to build view2, without building view1. Isn't that what you are asking for? In general - even if there is a typo in the code sample - the solution is to replace your join to SQLVIEW1, with a join to a select statement that creates the same data as SQLVIEW1. You should be able to get the idea from my example. Good Luck, Kevin

---

Code

[Guest.Visitor]

Kevin Yes - your example did exactly what I wanted - it eliminated one of the two views I was creating in my RPG program. I will study your example to further my understanding of SQL. We do have an older edition of SQL/400 Developer's Guide by Paul Conti and Mike Cravitz that gets me by most issues but sometimes I need to look for help beyond the book. Thanks. Steve

[K.Forsythe]

It was quite a revelation to me, when I first learned that I could replace a file in the From clause with a select statement that effectively builds a temporary table "on-the-fly". That one feature adds an incredible amount of flexibility to the language. Enjoy the study, and feel free to email me if I can help with anything else, Kevin

[Guest.Visitor]

Ok, I gotta ask. How would everyone rate the maintainability of that SQL statement, for the next programmer? I'd give it 3 out of 10. It's quite complex. But if that's what is required, I guess that's what you go with. Debugging that statement must have been fun too. Do systems with just SQL access have lots of statements like this? Say, Java using JDBC. Isn't RPG great???? Chris

[Guest.Visitor]

Do systems with just SQL access have lots of statements like this? Say, Java using JDBC. They certainly don't have to. The same logic could be written as a stored procedure using RPG, COBOL, etc.

[K.Forsythe]

Yeah it was ugly.... Documentation would help some. But just for fun - try writing the rpg PROGRAM that would do the same thing - how many lines of code? How easy to write? How easy to maintain - without documentation? But overall I agree - complex SQL statements are a bit ugly and more difficult to decipher - so you need to document them! Kevin

[OzzieH]

That statement is not complex by any means. It is just barely scratching the surface of what SQL can do ("Can" doesn't mean "Should" of course). To an SQL person, it is quite maintainable, but YES, it should be documented for sure.

[J.Pluta]

Unless I'm missing how this relatively simple query works ("simple" is not meant to imply that the syntax isn't complicated and a little difficult to read), what it's doing is using MBC6REP as its primary file, then getting fields from two other files (MBCKREP and MBCJCPP) with a key of three fields: c6aenb, c6dccd and c6cvnb. Okay, that's a READ on the primary and CHAIN on the other two files. The next bit is simply getting the lowest non-zero date value of the DOMSW100. That's a SETGT/READ. In addition, it's filtering on two fields: ckczcd = 'CH' and ckacp8 = ' '. So, my RPG code would be 11 lines (see code). There are some issues; I have to have all the appropriate logicals defined, but I need that anyway for SQL to perform well. And my particular code below assumes data exists; you'd need to put in a little bit of logic to handle your exception conditions. Of course, you'd have to do that in SQL as well using OUTER JOINs and default values (or COALESCE). If I needed to skip situations where data doesn't exist (the default for SQL), I'd have to slightly modify the loop and stick in some ITERs. This is one place where a single-line "if" construct would be REALLY nice (if %eof(MBCJPP) iter. Are you listening IBM? I also wonder about the performance. I'm not sure of the ramifications of doing a subselect with an aggregate function such as minimum in conjunction with a filter. It's not entirely clear to me whether the temporary table (including aggregation) is built first and then the filtering is done, or whether a row that fails the filter is not processed. In the best world the SQE would see that the filter doesn't include fields from the aggregate and so process it first, but I'm not certain. Also, my SETGT/READ might be significantly faster than the aggregate if it's not smart enough to realize there can be no negative dates and so it doesn't have to test every record. Joe

---

Code