Re: SQL "USING" reserved word

Frank,

As I said in my earlier post, if one of two conditions exist:
1 - You aren't up to V5R3 yet, or,
2 - You name your fields "wrong"
Then you can't use USING.  By "wrong" I mean calling the field KEY1 F1KEY1 
in one file, and F2KEY1 in another file.  If you had kept the name KEY1 in 
both files then you could have used USING.  Since you didn't you can't use 
the nice:
SELECT
      FILE1.F1KEY1, FILE1.F1KEY2,
      FILE2.F2DETAIL
FROM
      (FILE1
        LEFT OUTER JOIN FILE2 USING (KEY1, KEY2)
Instead you have to use:
SELECT
      FILE1.F1KEY1, FILE1.F1KEY2,
      FILE2.F2DETAIL
FROM
      (FILE1
        LEFT OUTER JOIN FILE2 ON (FILE1.F1KEY1=FILE2.F2KEY1 AND 
FILE1.F1KEY2=FILE2.F2KEY2)

SQL does not 'assume' that you can ignore the first two characters of a 
field name then if the rest of the field name matches it's a link.

Other general information.

You do not always have to create a view either.  You can do the select 
without the view.  You can test that using any number of tools:
STRSQL
RUNSQLSTM
iSeries Navigator's Run SQL Scripts.
The nice thing about the view is that instead of the users who use 
Query/400 or any other query tool (GUI or otherwise) knowing that you have 
to link several files together to get a decent report, they can just query 
the view.  Any tool vendor telling you that their tool is better because 
the users don't have to remember to link several files together is blowing 
you smoke.  Because you are, in effect, doing the same setup instructions 
in their tool that a decent view would do for Query/400.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





fkany@xxxxxxxxxxxxxxxxxx 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/30/2005 05:23 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
MIDRANGE-L@xxxxxxxxxxxx
cc

Subject
SQL "USING" reserved word







I'm trying to pseudocode an SQL solution for my current project.  I'm
stumped at trying to use the "USING" reserved word in the section that 
uses
"LEFT OUTER JOIN".  Did I use it correctly?

Thanks,

Frank

=================================================
FILE1 is the header file, keyed on F1KEY1 and F1KEY2.
FILE2 is the detail file, keyed on F2KEY1 and F2KEY2.

Here's an example of what my SQL pseudocode code looks:

CREATE VIEW ROB/DOUGGIE
      (F1KEY1, F1KEY2, F2DETAIL)

AS SELECT
      FILE1.F1KEY1, FILE1.F1KEY2,
      FILE2.F2DETAIL

FROM
      (FILE1
        LEFT OUTER JOIN FILE2 USING (F1KEY1 AND F1KEY2)



-- 
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.