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

Sadly its a REAL column name


On 11/29/06, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:


When debugging complex SQL statements I like to modularize them as much as
possible.  One way is to use CTEs, but in your case it may actually be
easier to create SQL views.

You'd start with the lowest level SQL and build upwards.  As you go along,
you'd be checking if the result set at that point contains data that you
expect.

I know it seems like a lot of work, but at least there is a method to it.
And you'll learn things about how SQL works as you go along.

BTW, DB2 for System i is compliant with 2003 core standard so you should
be
able to run any SQL statement that doesn't use proprietary SQL Server
functions.  What you have in your statement looks rather vanilla, except I
am not sure about [desc@7]... is that really a column name or some SQL
server special thing?

HTH

Elvis

-----Original Message-----
Subject: Re: SQL from MS SQL Server

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.