Re: Syntax for Stored Proc on IBM i

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You might be interested in the IBM DB2 migration toolkit...
http://www14.software.ibm.com/webapp/download/product.jsp?id=GFAH-4ULUTN&cat=database

It's specific to DB2 LUW, but the results generally work with DB2 for i.

Alternatively, would might want to look at *IBM® Database Conversion
Workbench plugin for IBM Data Studio.*
https://www.ibm.com/developerworks/community/groups/service/html/communityview?communityUuid=05901c97-75b2-47a1-9c32-25f748855913


HTH,
CHarles


On Thu, Sep 12, 2013 at 2:23 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

> HI Do you see anything that is not syntax correct for IBM i Stored Proc?
>
> CREATE PROCEDURE sp_AEPAYCR
>
> AS
> BEGIN
>  CREATE TABLE #TempTbl(
>   [OTCOM#] varchar(50),
>   [OHPTTC] varchar(50),
>   [OHSLR#] varchar(50),
>   [OTORD#] VARCHAR(50),
>   [OTTRND] VARCHAR(50),
>   [OHORDT] VARCHAR(50),
>   [OHORDD] VARCHAR(50),
>   [OHTTN$] VARCHAR(50),
>   [OTUSRN] VARCHAR(50),
>   [INVTOT] VARCHAR(50),
>   [WWDTA] VARCHAR(50)
>   )
>
>  declare @FromDate varchar(10)
>  declare @ToDate varchar(10)
>
>  SELECT @FromDate = CONVERT(char(10), DATEADD(mm,-2,getdate()),112)
>  select @ToDate = CONVERT(char(10), GetDate(),112)
>
>  -- insert first query
>  insert into #TempTbl (
>   [OTCOM#] ,
>   [OHPTTC] ,
>   [OHSLR#] ,
>   [OTORD#] ,
>   [OTTRND] ,
>   [OHORDT] ,
>   [OHORDD] ,
>   [OHTTN$] ,
>   [OTUSRN] ,
>   [INVTOT] ,
>   [WWDTA] )
> SELECT
>
>   ALL       T01.OTCOM#, T02.OHPTTC, T02.OHSLR#, T01.OTORD#,
> T01.OTTRND,
>             T02.OHORDT, T02.OHORDD, T02.OHTTN$, T01.OTUSRN  ,'' as INVTOT,
> '' as WWDTA
>   FROM      ASTCCDTA/OETRA99
> T01,
>             ASTDTA/OEORH1
> T02
>   WHERE     T01.OTCOM# =
> T02.OHCOM#
>     AND     T01.OTORD# =
> T02.OHORD#
>     AND(    T01.OTTRNC =
> '001'
>     AND     T02.OHPTTC IN ('N30', 'PPD',
> 'PCK')
>     AND     T02.OHORDD >= @FromDate
>     AND     T02.OHORDD <= @ToDate)
>   ORDER BY  T02.OHPTTC ASC, T01.OTUSRN ASC, T01.OTCOM# ASC, T01.OTORD#
> ASC,
>             T01.OTTRND
> ASC
>
>
> -- insert second query
>  insert into #TempTbl (
>   [OTCOM#] ,
>   [OHPTTC] ,
>   [OHSLR#] ,
>   [OTORD#] ,
>   [OTTRND] ,
>   [OHORDT] ,
>   [OHORDD] ,
>   [OHTTN$] ,
>   [OTUSRN] ,
>   [INVTOT] ,
>   [WWDTA] )
> SELECT
>
>   ALL       '' as [OTCOM#],T03.OHPTTC, T03.OHSLR#, T01.OTORD#,'' as
> [OTTRND] , T03.OHORDT, T03.OHORDD, '' as [OHTTN$] ,
>   '' as [OTUSRN] , (IHORG$-(IHORD$)) AS INVTOT, '' as [WWDTA]
>   FROM      ASTCCDTA/OETRA99
> T01,
>             ASTDTA/OEINH1
> T02,
>             ASTDTA/OEORH1
> T03
>   WHERE     T01.OTORD# =
> T02.IHORD#
>     AND     T02.IHORD# =
> T03.OHORD#
>     AND(    T01.OTTRNC =
> '001'
>     AND     T02.IHPTTC IN ('N30', 'PPD',
> 'PCK')
>     AND     T02.IHORDD >=
> @FromDate
>     AND     T02.IHORDD <=
> @ToDate
>     AND     (IHORG$-(IHORD$)) <>
> 0)
>   ORDER BY  T01.OTORD#
> ASC
>
>
> SELECT
>   ALL
>   T01.[OTCOM#] ,T01.[OHPTTC] , T01.[OHSLR#] ,T01.[OTORD#] ,T01.[OTTRND]
> ,T01.[OHORDT] ,T01.[OHORDD] ,T01.[OHTTN$] ,T01.[OTUSRN] ,
>   T01.[INVTOT] ,SUBSTR(RFDTA,1,20) AS WWDTA
>   FROM      #TempTbl  T01,
>             trlib/TRREF T02
>   WHERE     T01.OHSLR# = T02.RFSLC
>
> END
> GO
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.