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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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]

SQL question - one view instead of two

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

[Guest.Visitor]

SQL question - one view instead of two

Since many others have joined this thread its time for a little background so you may understand why this view was necessary. It may also allow you to point out other ways to accomplish what the SQL statement did for me and eliminate the need for the SQL view. The first file is an Infor (formally MAPICS) order header file. The next two files MBCJCPP and MBCKREP are also MAPICS files - they are an order header extension file and an order hold file respecively. The last file (DOMSW100) is part of an in-house scheduling system with one record per order detail line. The users requirements was a screen to manage all orders on credit hold. The screen had to provide default sorting by order number but also provide for sorting by customer account and by earliest schedule date. The display was one subfile record per order. Since a many to one relationship exists between orders and order detail lines the MIN(SWDATE) function was needed to get just one record from the DOMSW100 table. Records with a zero value (unscheduled) were dropped by using the "where swdate <> 0" statement. The need to provide this in a subfile, and sort on that scheduled date, dictated that the information either be gathered into a work file before writting it to the screen or using the SQL statement. I choose to use the SQL statement because the SQL view is based on the actually data. Gathering the data into a work file means that the work file must be constantly "refreshed" to make certain that the information shown to the user is up to date. Startup for the program takes around 3 seconds on our i5. Switching the sorting method from one sort to another takes a similar amount of time (2 to 3 seconds) the first time the user chooses a view that has not yet been used before. Once a sort view has been used, returing to that view is very fast. There are 5000 records in MBC6REP, 4300 records in MBCJCPP, 4000 records in MBCKREP (320 of them meet the selection criteria of "ckczcd = 'CH' and ckacp8 = ' '"), and 15,000+ records in file DOMSW100. Steve

[J.Pluta]

SQL question - one view instead of two

Makes perfect sense to me, Steve. The only thing I'd consider is the fact that since you're only selecting a few hundred records you might consider just creating a nice array of data structures and using QSORT to sort the data. If the user changes the sort criteria, resort the array and reload the subfile. If they hit "refresh", you can reload the array from disk. Of course, you can load the array using either SQL or native I/O! But if it's truly only about 300 records, my guess is that with the native I/O technique even the initial load will be subsecond, especially if you put a logical over the MBCKREP file selecting on CKCZCD and CKACP8 and use that as your primary file. And reorders will be nearly instantaneous. Joe

[R.Daugherty]

SQL question - one view instead of two

I'm totally missing something here. I didn't see an ORDER statement in your SQL, so what do you mean by you are using the SQL to sort on scheduled date? If you are referring to the View, reading by the logical provides the same sort without using a workfile, but like I say, I may be missing something here. rd

[J.Pluta]

SQL question - one view instead of two

I think he's writing yet another SQL statement that reads the view with an ORDER BY clause, Ralph. Joe

[R.Daugherty]

SQL question - one view instead of two

Oh, ok Joe. Thanks.