RE: SQL from MS SQL Server

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