Re: SQL "USING" reserved word -- MIDRANGE-L

USING is a replacement for ON. It's available beginning in V5R3, I think. From the manual--

"The USING clause is equivalent to a join-condition in which each column from the left table-reference is compared equal to a column of the same name in the right table-reference."

BTW, Frank, do you have access to the manuals? Your answer is in the SQL Reference at V5R3. In your example, the fields should be a comma-separated list - there is no logical stuff going on - everything is ANDed. Here's the syntax--

USING (F1KEY1, F1KEY2)

But this will not work in real life because you don't have columns of the same name in the 2 files - you'd need to have an F1KEY1 and and F1KEY2 in FILE2. As pseudo-code, OK, maybe, but your p-code looks like real code, not speudo, so use the real syntax, eh?

HTH
Vern

At 04:28 PM 3/30/2005, you wrote:

try ON instead of USING

you also left off a final  )
_________________________
Daniel L. Gray
Systems Analyst
Maverick Transportation, Inc.
13301 Valentine Road
North Little Rock, AR 72117

Phone: 501-955-1221
Fax: 501-955-4221

             fkany@averittexpr
             ess.com
             Sent by:                                                   To
             midrange-l-bounce         MIDRANGE-L@xxxxxxxxxxxx
             s@xxxxxxxxxxxx                                             cc

                                                                   Subject
             03/30/2005 04:23          SQL "USING" reserved word
             PM


             Please respond to
             Midrange Systems
                 Technical
                Discussion
             <midrange-l@midra
                 nge.com>

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)