RE: SQL from MS SQL Server

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.