Information Systems:From Query/400 to SQL: A discussion on queries

Overview

This page follows the theme of commenting on the state of the system. In particular, queries are discussed here. Refer to the Database Query category for a more encompassing discussion of queries. This article merely pertains to observations on current practices and musings on modernization.

Notes

Calling queries from menu options

Many menu options within Extensions (OMS) actually just run Query/400 queries - some display results directly on the screen, while other queries produce spool files for printing. For example, Accounting runs a query through the following menu option in extensions:

* 21 EDI Menu
*  9 Supplier Invoices

The backend process triggered by this menu option can be described as follows:

• Menu option 9 calls EDINVSUPC (CLLE program).
• EDINVSUPC runs Query/400 query EDINVSUP1, outputting a temporary file in QTEMP called EDINVSUP1
• EDINVSUPC creates physical file EDINVSUP2 and copies results of EDINVSUP1 to EDINVSUP2, running object checks prior to operating on these files
• EDINVSUPC calls EDINVSUP (RPGLE program)
  • EDINVSUP performs logic to set values on one of the fields.
• EDINVSUPC runs a second query, EDINVSUP2, that inputs EDINVSUP2 and outputs to a spool file for the user to view.

The necessary query logic can actually be achieved through a single SQL statement:

select doc#,supp#,po#,suppinv#,invdate,duedate,cshdiscduedate1 from (
   select t03.ctidno as Doc#,t01.i1sean as Supp#,t01.i1porf as PO#,t01.i1vein as SuppInv#,t01.i1idat as InvDate,t01.i1dudt as DueDate,t01.i1dud1 as CshDiscDueDate1,
       case 
           when t01.i1dud1 <= t01.i1dudt and i1dud1 <> '0' then t01.i1dud1
           else t01.i1dudt 
       end as SortDate
   from uwdaswprdd.edoinh1 as t01 join up1480bfva.sroltai as t02 on i1vein=ctvein join up1480bfva.srolta as t03 on t02.ctrefx=t03.ctrefx 
   where ctdoty='EIN' 
   order by t01.i1dud1,t01.i1dudt
) as result order by SortDate