RE: RTVQMQRY anomaly? -- MIDRANGE-L

Exception joins can be directly defined as:

Select file_a.*
from file_a EXCEPTION JOIN file_b
        on (fld_a = fld_b)

which returns all records in file_a that do not join to records in file_b.
I shaved hours off a SQL query by switching from a subselect form ("where
fld_a not in (subselect)") to an exception join.

Query existed long before SQL-89 was ever formally defined.  I doesn't
surprise me that some of these old tools don't know about newer SQL
features.

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@attbi.com]
Sent: Friday, March 08, 2002 11:41 AM
To: midrange-l@midrange.com
Subject: Re: RTVQMQRY anomaly?


Interesting. But there is an equivalent for exception join - it's called
WHERE NOT EXISTS SELECT, and there's no reason it could not have been done.
It would involve a correlated subselect, but so what?

Left and right outer joins can be simulated in a similar fashion, with
unions.

You should get a warning, obviously. Did you check joblog. I've seen some
stuff there.

Cheers

At 12:22 PM 3/8/02 -0500, you wrote:

>Vernon,
>
>I did some checking in the manuals, and found my answer.
>
>quoted from "DB2 for AS/400 Query Management"
>
>"Some of the functions that can be specified and saved in a QRYDFN object
>cannot be transformed into query-management-supported SAA functions, and
>some cannot be precisely transformed.
><snip?
>The ANZQRY command provides analysis in the form of messages and on-line
>help information that suggest ways of dealing with transformation problems.
>"
>
>and in another chapter, explains what it does with joins:
>
>"If any join tests exist, they are used to start the WHERE clause, and any
>record selection tests are added using the AND operator. "
>
>My guess is... when joining more than 2 files, there would be no direct sql
>equivelent to qry/400 join option 3.  so I guess that's why it just throws
>the join out and makes it a "where".  I ran ANZQRY and that's just what it
>said it did - ignored the join type.
>
>obviously, not a perfect command, AND it didn't give me any warnings when I
>ran it.   It would be nice if it could return the proper join type if there
>were only 2 files.  oh well.
>
>thanks anyway,
>
>rick
>
>
>
>---original message---
>From: Vernon Hamberg <vhamberg@attbi.com>
>
>Looks wrong to me. What's your vxrxmx?
>
>At 08:11 AM 3/8/02 -0500, you wrote:
> >Hey all,
> >
> >I created a qry/400 query, joining two files (option 3=Unmatched records
> >with primary file).   I saved the query and did a RTVQMQRY to see what
the
> >sql statement should look like.
> >
> >what I got was a simple join:
> >
> >SELECT
> >   ALL       T01.ORG, T01.VENID, T01.ORIGORG
> >             T01.INVOICE
> >   FROM      M30DAP/VOL0 T01,
> >             M30DAP/VOH0 T02
> >   WHERE     T01.ORG = T02.ORG
> >     AND     T01.VENID = T02.VENID
> >     AND     T01.ORIGORG = T02.ORIGORG
> >     AND     T01.VCHNBR = T02.VCHNBR
> >
> >shouldn't this have been a 'left outer join'?  I ran the SQL statement
and
> >got completely different results than the first.  What gives?
> >
> >I'm on V4R5.
> >
> >Thanks!
> >
> >Rick
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>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@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.