MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2006

Re: SQL from MS SQL Server



fixed

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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact