intersect SQL query in FoxPro 1

[DSaba10]

I'm trying to use an intersect query with foxpro, but I'm getting a syntax error, so I'm guessing foxpro doesn't like intersects. Does anyone know a better way to accomplish what I'm trying to do below?

set defa to d:\supporte\loader\00dataanalysis\vfptest

mStanConn = SQLCONNECT('server', 'user', 'pword')
mResultPB = SQLEXEC(mStanConn, "select id_number, interface_id_number, user_fld5 from stan08.prospect_base")
copy to STAN08_PB
mResultPP = SQLEXEC(mSTanConn, "select id_number from stan08.pledge_payment")
copy to STAN08_PP

SQLDISCONNECT(mStanConn)

**ACCEPT "Enter Data Cycle (e.g. 7209):  " to mDataCycle
? ' '

select interface_id_number, user_fld5 from STAN08_PB ;
inner join STAN08_PP (id_number) ;
intersect ;
select interface_id_number, usrfield5 from origstru_8294 ;
where usrfield6 <> 'SFR'

browse

Thanks for your help!
-Doug

 

[Mike Lewis]

Doug,

I'm guessing foxpro doesn't like intersects.

You guessed right. But why guess? Why don't you just consult the Help?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[DSaba10]

For some strange reason my company has removed the help files from foxpro. So I'm not able to go that route.

 

[Olaf Doschke]

Do the intersect in sql server. Intersect is not in foxpro SQL syntax. But as intersect is a special inner join on the condition of all fields or the whole record being equal, you can write in this case:

select * from (select interface_id_number, user_fld5 from STAN08_PB pb;
inner join STAN08_PP pp on PB.id_number = pp.id_number) ppb ;
inner join;
(select interface_id_number, usrfield5 as user_fld5 from origstru_8294 where usrfield6 <> 'SFR') org;
on ppb.interface_id_number = org.interface_id_number and ppb.user_fld5 = org.user_fld5

Bye, Olaf.

 

[Stella740pl]

Doug,

Since you have access to Internet, you can use Help on Microsoft's MSDN site. It's here:

http://msdn2.microsoft.com/en-us/library/ms950411.aspx

 

[Mike Lewis]

Interestingly, I see that Intersect is listed as a reserved word in VFP. But it certainly gives syntax errors in a SELECT.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[DSaba10]

Olaf's method definitely makes sense, but I'm coming up with a syntax error when running it that I can't seem to debug. My eyes may be getting a little tired at this point.

I'm currently using VFP 6, does anyone know if intersect works in 9 at all?

-Doug

 

[Mike Lewis]

Doug,

does anyone know if intersect works in 9 at all?

'Fraid not.

But Olaf's code should run fine in 9.0. It uses projections, which are not supported in 6.0, but are in 9.0.

In 6.0, I guess you would just have to break it down into three separate statements, with the results of each of the sub-selects going to a temporary cursor.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Hi Doug,

thanks for the star. If you're bound to VFP6 I'd do the intersect in sql server, as long as you can also get the data of origstru_8294 from there.

In general intersects of partial rsults could simply be avoided by selecting with a stronger where clause in the first place.

In vfp9 another helpful function is sys(2017) - record checksum. You could compute that for each record and the inner join on the sys(2017) result to simulate intersect also in more complex cases with more fields involved.

Bye, Olaf.

 

[DSaba10]

Unfortunately the data from origstru_8294 is a stand alone VFP table. I've changed my code to this:

set defa to d:\supporte\loader\00dataanalysis\vfptest

mStanConn = SQLCONNECT('server', 'user', 'pword')
mResultPB = SQLEXEC(mStanConn, "select interface_id_number, user_fld5 from stan08.prospect_base inner join stan08.pledge_payment using(id_number)")
copy to STAN08_PB

SQLDISCONNECT(mStanConn)

select Interface_id_number from STAN08_PB pb;
inner join origstru_8294 org on pb.Interface_id_number = org.id ;
where pb.user_fld5 in (select userfld5 as user_fld5 from origstru_8294);
and org.usrfield6 <> 'SFR'

browse

Which solves some of the issue. I no longer need to create the intersect this way, but when I run it this time, I'm getting a message that field interface_id_number does not exist... which it clearly does. Baby steps!

-Doug

 

[Olaf Doschke]

Hi Doug,

In your first post you selected

mResultPP = SQLEXEC(mSTanConn, "select id_number from stan08.pledge_payment")

So where is interface_id_number in stan08?

In my select you'd inner join and get NULL values of interface_id_number for the records of STAN08_PP.

Bye, Olaf.

 

[DSaba10]

Well after stewing on it all night I figured out the entire thing with exactly what I wanted to accomplish.

**-------------------------------------------------------------------
** @Author: Douglas Saba
** @copyright: 2008
**   Description:
**   - This program will do ... What I want it to.
**-------------------------------------------------------------------

close all

set defa to d:\supporte\loader\00dataanalysis\vfptest

mStanConn = SQLCONNECT('server', 'user', 'pword')
mResultPB = SQLEXEC(mStanConn, "select interface_id_number, user_fld5 from stan08.prospect_base inner join stan08.pledge_payment using(id_number)")
copy to STAN08_PB

USE STAN08_PB EXCLUSIVE

INDEX ON interface_ TAG interid

SQLDISCONNECT(mStanConn)
    
CLOSE all
**CLEAR

accept "Is Userfld10 currently blank (e.g. Y or N): " to mBlank

set talk off
if upper(substr(mBlank,1,1))="Y"
else 
	set talk on
	? ' Cancelling program.'
	close all
	cancel
endif

set talk on
? ' '
? '  Processing.'
? ' '

set talk off

set century on
store substr(dtoc(date()),7,4)+substr(dtoc(date()),1,2)+ substr(dtoc(date()),4,2) to mCurrDate
set century off

store 'STAN08_PB' to mMainFile
store 'origstru_8294' to mUpdateFile  
store 'origstru_8294_exclusion_' + alltrim(mCurrDate) to mEndFile

** update records
SELECT 1
	USE &mMainFile order interid
SELECT 2
	USE &mUpdateFile order interid

DO WHILE .NOT. EOF()

   SELECT 1
      IF interface_ = ' '
         SKIP
         LOOP
      ENDIF

      STORE interface_ TO mID
      
      SELECT 2
         SCAN for interid = mID
            REPLACE userfld10 WITH 'Y'
         ENDSCAN    
         
      SELECT 1
     
      SKIP
      
ENDDO
  
set talk on
? ' '
? '  Removing excluded records.'
? ' '
set talk off

close all
use &mUpdateFile 
copy to &mEndFile for len(alltrim(userfld10))>0
dele for len(alltrim(userfld10))>0
pack


CLOSE ALL
SET TALK ON

? '  Done.'
? '  '

The program will create a temp table for the sql query results, then compare and create an exclusions table for any data that matches.

Thanks everyone for ideas and tips. Its much easier to figure things out when you can bounce ideas off of others.

-Doug