SQL question - one view instead of two

[Guest.Visitor]

SQL question - one view instead of two

Joe is correct. Once the view is established, I open it and close it with different "order by" statements in order to show the information in the sequence desired by the user. Steve

[Guest.Visitor]

SQL question - one view instead of two

Mark, They certainly don't have to. The same logic could be written as a stored procedure using RPG, COBOL, etc. Of course. That's the beauty of the iSeries, you can define your proven RPG programs as a stored procs. I think you missed the word 'just' in my previous message. I'm saying what if you didn't have RPG or COBOL where you can directly access your data. What if you had 100% pure java and could only access your data with JDBC SQL Select statements? I'd hate to code/debug/maintain a Bill of Material Chase using just SQL Select statements. Don't get me wrong. I certainly use SQL when RPG falls short such as using LIKE for wild card searches or complex inquiries that involve many optional search fields. Chris

[Guest.Visitor]

SQL question - one view instead of two

Os, I know SQL. I know RPG. The point about such big SQL statements is that they are difficult to debug and maintain. RPG is easy to debug, just start up STRDBG and step through it line by line. The good thing about that particular SQL statement is that it's NOT updating data, just creating a view, so debugging is less stressful. We had a contractor here. He created a big SQL view joined over 5 files, did an embedded Select count(*) against it. If the count is zero, the user is not authorized to do something. So, of course, now the users want to know which piece of data is missing from which file, so they can define it. Is there some field in the SQLCA that will tell me? If this were RPG I/O, I could see which of the 5 CHAIN's was failing and just throw up the appropriate detailed error message. Chris

[OzzieH]

SQL question - one view instead of two

Chris, That's a wide open answer as you know. The word "contractor" seems problematic to me. Who knows what this person is thinking? And why is SQL being used in this manner for the problem at hand? There is nothing in SQLCA that will help you. You might use RPG instead in this instance, or if the problem doesn't warrant RPG and SQL is required (I cannot possibly see how that could be), you might rework the statement. I've done things like this sometimes to obtain counts from various files. This isn't usable code, it's just an example of the concept.

---

Code

[Guest.Visitor]

SQL question - one view instead of two

Kevin I just wanted to give you an update on the end result of your help. The department using the program is happy with the end result. They did make one final specification change. They wanted to see all records even if the SQLVIEW1 did not have a joined record available. I changed the final join in your example to a LEFT OUTER JOIN and all is now well. Thanks. Steve

[rblumstein]

SQL question - one view instead of two

A view is a result table. Running the CREATE VIEW statement in SQL would be the same thing as refreshing a work file in RPG. As for the performance of your SQL, for SQL must always be tuned to perform well, the best tool to use is iSeries Navigator. It's visual explain is quite powerful in assisting with tuning SQL statements. I once worked with a developer who was submitting SQL statements via JDBC from Blackberry devices. His SQL was taking 3-6 seconds to execute on the iSeries. Of course, he wanted to know why the iSeries was so slow. By the time I finished analyzing, I set up one stored procedure, one index and the execution time droppped to subsecond response. It also made his code on the Blackberry smaller since he then only had to call the procedure passing a single parameter rather than creating the entire SQL statement.

[Guest.Visitor]

SQL question - one view instead of two

rblumstein wrote: > A view is a result table. Running the CREATE VIEW statement in SQL > would be the same thing as refreshing a work file in RPG. I must disagree with this. A view is much the same as a logical file, it only describes how to "view" the original table(s), it doesn't store the actual data that is being viewed and it's object size would prove this. A work file would indeed be a copy of the original data and it's object size prove this. Bill

[Guest.Visitor]

SQL question - one view instead of two

I am rather new to SQL but doing a rather involved SQL view for my third SQL program. What I would like to know is it possible to write a single view statement instead of the two that I now have (and that works). My program reads and processes SQLVIEW2. SQLVIEW1 is not used except to construct SQLVIEW2. SQL statements and result attached.

---

Code

[rblumstein]

SQL question - one view instead of two

True. A view is a named specification of a result table providing an alternative way of looking at the data.