SQL Views

[Denny]

I'm an old green screen RPG guy who just recently started using SQL. Here is an interesting usage of an SQL view. A view is inplemented as an AS/400 logical, but you can do things with SQL that are not possible with DDS.

This example is a bit ugly, but it demonstrates using SQL to reformat fields in the view. You can create or derive fields in the view that don't exist in the physical file. This physical file in the PRMS ERP package breaks the G/L number up into 4 separate fields. It also create all amounts as positive values and your have to look at the field GCOCE to see if it contains a D or a C for Debit or Credit values. This is not easy to use in a QUERY. This SQL View/Logical concatinates the 4 G/L numbers into one field and converts the amount to have a negative value when GCODE is C. The first set of field names simply list the fields to be picked up from the physical and the AS SELECT fields are the logical fields created.

CREATE VIEW RMSFILES/GLIFL999 
(ACTIV, CMPNO, PLTNO, GLAC#, IFTDT, USRID, IFPDT, JENUM, GLCMT, SOURC, TRTYP, GCODE, GLAMT, REFR1, REFR2, REFR3, IFOR1, IFOR2, IFCUR, IFFAM, IFXRT, USRF1, USRF2, IFPYR, IFPRD, IFFCC, IFXMD, IFXR1, IFXM1, IFXR2, IFXM2, IFXR3, IFXM3, IFTAC, IFOAC, IFTME, IFPRJ, IFRE1, IFRE2, IFRE3, IFRE4, IFLNE, IFRLS) 
AS SELECT ACTIV, CMPNO, PLTNO, DECIMAL( (glac1 *  1000000000000 + glac2 * 1000000 + glac3 * 1000 + glac4),15,0) glac#, 
IFTDT, USRID, IFPDT, JENUM, GLCMT, SOURC, TRTYP, GCODE, DECIMAL(case gcode when 'D' then ifamt else ifamt * -1 END,13,2) glamt, REFR1, REFR2, REFR3, IFOR1, IFOR2, IFCUR, IFFAM, IFXRT, USRF1, USRF2, IFPYR, IFPRD, IFFCC, IFXMD, IFXR1, IFXM1, IFXR2, IFXM2, IFXR3, IFXM3, IFTAC, IFOAC, IFTME, IFPRJ, IFRE1, IFRE2, IFRE3,  IFRE4, IFLNE, IFRLS
FROM RMSFILES/GLIFP100

[Denny]

Opps. You can tell I am new at SQL.

Correction. The first set of field names define the new fields in the View. The second set are the fields from the physical.