|
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:
midrange.com
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
