×
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.
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
midrange.com
