FP2.6 sql joins

[johnhouk]

Im new to using SQL in FP and Im trying to transfer the following sql from ms query:

SELECT Max(po.po) as [PO], po.venname as [Vendor], po.podate_ord as [Date_Ordered], stock.stawg as [Ga], stock.stwiretype as [Description], po.pounitcost as [Unit_Cost], po.pounits as [Units], stock.stftperlb as [Ft_per_Lb], po.pounstnum as [Item_Number]
FROM po po inner join stock on po.pounstnum= stock.stitemnum
WHERE (po.pounitcost>0) AND (po.pounstnum Is Not Null) AND (po.pounstnum <>&quot;&quot
Group By po.pounstnum
ORDER BY po.pounstnum

However I can only get as close as:

SELECT MAX(PO.PO) AS [PO], PO.VENNAME AS [VENDOR], PO.PODATE_ORD AS [DATE_ORDERED], STOCK.STAWG as ,;
STOCK.STWIRETYPE, PO.POUNITCOST, PO.POUNITS, PO.POUNSTNUM;
FROM PO, STOCK;
WHERE PO.POUNSTNUM == STOCK.STITEMNUM;
AND (PO.POUNITCOST > 0;
AND PO.POUNSTNUM <> &quot;&quot;
GROUP BY PO.POUNSTNUM;
ORDER BY PO.POUNSTNUM

So my question is, how can I, if at all, acheive my inner join?
I've also noticed the use of cursors in some of the posts. Where can I find more specific info on usiing cursors in 2.6?
Any and all advice is greatly apreciated.
Thanks,
John

 

[rgbean]

John,
Your Where clause (PO.POUNSTNUM == STOCK.STITEMNUM) is establishing an Inner Join, however you may not be getting what you expect. When you do a Group By, then the only data that can be guaranteed as &quot;correct&quot; are aggregate functions (MAX, MIN, SUM, COUNT, etc.) results, the other fields may not &quot;match&quot;. i.e. The Vendor (name) shown doesn't necessarily match the one with the MAX(PO). To get what I think you want, will require a different query. The SQL query capabilities of FoxPro DOS / Windows are quite limited unless you get real creative - consider upgrading to VFP, it supports a much fuller feature set.

There is quite a lot of info on cursors in the help file. Cursors are essential nothing but a temporary table. Almost anything you can do with a table you can do with a cursor. There are 3 basic types: 1) Those created with a CREATE Cursor statement - they are updatable. 2) Those created by an SQL Select on multiple tables or one table with at least one computed or constant field - they are not updatable. 3) Those created from one table with no special circumstances, and these are usually just a Filtered sub-set on the original table. While these are normally not updatable, there are techniques to get at the underlying table.

Rick

 

[johnhouk]

I'm attempting to recreate our app in vb6. Someone else made this foxpro app, and I'm not all that familiar with either FP. I would transfer it to VFP if I thought it would be that easy.

I'm aware of the aggrigate function situation, however this was the only way I could get ms query to return the correct results. I didn't understand why it worked in MSQ.

I hope someone can point me in the right direction on this. I'm trying to save a week or so of manual data entry. I'm trying to insert last known costs from POs where not all POs have prices entered yet. Without a script, I must use the app to look up an item, reveiw its history, return to the item, then edit the item to reflect the correct cost. We have a few thousand items, which is more work than I want to do every couple of weeks.

Anyway, more help would be great.
Thanks,
John

 

[JRB-Bldr]

Not being totally familiar with your application I must make some assumptions. If the assumptions are wrong, sorry - trash this comment.

I would guess that you are looking for the last cost on each individual po.pounstnum (i.e. Item_Number) based on the most recent PO.

If that is right then you might consider re-structuring your query to get better results and you might want to consider getting your end result via a 2 step operation.

Step 1 - collect all valid records organized by pounstnum
SELECT PO.POUNSTNUM AS item_number,;
PO.PO AS po,;
PO.PODATE_ORD AS date_ordered,;
PO.VENNAME AS vendor,;
STOCK.STAWG AS ga,;
STOCK.STWIRETYPE AS description,;
PO.POUNITCOST AS unit_cost,;
PO.POUNITS as units,;
STOCK.STFTPERLB AS ft_per_lb;
FROM PO, STOCK;
WHERE PO.POUNSTNUM == STOCK.STITEMNUM;
AND PO.POUNITCOST > 0;
AND .NOT. EMPTY(PO.POUNSTNUM);
ORDER BY PO.POUNSTNUM, PO.PO DESC;
INTO CURSOR result1

Assuming that the field length of PO.POUNSTNUM and STOCK.STITEMNUM are identical and that there are good equates, this should result in a temporary cursor (result1) containing all good records for each item number sequenced by
hierarchy: #1 - item_number #2 - po number, descending order (i.e. most recent first)


Step 2 - only look at the most recent valid PO record for each item_number.

One way is to:
SELECT result1
INDEX ON item_number TAG key1 UNIQUE
With this index active you now only &quot;show&quot; the most recent PO and its values.

Another way is to:
SELECT result1
DELETE ALL
INDEX ON item_number TAG key1 UNIQUE
RECALL ALL
SET ORDER TO
PURGE
Which would totally eliminate (rather than just &quot;hide&quot all un-wanted records, but most times this is not necessary.

A third way would be to programatically scan through the cursor result1 and only look at the first record for each Item_Number within the cursor.

The above solution does not assume that you want the most recent cost per item_number per vendor. If so then we need to add more code to separate out that information.

Good Luck,
jrbbldr
jrbbldr@yahoo.com

 

[johnhouk]

This looks wanderfull. You assume correctly. I cant wait to try this, but it will have to wait till monday. I'm wandering if I put both select statements in the same .qpr file. As far as the options you presented, I guess i'm interested in whichever one will return results the quickest. And as you mentioned, my end goal IS reporting the 3 (maybe 5) last vendors used, each listing their last known cost, quantity ordered, and date.

I'm going to start with what you've provided me with monday. To see if I can get it working to this level. I'll keep you posted with my progress.
Thank you so very much for you assistance,
John

 

[johnhouk]

I had a minute to try the 1st step. It returned 0 results. Both po.pounstnum and stock.stitemnum are length=16 type=character 0 decimal places. I don't beleive it to be a referential integrity problem. I'll try and see if its something in the where clause.

 

[johnhouk]

Had to remove,
AND .NOT. EMPTY(PO.POUNSTNUM);
Then the first step appears to work fine.
I do not see the null po.pounstnum that I thought would collect all non stock POs, Its fine even if it does reapear, because in the end a single item number will be specified.

Impatience is going to get the best of me one of these days. I added the first option of step 2 to the end of the file. It returns the same number of records as my original excell query. Now I just need to figure out how I want to do this for multiple vendors. I also need to figure out how to incorporate these results into the items screens. I figure it should be invoked and updated when a user looks up an item.

I don't ever remember anticipating a monday,
John

 

[johnhouk]

Two Mondays Later...
I can't figure out how to incorporate this query into what's already there.
Here is the existing Stckbrow.prg:

public itemnum
match=recno()
Skip -5
do while .not. eof()
clear
SET COLOR to R+
@ 0,9 say &quot;STOCK Browsing&quot;
Set Color to W+
@ 3,0 SAY &quot;#&quot;
@ 3,2 say &quot;AWG&quot;
@ 3,9 say &quot;Diameter&quot;
@ 3,20 say &quot;Wire Description&quot;
@ 4,0 to 4,79
Line=0
toprec=recno()
Do While .not. eof().and.line<10
if recno()=match
set color to n/w
endif
@line+5,0 say line picture &quot;9&quot;
@line+5,2 say stawg
@line+5,9 say stdiameter
@line+5,20 say stwiretype
if recno()=match
set color to w+
endif
skip
line=line+1
enddo
if eof()
set color to GR+*
@line+7,30 say &quot;*** End of File ***&quot;
set color to W+
endif
Set color to n/W
@ 22,10 say &quot;N&quot;
@ 22,23 say &quot;P&quot;
@ 22,41 say &quot;0-9&quot;
@ 22,62 say &quot;Q&quot;
set color to w+
@ 22,11 say &quot;ext page&quot;
@ 22,24 say &quot;revious page&quot;
@ 22,45 say &quot;view item&quot;
@ 22,63 say &quot;uit browse&quot;
selection=&quot;Q&quot;
set color to GR+
@ 19,15 say &quot;Please choose a selection from the menu below:&quot; get selection Picture &quot;@!&quot;
read
set color to w+
IF SELECTION=&quot;P&quot;
SKIP -20
ENDIF
IF selection=&quot;Q&quot;
clear
set color to w+
close all
return
ENDIF
IF SELECTION>CHR(47).AND.SELECTION<CHR(58)
skco=-1*(line-(val(selection)))
skip skco
itemnum=stitemnum
itemtot=0
units=stunit
count=0
select b
use spool index spitem
go top
seek(itemnum)
if found()
do while itemnum=spitemnum
if spactive
if units=&quot;FT&quot;
itemtot=itemtot+spftnet
else
itemtot=itemtot+spwnet
endif
endif
count=count+1
skip
enddo
endif
skip -1*count
select a
edt=&quot;&quot;
do while edt<>&quot;Q&quot;
clear
set color to r+
@ 0,9 say &quot;Item Viewing&quot;
set color to w+
@ 2,9 SAY &quot;Item #: &quot;+STITEMNUM
@ 4,11 SAY &quot;Awg : &quot;+STAWG
@ 5,6 SAY &quot;Diameter : &quot;+STDIAMETER
@ 6,10 SAY &quot;Type : &quot;+STWIRETYPE
@ 7,10 SAY &quot;Ohms : &quot;
@ 7,17 SAY STOHMS
@ 9,6 SAY &quot;Price per LB : &quot;
@ 9,21 SAY stpricelb PICTURE &quot;$99,999.99&quot;
@ 10,5 SAY &quot;Price per KFT :&quot;
@ 10,21 say stpricekft PICTURE &quot;$99,999.99&quot;
@ 9,42 say &quot;FT per LB : &quot;
@ 9,54 SAY stftperlb
@ 10,40 say &quot;LBS per KFT : &quot;
@ 10,54 SAY stlbperkft
@ 12,22 say &quot;Prefered Units : &quot;+stunit
@ 13,27 say &quot;Low order : &quot;
@ 13,40 SAY stlowquant
@ 15,13 say &quot;Spool quantity in stock : &quot;
if stunit=&quot;FT&quot;
@ 15,39 say itemtot picture &quot;99,999,999&quot;
@ 15,50 say &quot;Feet&quot;
else
@ 15,39 say itemtot picture &quot;9,999.999&quot;
@ 15,49 say &quot;LBs&quot;
endif
@ 17,15 say &quot;Inventory Cost: &quot;
@ 17,31 say stcost picture &quot;999.9999&quot;
@ 17,41 say &quot;Per &quot;+stcostunit

* @ 18,0 say &quot;Memo:&quot;+memo

set color to n/w
@ 22,1 say &quot;E&quot;
@ 22,10 say &quot;P&quot;
@ 22,20 say &quot;D&quot;
@ 22,30 say &quot;S&quot;
@ 22,40 say &quot;H&quot;
@ 22,50 say &quot;L&quot;
@ 22,60 say &quot;O&quot;
@ 22,70 say &quot;Q&quot;
@ 23,5 say &quot;M&quot;
@ 23,15 say &quot;X&quot;
@ 23,25 SAY &quot;C&quot;

@ 23,40 say &quot;A&quot;

set color to w+
@ 22,2 say &quot;DIT&quot;
@ 22,11 SAY &quot;RINT&quot;
@ 22,21 SAY &quot;ELETE&quot;
@ 22,31 SAY &quot;POOLS&quot;
@ 22,41 say &quot;ISTORY&quot;
@ 22,51 say &quot;ABEL&quot;
@ 22,61 say &quot;N ORDER&quot;
@ 22,71 SAY &quot;UIT&quot;
@ 23,6 say &quot;ASK EDIT&quot;
@ 23,16 say &quot; ORDERS&quot;
@ 23,26 SAY &quot;USTOER ORDERS&quot;

@ 23,41 Say&quot;DDITIONAL COMMENTS&quot;

set color to RG+
edt=&quot;Q&quot;
@ 20,15 SAY &quot;Please choose a selection from the menu below:&quot; get edt picture &quot;@!&quot;
read
do case
case edt=&quot;E&quot;
DO STCKDENT
case edt=&quot;D&quot;
delete
do stckdelr
edt=&quot;Q&quot;
case edt=&quot;P&quot;
do stckrecp
CASE EDT=&quot;S&quot;
DO splstck
case edt=&quot;L&quot;
do spllabel
case edt=&quot;O&quot;
do stckonor
case edt=&quot;H&quot;
do stckhist
case edt=&quot;X&quot;
do stckxodr
case edt=&quot;M&quot;
do stmdent
CASE EDT=&quot;C&quot;
DO STCKCUST

CASE EDT=&quot;A&quot;
Do StockMem

endcase
enddo
goto toprec
endif
enddo
return
___________________________________________________________
And here is the ItemCost.qpr:

SELECT PO.POUNSTNUM AS item_number,;
PO.PO AS po,;
PO.PODATE_ORD AS date_ordered,;
PO.VENNAME AS vendor,;
STOCK.STAWG AS ga,;
STOCK.STWIRETYPE AS description,;
PO.POUNITCOST AS unit_cost,;
PO.POUNITS as units,;
STOCK.STFTPERLB AS ft_per_lb;
FROM PO, STOCK;
WHERE PO.POUNSTNUM == STOCK.STITEMNUM;
AND PO.POUNITCOST > 0;
ORDER BY PO.POUNSTNUM, PO.PO DESC;
INTO CURSOR result1

SELECT result1
INDEX ON item_number TAG key1 UNIQUE
___________________________________________________________

Any ideas?
(Just for the record, I did not write the .prg It was in place before I was hired. If it were my choice, I would have made this in VB.)
,John