AS400

Update Multiple Field with Value from Fields in another file Below Query can update fields a.Fld1,a.Fld2,a.Fld3,a.Fld4 & a.Fld5  in TBL1 with the values from  b.Bld1 ,b.Bld5,b.Bld3,b.Bld6 & b.Bld8 in TBL2 Below Query will work only if the below query will not have duplicate record select b.fkey1,b.fkey2 from Tabl2 B, TBL1 a               where a.fkey1= b.fkey1 and a.fkey2= b.fkey2 If there is duplicate change the where clause to get unique values then use the same whare claues in the subquery in both places update Tabl1  a set (a.Fld1,a.Fld2,a.Fld3,a.Fld4, a.Fld5  )  =           (select b.Bld1 ,b.Bld5,b.Bld3,b.Bld6,b.Bld8 from Tabl2 B               where a.fkey1= b.fkey1 and a.fkey2= b.fkey2 ) Where (fkey1,fkey2) in (select fkey1,fkey2 from Tbl2)

Control the Way SQL sorts the records based on Values in the tables field: Below piece of SQL statement will let me have control on how the SQL sort the records based on the values in the Status field; apart from SQL sort based on the alphabetic order   order by case  Status                                                 when 'D' then 1                            when 'A' then 2                            when 'X' then 3                            when ' ' then 99                         end                               

Scenario: Container  field in CONT file is build as Julian Day (3 Digit)+ 6 Digit sequence number. Julian Day (3 Char) is also field in CONT file. Below query will identify all the records that are having Jul_Day and first 3 digit are not matching using Case when condition. select * from Cont where                                                                                                               int(Jul_Day) <> case                                                          when length(trim(char(container))) = 8                        then int(substr(char(container),1,2))                       when length(trim(char(container))) = 7                        then int(substr(char(container),1,1))                       Else                                                               int(substr(char(container),1,3)) end                                                                 

Check box can be added to the AS400 Display using the Multi Choice Field. Each Choice in the MLTCHCFLD should have a Choice control defined as below. These Choice Control can be used in the Program logic. A            MOPT           2Y 0B  3 58MLTCHCFLD               A                                      CHOICE(1 'Shipment')     A                                      CHOICE(2 'Transfer')     A                                      CHOICE(3 'Ship to ')     A                                      CHOICE(4 'Model')       A                                      CHCCTL(1 &CHC1)         A                                      CHCCTL(2 &CHC2)         A                                      CHCCTL(3 &CHC3)         A                                      CHCCTL(4 &CHC4)         A            CHC1           1Y 0H                               A            CHC2           1Y 0H                               A            CHC3           1Y 0

Radio button can be added to the AS400 Display. In the below code we have added a Radio button with 2 option 'Today' or 'Tomorrow' DSPF: A            OPTION         2Y 0B  3 42SNGCHCFLD           A                                      CHOICE(1 'Today')   A                                      CHOICE(2 'Tomorrow') The OPTION can be used used in RPG program as PGM:   If Option =  1 ;                    Msg = 'Today';               ElseIf Option = 2 ;                Msg = 'Tomorrow';           EndIf;                            

TRIM Sample Query and Result: TRIM – Removed Both leading and Trailing Blanks select Length( trim (' 12345  ')) from Sysibm/Sysdummy1-> 5 LTRIM – Removes Leading blanks select Length( ltrim (' 12345  ')) from  Sysibm/Sysdummy1   -> 7 RTRIM – Removes Trailing blanks select Length( rtrim (' 12345  ')) from  Sysibm/Sysdummy1   -> 6

REPLACE will replace a set characters in a String ro another set of Characters Examples: SELECT CHAR ( REPLACE ( 'DINING' , 'N' , 'VID' ), 10 )       FROM SYSIBM . SYSDUMMY1 :   The result is the string 'DIVIDIVIDG'. SELECT REPLACE ( 'ABCXYZ' , 'ABC' , '' )       FROM SYSIBM . SYSDUMMY1 :   The result is the string 'XYZ'. SELECT REPLACE ( 'ABCCABCC' , 'ABC' , 'AB' )       FROM SYSIBM . SYSDUMMY1 : 

Date to Int conversion:  Select  int(substr(char( year(current date)*10000 + month(current   date)*100 + day(current date)),3)) from Sysibm/Sysdummy1 2017/05/11 = 170511 To get the integer value of date 8 months from today:  Select  int(substr(char( year(current date - 8 Months)*10000 + month(current   date - 8 months)*100 + day(current date - 8 Months)),3)) from Sysibm/Sysdummy1 2016/11/08 =  161,108

Below code ERASE with the Negative indicators on the Subfile Display will clear the Previous Exfmt's Data. Scenario: I have a subfile with Option to delete the record and redisplay the result. If all the records are removed then there wont be anything to display and since nothing is in the subfile we cannot turnon the display file indicator. Resulting the data from the previous display will still be seen. That is the subfile memory from the previous exfmt. It can be cleared using ERASE in the subfile control. A    50                                  SFLDSP                 A N50                                  ERASE(SFLM)            SFLM is the subfile record format

AS400: Timestamp to yyyy-mm-dd (ISO) select Char(date(Now()), iso) from  sysibm / sysdummy1 Output  2017-12-18 select date(Now()) from  sysibm / sysdummy1 Output 12/18/17

CTE Example: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. Query to find out the Model present in the ItmSup1 file that are present in any 2 of the 3 files (ItmStr1, ItmStr2, ItmStr3) with tbl1 (SKU) as                                  (select model from devlib/ItmStr1                   union all                                             select model from devlib/ItmStr2                      union all                                             select model from devlib/ItmStr3                      ), tbl2 (model) as (