Re: Syntax for Stored Proc on IBM i(OFFLIST)

On 12 Sep 2013 11:23, Hoteltravelfundotcom wrote:

Do you see anything that is not syntax correct for IBM i Stored
Proc?

A number of things are not syntactically correct.

CREATE PROCEDURE sp_AEPAYCR AS

There is no _AS_ for that CREATE statement. The syntax is in the
documentation:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcrtpsf.htm

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) )

The column-name delimiter, at begin and end, is the double-quote
character ("). The paired left\right square-bracket characters are not
allowed as delimiters.

A procedure statement must be terminated with a semicolon. The
CREATE TABLE as a SQL-procedure-statement must follow any declarative
[i.e. after declarations for any of: variable, condition, return code,
handler]. See comments for the following two "declare" source-lines.

SQL Identifiers should not include variant characters; i.e. the hash
(#) character is discouraged.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2iden.htm

declare @FromDate varchar(10)
declare @ToDate varchar(10)

Those variable DECLARE declarative statements
[SQL-variable-declaration], must appear _first_ in that SQL
compound-statement [as the one SQL control-statement] introduced with
the BEGIN within the SQL routine-body. Those, like procedure
statements, must be terminated with a semicolon.

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcompoundstmt.htm

SQL Identifiers should not include variant characters; i.e. the "at"
symbol (@) character is discouraged.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2iden.htm

SELECT @FromDate = CONVERT(char(10), DATEADD(mm,-2,getdate()),112)
select @ToDate = CONVERT(char(10), GetDate(),112)

SELECT ... INTO requires a [dummy-row; e.g. values-row] file from
which to select the data. Either a VALUES INTO statement or the SET
statement is preferable. Whichever, the procedure-statement should have
a semicolon (;) as terminator.

The identifier "MM" seems to be undeclared. That could exist as the
result of a prior CREATE VARIABLE or perhaps that was intended to be
specified on the parameter-declaration of the CREATE PROCEDURE statement.?

-- 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

Refer to a prior reply in another recent thread showing [a likeness
of] the above query, about using JOIN syntax and the BETWEEN predicate.
The same comments apply also to the following SELECT query.

-- 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

The use of ORDER BY in two separate INSERT statements seems suspect.
If overall physical collation of the data in the target file is
desirable, then the CREATE TABLE ... AS ... WITH DATA using a UNION with
a single ORDER BY is probably the most appropriate. Whatever is the
application(s) that read the data from the temporary\target file
normally would be responsible to request the ORDER BY.

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

If this is supposed to be a RESULT SET returned to the invoker, then
the option-list should define that effect. AFaIK the SELECT must be
statically declared or dynamically prepared cursor that is effected by
an OPEN statement.

If this is the effect /application/ that is reading the data from the
temporary file [the target of the two INSERT requests], then this is the
query that should have the ORDER BY clause.

The commas in the SUBSTR() should be preceded by one or more blanks
to enable the code to be compiled in an SQL environment using the comma
as decimal separator. Joins are best specified using JOIN syntax for
clarity; explicit join vs implied join.

END

Nothing [but comments] should follow the END for that compound-statement.

GO

Thus the "GO" following is unexpected. Perhaps that is just
something copied\pasted unintentionally.?