× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.