Re: where to enter the sql statements in Iseries nav

[Mark S Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>]

When you open up System i Navigator, you have to create a connection to 
the IBM I (or OS/400 ) system.

Once connected, you can expand the tree view on the left.

Then, I suggest you press F1=Help and expand the "Databases" topic ...

Under that, you will find a "How do I ..." section ... expand that topic ...

You will see a topic called "Run SQL Scripts."

You should learn how to use the built-in help, both on the "green 
screen" commands, and in the PC-based development tools.

Hope that helps,

Mark S. Waterbury

On 5/24/2013 5:47 PM, Crystal Reports wrote:
> 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'