Re: SQL from MS SQL Server -- MIDRANGE-L

ya had to go there didn't ya..............

we have a TON of em that should be RPG's or view created or.......

I'll keep slogging

On 11/29/06, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:


Wow...probably could be done in RPG with a priming read and a couple of
chains. :)

On 11/29/06, Mark Allen <scprideandms@xxxxxxxxx> wrote:
>
> will iNav let me run the MS SQL Server statements as is (example below
of
> the first part ONLY)
>
> SELECT DISTINCT
>
>
>             n.C1NMID AS id,
>
> n.C1LNAM  AS lname,
>
> n.C1MNAM AS mname,
>
> n.C1PRFN AS prefname,
>
> n.C1FNAM AS firstname,
>
> a.[desc@7] AS prefix,
> RSI4AA AS degreedate,
>
> b.[desc@7] AS suffix,
>
> WELGNL1.C7HSAA,
> RTRIM(NMINTL0.IFVOAB) AS emailadd,
>
> RTRIM(NMINTL0.IFC5AA) AS emailadd2,
> c.C2CITY AS city,
>
> c.C2STAT AS state,
>
> z.[desc@7] AS country,
>
>
>
> degreeyear = CASE
>
>
>
> WHEN
>
>
>
> (RTRIM(RSI4AA) <> '') AND
>
> (RTRIM(RSI4AA) <> 0) AND
> len(RTRIM(RSI4AA)) > 6
>
> THEN '20' + RIGHT(LEFT(RTRIM(RSI4AA),3), 2)
>
>
>
> WHEN
>
> (RTRIM(RSI4AA) <> '') AND
>
> (RTRIM(RSI4AA) <> 0) AND
>
>             len(RTRIM(RSI4AA)) = 6
>
> THEN '19' + LEFT(RTRIM(RSI4AA), 2)
>
>
>
> ELSE ''
>
> END,
>
>
>
> RSGJAA AS degree,
>
> STDMSL0.SBPEAE,
>
> PDE.[PDE TITLE],
>
>              e.C1LNAM AS core1lname,
>
> e.C1FNAM AS core1fname,
>
> g.C1LNAM AS core2lname,
>
> g.C1FNAM AS core2fname,
> i.C1LNAM AS core3lname,
> i.C1FNAM AS core3fname,
>
> RTRIM(RSI4AA) as RSI4AA,
>
> RSGVAA as exitreason
>
> FROM         NAMMSL0 n
>
> INNER JOIN
>
>
>
> DGRHSL1 ON n.C1NMID = RSNMID AND
> RSI4AA <> 0
>
>
> At this point I've duplicated the MS SQL Server stuff manually on our
box
> and the "missing" person is in the result set, so far, only 13 more
joins
> to
> go!!!!
>
>
> On 11/29/06, Haase, Justin C. <justin.haase@xxxxxxxxxxxx> wrote:
> >
> > iNav's SQL toolset is PHENOMENAL.  Visual Explain?  Yeah!  Index
> > Advisor?  YEAH!  HEALTH CENTER (V5R4)?!?!?! YEEEAAAAAHHHHH!!!!!!
> >
> > Very nice stuff.
> >
> >
> > --
> > Justin C. Haase - iSeries System Engineer
> > IBM Certified Systems Expert - System i
> > Kingland Systems Corporation
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> > Sent: Wednesday, November 29, 2006 9:48 AM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: SQL from MS SQL Server
> >
> > select * from a
> > ....+....1...
> > MYKEY  MYDATA
> >   A    B
> >   B    C
> >
> > select * from b
> > ....+....1....+
> > MYKEY  ITSDATA
> >   A    HI
> >
> > 1. An unspecified join seems to be equal to an inner join - data must
> > exist in both files.
> > SELECT A.MYKEY,A.MYDATA,B.ITSDATA FROM A JOIN B USING (MYKEY) and
SELECT
> > A.MYKEY,A.MYDATA,B.ITSDATA FROM A inner JOIN B USING (MYKEY) both
return
> > only ....+....1....+....2..
> > MYKEY  MYDATA  ITSDATA
> >   A    B       HI
> >
> >
> > 2. LEFT JOIN seems to be equal to LEFT OUTER JOIN - pull from
first/left
> > most file all records and any matching (if any) from the second file
> >
> > SELECT A.MYKEY,A.MYDATA,B.ITSDATA FROM A LEFT JOIN B USING (MYKEY)
> > ....+....1....+....2..
> > MYKEY  MYDATA  ITSDATA
> >   A    B       HI
> >   B    C       -
> >
> > SELECT A.MYKEY,A.MYDATA,B.ITSDATA FROM A LEFT OUTER JOIN B USING
(MYKEY)
> > ....+....1....+....2..
> > MYKEY  MYDATA  ITSDATA
> >   A    B       HI
> >   B    C       -
> >
> >
> > 3. Don't know if any of the tools in iNav will allow you to see where
> > the
> > person is being dropped.  But I have heard that the set of tools in
iNav
> >
> > are so darn good for analysis that one shop switched from SQL Server
on
> > that basis alone.
> >
> > Rob Berendt
> > --
> > Group Dekko Services, LLC
> > Dept 01.073
> > PO Box 2000
> > Dock 108
> > 6928N 400E
> > Kendallville, IN 46755
> > http://www.dekko.com
> >
> >
> >
> >
> >
> > "Mark Allen" <scprideandms@xxxxxxxxx>
> > Sent by: midrange-l-bounces@xxxxxxxxxxxx
> > 11/29/2006 10:28 AM
> > Please respond to
> > Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> >
> >
> > To
> > "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> > cc
> >
> > Subject
> > SQL from MS SQL Server
> >
> >
> >
> >
> >
> >
> > I have an SQL statement that runs nightly to pull data from our
iSeries
> > (V5R4) for a web based directory that does not "appear" to be pulling
> > all
> > the data correctly (1 specific person right now).
> >
> > This is NOT a pretty statement and would like the lists advice on the
> > best
> > way to see "why" I am missing this person (and probably more)
> >
> > This is a SELECT DISTINCT followed by a series of joins as follows:
> >
> > INNER JOIN
> > then 7 LEFT JOIN's
> > then 6 LEFT OUTER JOIN's
> >
> > Questions:
> >
> > 1.  The INNER JOIN means that data must exist in both files?
> > 2.  LEFT JOIN and LEFT OUTER JOIN means pull from first/left most file
> > all
> > records and any matching (if any) from the second file
> > 3.  Is there a way to run the SELECT so I can see "where" it is
dropping
> >
> > the
> > affected person
> >
> > There are just a couple of WHERE statements and they do not appear to
be
> > affecting the selection (the person missing would be selected based on
> > the
> > WHERE condition)
> > --
> > 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.
> >
> >
> >
> > CONFIDENTIALITY NOTICE:  This e-mail communication, including
> attachments,
> > is covered by the Electronic Communications Privacy Act, 18 U.S.C.
> > 2510-2521, is confidential, and may be legally privileged.  If you are
> not
> > the intended recipient or believe you received this communication in
> error,
> > please reply to the sender indicating that fact and delete the copy
you
> > received.  In addition, retention, dissemination, distribution,
copying,
> or
> > otherwise use of the information contained in this communication is
> strictly
> > prohibited.  Thank you.
> >
> > --
> > 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.
>
>
--
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.