Re: getting stomped on ORDER BY

On 11 Sep 2013 14:29, Hoteltravelfundotcom wrote:

<<SNIP>>

CREATE VIEW AEPAYCR2 AS
SELECT ALL
T01.OTCOM#, T02.OHPTTC, T02.OHSLR#, T01.OTORD#
, T01.OTTRND, T02.OHORDT, T02.OHORDD, T02.OHTTN$
, T01.OTUSRN, '' as INVTOT
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 >= 20120701
AND T02.OHORDD <= 20130930
)
ORDER BY T02.OHPTTC ASC, T01.OTUSRN ASC, T01.OTCOM# ASC,

Beyond the ORDER BY clause not being allowed in a VIEW [as others have replied to the <snipped> inquiry in the quoted message], there is a superfluous comma on the end of that clause. Thus even outside the CREATE VIEW request, the SELECT statement is invalid.

While it appears the statement is auto-generated [although without name delimiters], I would suggest some /improvements/ for readability: changing to use JOIN syntax [or removing the unneeded parenthetical separation of the selection predicates from the /join/ predicates]; using the BETWEEN predicate vs two separate x-than-equal predicates [used on the OHORDD column]. If the [client] feature that generates the statement has a configurable option for composing JOIN syntax, I suggest activating that capability, so a join query is conspicuous [and IMO more clear].

CREATE VIEW AEPAYCR2 AS
SELECT ALL
T01.OTCOM#, T02.OHPTTC, T02.OHSLR#, T01.OTORD#
, T01.OTTRND, T02.OHORDT, T02.OHORDD, T02.OHTTN$
, T01.OTUSRN, '' as INVTOT
FROM ASTCCDTA.OETRA99 T01
INNER JOIN ASTDTA .OEORH1 T02
ON T01.OTCOM# = T02.OHCOM#
AND T01.OTORD# = T02.OHORD#
WHERE T01.OTTRNC = '001'
AND T02.OHPTTC IN ('N30', 'PPD', 'PCK')
AND T02.OHORDD BETWEEN 20120701 AND 20130930