Re: Making Matched Records with Primary File in Query an Outer joinin SQL

Vern and Charles,

The joins and the "where clause" fields are all printed and I have all of them in my SQL. However, I have discovered that it isn't as simple as taking the RTVQMQRY data into and SQL when you have a "Matched Records with Primary File" option checked because I can take the QMQRY data, paste it into an SQL statement and get something completely different than the query. I understand that RTVQMQRY is limited, I was just trying to find something to help transform this query into an SQL statement. Using Left outer join syntax is getting me there, just not completely.

I am still working the trial and error approach. Eventually I'll figure it out.

Thanks for the help.

Pete


Vernon Hamberg wrote:

Pete

RTVQMQRY has these known limitations - only type 1 queries are correctly converted.

Charles suggested looking at the JOIN spec in WRKQRY - I forget the rules about the joins, but they are documented - I too wish for a complete and useful converter.

Vern

At 08:01 AM 10/12/2007, you wrote:

"Pete, make sure you double check the joins being done in query vs. the
joins being done in SQL. "

There's the rub. I don't know what the joins are in the query because
all I have is a list of "where " comparisons. That is I have "Where
field1=field2 and field1 = field2 " etc instead of "from file1 join
file2 on field1= field2 join file3 on field1 = field3" That is what is
leaving me scratching on head on a " Matched Records with Primary File"
option in query. There are no joins listed in the query from RTVQMQRY.

I guess to restate this: If there WAS a way to see the exact syntax (SQL
Syntax) from a query, replicating that in SQL would be a snap. As it
is, when you have a complex query that produces the desired results in
Query/400, you basically have to start from scratch in rebuild that
query in SQL. In my particular case I have a query that outputs in a
way I expect in Query/400 but replicating that same result in SQL had
been devilishly difficult.

I wonder how the new Web Query product is going to migrate existing
Query/400 queries (if it indeed does do that)? They must have a better
tool than RTVQMQRY .

Pete