RE: Syntax for Stored Proc on IBM i

You must use SET (instead of SELECT) to assign values to your procedure variables...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Thursday, September 12, 2013 1:24 PM
To: Midrange Systems Technical Discussion
Subject: Syntax for Stored Proc on IBM i

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