FULL JOIN THROUGH ODBC

[erp84]

I am trying to full join two tables through ODBC but can't seem to get the syntax right, or maybe it's not supported?

select t1.PN, t1.fqty, t2.cqty
from
(select pn,sum(qty) as fqty from invloc where warehouse ="FLORIDA" group by pn) t1
full outer join
(select pn,sum(qty) as cqty from invloc where warehouse ="CALIFORNIA" group by pn ) t2
on
t1.PN = t2.PN

I essentially need to sum up quantities of available inventory, one sum for each warehouse, for each part number, even if sum=0.

Any help is appreciated!

 

[JRB-Bldr]

In your query syntax above I only see one actual data table referenced: InvLoc

Yes, you appear to want to use it as T1 & again as T2, but why?

I'd think that something like the following would give you want you intend

SELECT PN,;
   SUM(IIF(ALLTRIM(UPPER(Warehouse))="FLORIDA",Qty,0)) AS FQty,;
   SUM(IIF(ALLTRIM(UPPER(Warehouse))="CALIFORNIA",Qty,0)) AS CQty;
   FROM InvLoc;
   GROUP BY PN;
   INTO CURSOR Results READWRITE

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

I did forget to ask what is your data 'backend'

If it is not a VFP data table, then the IIF() function will not work in the query syntax.

But if it happened to be a SQL Server, you could change that part of the code to

SUM[b](CASE WHEN RTRIM(UPPER(Warehouse)) = 'FLORIDA'
                     THEN Qty
                ELSE 0 END)[/b] AS FQty
and so on...

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Given that you are using ODBC, that suggests that you are using a back-end database, such as SQL Server, MySQL, Oracle, or something similar.

If so, keep in mind that syntax rules and features vary slightly between databases, so it's not possible for us to know if your query is valid, unless you tell us which database you are using.

Having said that, many databases have an equivalent of VFP's command window, where you can type a command and see the result straight away (it's called the query window in SQL Server, for example). You should be able to paste your query into that window, and see if it works; if it doesn't, you should see an error message.

I suggest you do that, and let us know the result.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[erp84]

I'm actually in Crystal Reports connecting to VFP tables through ODBC. I'm using SQL queries to select my data for performance reasons, pulling down large tables takes forever.

I tried the syntax posted and it failed "Command is missing require clause.". Seems maybe VFP ODBC driver is significantly limited?

 

[Mike Lewis]

Seems maybe VFP ODBC driver is significantly limited?

That is exactly right.

The problem is that the VFP ODBC driver only supports VFP SQL syntax up to version 6.0, which does not include sub-queries. The solution is to use the VFP OLE DB provider instead. You can do that in Crystal Reports, and it supports the most up-to-date version of the language. So, on that basis, your query should work as expected.

You might find this article useful:

http://www.hexcentral.com/articles/crystal-sql-commands.htm.

Although it isn't specific to VFP, it does show how to use an OLE DB provider to access the database.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[erp84]

Thanks Mike! As an added bonus it even runs much quicker!

 

[TamarGranor]

The problem is that the VFP ODBC driver only supports VFP SQL syntax up to version 6.0, which does not include sub-queries.

That's not exactly right. What I think you meant to say is that the VFP ODBC driver doesn't support sub-queries in the FROM clause (AKA derived tables).

Tamar

 

[Mike Lewis]

What I think you meant to say is that the VFP ODBC driver doesn't support sub-queries in the FROM clause (AKA derived tables)

Tamar, you know, I thought of that just after I hit the Submit button. I didn't bother to correct it, because it wouldn't have made any difference to the solution. But you're right to point it out.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, how about a simpler query where sums for each warehouse are a record each?

Select pn, warehouse, sum(qty) as whqty from invloc group by pn, warehouseinto cursor quantities readwrite

Much simpler, no need to add to the query, if you grow and have more warehouses. This is how queries should be maintanance free.


Bye, Olaf.

 

[Olaf Doschke]

There is a space missing in warehouseinto, of course.

Bye, Olaf.

 

[Olaf Doschke]

Oh, and you can't use readwrite with ODBC, too. This was introduced in VFP7. Doesn't matter, though, as you would only be able to write back to the cursor in VFP anyway.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

But wouldn't he need a WHERE clause to limit the results to California and Florida? He could filter on the warehouse within the report, but that would mean retrieving data for all warehouses, which would be slower.

Also, I don't get your point about READWRITE. He is using this data in a report, so why does he need to update the cursor? In fact, why does he need INTO CURSOR at all? He is executing the query from Crystal Reports, and will therefore only see the result set.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike,


The readwrite clause just came from his initial query. I don't know how a query result arrives in Crystal, most probably not as a cursor, but as a recordset, which has it's own rules anyway, and won't need that clause.

But readwrite is of course something failing on the ODBC driver, too, not only the form subqueries.

Bye, Olaf.