AW: where to enter the sql statements in Iseries nav -- MIDRANGE-L

Just FYI:
Comments as /* My comment */ are also supported within IBM i Navigator's
Run an SQL Script.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary Thompson
Gesendet: Saturday, 25.5 2013 00:07
An: Midrange Systems Technical Discussion
Betreff: RE: where to enter the sql statements in Iseries nav

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.

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