Substring Operations in SQL

The example below is an SQL function in Net.Data manipulating a date format and concatenating the date digits with edit characters.

%{=====================================================%}
%{============== Get Project Data =====================%}
%{=====================================================%}

  %FUNCTION(DTW_SQL) SHOWPOS (OUT t1)  {

      SELECT PHVEND, PHSTK, PHORD#,
        substr(DIGITS(PHDT),5,2) concat '/' concat substr(DIGITS(PHDT),7,2) concat '/' concat substr(DIGITS(PHDT),3,2) as PODATE,
        substr(DIGITS(PHSPDT),5,2) concat '/' concat substr(DIGITS(PHSPDT),7,2) concat '/' concat substr(DIGITS(PHSPDT),3,2) as OSHDATE,
        substr(DIGITS(PHPMDTL),5,2) concat '/' concat substr(DIGITS(PHPMDTL),7,2) concat '/' concat substr(DIGITS(PHPMDTL),3,2) as LASTPRM,
        PHQTY, PHCOST, PHDUDT
        FROM PORHEDL5 INNER JOIN OPSCTLLF ON PHSTK = SYKYID1
      WHERE  PHVEND=$(VND)
      %{  And  PHSTK <> 'N' %}
	  And   (substr(SYDATA,1,1)='Y' Or substr(SYDATA,4,1)='Y')
      ORDER BY PHORD#

        %MESSAGE { 100 : { @Write_Return() %} : continue %}                                   

The example below is an embedded SQL statement in RPG manipulating selected fields to match the length of the desired output fields.

          Exec SQL
            Declare WR1010cursor Cursor For
             Select
              Substr (doccode,1,4) as Dtype,
              subStr (docNum,5,8)  as docNo,
              cast(doclinenum as decimal(9,0)) as ieRef,
              substr(ref3,1,20) as dxref3,
              substr (el2,1,12) as dacct1,
              substr (el3,1,12) as dacct2,
              substr (el4,1,12) as dacct3,
              substr (el5,1,12) as dacct4,
              case when(statpay = 84) then 'A'
                   when(statpay = 87) then 'H'
                   when(statpay = 89) then 'P'
                   when(statpay = 93) then 'R'
                   when(statpay = 171) then 'X'
                   Else ' '
              End as DDstat,
              substr(DDescr,1,30) as Dddesc,
              yr,
              period,
              substr(ref2,1,10) as Dxref2,
              ifNull(Cast(Replace(Char(DOCDATE, ISO), '-', '')
                 as  dec(8, 0)),'0') as iVdat,
              substr(ref1,1,20),
             Cast(valueHome as decimal(18,3)) as ddValu

            From OAS_DOCV1