Once you have a connection to your iSeries, in the left pane,
expand the connection by clicking the plus sign in the box at the left of your connecton.
This should produce a "list" starting with "Basic Operations" and ending with maybe "AFP Manager"
Look for "Databases" in the list in the left pane, and click on that to expand the list.
You should have at least one data base (others can be defined as needed)
Click on one (or the only) database (database names are the first indent under "Databases")
When you click on a database name, you should then see an option named "Run an SQL script"
in the pane at the bottom right of your System i Navigator window.
Click "Run an SQL script (and wait a few seconds) and a new window should open with
something like "Untitled - Run SQL Scripts " in the title bar at the top of the window,
and "Connected to relational database..." in message pane in the bottom half of the window.
All of your SQL code and comments are entered in the top half.
Best of Luck (the waters here are deep, but the fishing provides a rich harvest).
-----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
