Results 1 to 2 of 2

Thread: AS/400 syntax issue

  1. #1
    Join Date
    Jan 2003
    Location
    Orlando, FL
    Posts
    3

    AS/400 syntax issue

    What would be the correct syntax for this statement.
    AND DIXCOM.F0005.DRKY = right(' ' + DIXCUST.FDCASLD.CASR2, 10)

    I use this in SQL 7 and it works great but not on the AS/400. What would be the correct syntax for AS/400 or a workaround.

  2. #2
    Join Date
    Jan 2003
    Location
    Columbus, Ohio
    Posts
    1

    Lightbulb

    Since SQL/400 doesn't currently have a RIGHT function I have done several work arounds. I hope this one helps.

    create procedure cmsddata.CcYyMmDd
    (InOut CcYyMmDd VarChar(8))

    -- Accept 6 character date of YYMMDD
    -- Create 8 character date of CCYYMMDD

    -- By: Dan Holt (CompuName)

    Language Sql

    Begin

    -- Accept Date of YyMmDd
    -- Return Date of CcYyMmDd

    Declare cnvDate Decimal(8,0);
    Declare FldLength Integer;

    Set CnvDate = cast(CcYyMmDd as Decimal);

    If CnvDate > 500101 Then

    Set CnvDate = CnvDate + 19000000;

    Else

    Set CnvDate = CnvDate + 20000000;

    End If;

    Set CcYyMmDd = '000' || Strip(cast(CnvDate as VarChar(8)), L, ' '); -- Remove leading spaces and add zero's

    Set FldLength = Character_Length(CcYyMmDd);

    Set CcYyMmDd = SubStr (CcYYMmDd, FldLength - 7, 8);


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •