Oh, two other details:
comments in Run SQL Scripts are formd using two dash characters: -- my comment
a good habit is to end each sql statement with a pair of semi-colons, as in:
-- my comment
SELECT *
FROM mylib/myfile
;
;
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Crystal Reports
Sent: Friday, May 24, 2013 3:48 PM
To: midrange-l@xxxxxxxxxxxx
Subject: where to enter the sql statements in Iseries nav
I have now the Iseries nav fully installed. where do I enter a query like this?
SELECT
NOTEH6.UARIDC
, NOTEH6.UANOTL
, NOTEP1.UCCMT
, OEORH41.OHORD#
, OEORH41.OHOSTC
, OEORH41.OHORDT
, OEORH41.OHORDD
, OEORH41.OHPO#
, OEORH41.OHSHTC
, OEORH41.OHSLR#
, OETRA95.OTTRNC
, REFER1.RFCAT
, REFER1.RFDAT
FROM OEORH41
INNER JOIN REFER1
ON OEORH41.OHSHTC = REFER1.RFSLC
INNER JOIN OETRA95
ON OEORH41.OHCOM# = OETRA95.OTCOM#
AND OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6
ON OEORH41.OHENT# = NOTEH6.UAENT#
AND OEORH41.OHSFX# = NOTEH6.UASFX#
INNER JOIN NOTEP1
ON NOTEH6.UANOTT = NOTEP1.UCNOTT
AND NOTEH6.UANTK = NOTEP1.UCNTK
AND NOTEH6.UANOT# = NOTEP1.UCNOT#
WHERE NOTEH6.UARIDC = 'UA'
and OETRA95.OTTRNC = '001'
/* !! Note: use special reg
below !! */
and OEORH41.OHORDD = CURRENT DATE
/* !! Note: use IN predicate below !! */
and OEORH41.OHORDT IN ('COC', 'COE', 'COF', 'COR', 'COS')
/* !! Note: missing operator below !! */
AND OEORH41.OHOSTC <> 'CN' /* <--
"AND" was presumed+added */
and REFER1.RFCAT = '0023'
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
