I think this is an SQL question...

Hello and thanks in advance for the help...

I have two cursors which are the result of two different queries. Each table
has the following fields:

Folio N (6,0)
Code C(4)

In a perfect world they are identical. When the are not I need the following
two additional cursors:

All records in the first cursor that are missing from the second
All records from the second cursor that are missing from the first

Right now I'm doing this using an index: STR(FOLIO,6,0)+CODE for both
tables.
Then I setup a relation from one to the other: SET RELATION TO
STR(FOLIO,6,0)+CODE INTO TWO
Finally I use a copy command: COPY TO NOTINONE FOR FOLIO <> TWO.FOLIO

This works but copy will only create tables. There doesn't appear to be a
"COPY TO CURSOR"

I imagine that this could be done with another SQL query but I'm not sure
how to setup that query.

Thanks again.
Jeff

trw7at
Tue Apr 04 11:02:27 CDT 2006

Jeff Grippe seemed to utter in news:12353ev2jaupqf8@news.supernews.com:

> Hello and thanks in advance for the help...
>
> I have two cursors which are the result of two different queries. Each
> table has the following fields:
>
> Folio N (6,0)
> Code C(4)
>
> In a perfect world they are identical. When the are not I need the
> following two additional cursors:
>
> All records in the first cursor that are missing from the second
> All records from the second cursor that are missing from the first
>
> Right now I'm doing this using an index: STR(FOLIO,6,0)+CODE for both
> tables.
> Then I setup a relation from one to the other: SET RELATION TO
> STR(FOLIO,6,0)+CODE INTO TWO
> Finally I use a copy command: COPY TO NOTINONE FOR FOLIO <> TWO.FOLIO
>
> This works but copy will only create tables. There doesn't appear to be
> a "COPY TO CURSOR"
>
> I imagine that this could be done with another SQL query but I'm not
> sure how to setup that query.

SELECT * ;
INTO CURSOR NOTINTWO ;
FROM ONE ;
WHERE NOT EXISTS ( ;
SELECT 'X' ;
FROM TWO ;
WHERE STR(TWO.FOLIO,6,0)+TWO.CODE = STR(ONE.FOLIO,6,0)+ONE.CODE ;
)

And the same in the other direction. There are a couple
different ways to get the same information.

-- TRW
_______________________________________
t r w 7
at
i x dot n e t c o m dot c o m
_______________________________________

Jeff
Tue Apr 04 12:32:48 CDT 2006

Tim,

Thanks for the help.

As I understood it, it didn't work. Here is the code that I used based on
your response....

SELECT ;
* ;
INTO CURSOR ;
MissingFromControls ;
FROM ;
FromFactor ;
WHERE ;
NOT EXISTS ( ;
SELECT ;
'X' ;
FROM ;
FromControls ;
WHERE ;
STR(FromControls.Folio,6,0) + FromControls.Code = ;
STR(FromFactor.Folio,6,0) + FromFactor.Code ;
)

the error returned was SQL: Error correlating fields

thanks again

Jeff

"Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
news:Xns979B5C38BBE88timwitortwrotethis@81.174.50.80...
> Jeff Grippe seemed to utter in news:12353ev2jaupqf8@news.supernews.com:
>
>> Hello and thanks in advance for the help...
>>
>> I have two cursors which are the result of two different queries. Each
>> table has the following fields:
>>
>> Folio N (6,0)
>> Code C(4)
>>
>> In a perfect world they are identical. When the are not I need the
>> following two additional cursors:
>>
>> All records in the first cursor that are missing from the second
>> All records from the second cursor that are missing from the first
>>
>> Right now I'm doing this using an index: STR(FOLIO,6,0)+CODE for both
>> tables.
>> Then I setup a relation from one to the other: SET RELATION TO
>> STR(FOLIO,6,0)+CODE INTO TWO
>> Finally I use a copy command: COPY TO NOTINONE FOR FOLIO <> TWO.FOLIO
>>
>> This works but copy will only create tables. There doesn't appear to be
>> a "COPY TO CURSOR"
>>
>> I imagine that this could be done with another SQL query but I'm not
>> sure how to setup that query.
>
> SELECT * ;
> INTO CURSOR NOTINTWO ;
> FROM ONE ;
> WHERE NOT EXISTS ( ;
> SELECT 'X' ;
> FROM TWO ;
> WHERE STR(TWO.FOLIO,6,0)+TWO.CODE = STR(ONE.FOLIO,6,0)+ONE.CODE ;
> )
>
> And the same in the other direction. There are a couple
> different ways to get the same information.
>
> -- TRW
> _______________________________________
> t r w 7
> at
> i x dot n e t c o m dot c o m
> _______________________________________

Cindy
Tue Apr 04 13:32:25 CDT 2006

Hi Jeff,

Here's another way to find rows that are in A but not in B:

Select A.* From A ;
Left Join B On A.Whatever = B.Whatever ;
Where B.AnyField Is Null

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Jeff Grippe" <jgrippe@hilldun.com> wrote in message
news:1235bi0fmtk490d@news.supernews.com...
> Tim,
>
> Thanks for the help.
>
> As I understood it, it didn't work. Here is the code that I used based on
> your response....

Jeff
Tue Apr 04 14:20:59 CDT 2006

Thank you. It worked perfectly. Joins is one of the weak spots of my SQL.
Could you recommend something to read that would fill in the gaps?

Thanks again
"Cindy Winegarden" <cindy_winegarden@msn.com> wrote in message
news:er5gFaBWGHA.5588@TK2MSFTNGP09.phx.gbl...
> Hi Jeff,
>
> Here's another way to find rows that are in A but not in B:
>
> Select A.* From A ;
> Left Join B On A.Whatever = B.Whatever ;
> Where B.AnyField Is Null
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Jeff Grippe" <jgrippe@hilldun.com> wrote in message
> news:1235bi0fmtk490d@news.supernews.com...
>> Tim,
>>
>> Thanks for the help.
>>
>> As I understood it, it didn't work. Here is the code that I used based on
>> your response....
>
>

AA
Tue Apr 04 12:33:55 CDT 2006

One way is to get the unmatched rows from Table1:
SELECT Table1.* FROM Table1 LEFT JOIN Table2 ;
ON Table1.folio=Table2.folio AND Table1.code=Table2.code ;
WHERE Table2.code IS NULL

-Anders


"Jeff Grippe" <jgrippe@hilldun.com> skrev i meddelandet
news:12353ev2jaupqf8@news.supernews.com...
> Hello and thanks in advance for the help...
>
> I have two cursors which are the result of two different queries. Each
> table has the following fields:
>
> Folio N (6,0)
> Code C(4)
>
> In a perfect world they are identical. When the are not I need the
> following two additional cursors:
>
> All records in the first cursor that are missing from the second
> All records from the second cursor that are missing from the first
>
> Right now I'm doing this using an index: STR(FOLIO,6,0)+CODE for both
> tables.
> Then I setup a relation from one to the other: SET RELATION TO
> STR(FOLIO,6,0)+CODE INTO TWO
> Finally I use a copy command: COPY TO NOTINONE FOR FOLIO <> TWO.FOLIO
>
> This works but copy will only create tables. There doesn't appear to be a
> "COPY TO CURSOR"
>
> I imagine that this could be done with another SQL query but I'm not sure
> how to setup that query.
>
> Thanks again.
> Jeff
>
>

David
Tue Apr 04 19:44:20 CDT 2006

For reading, see
-
http://www.hentzenwerke.com/catalog/tamingvfpsql.htm
-
...edited by Anders Altberg!
-
David Aman

Andrew
Wed Apr 05 03:08:36 CDT 2006

"Jeff Grippe" <jgrippe@hilldun.com> wrote in message
news:1235bi0fmtk490d@news.supernews.com...
> the error returned was SQL: Error correlating fields

In your join conditions you need

...
WHERE ;
FromControls.Folio=FromFactor.Folio ;
AND FromControls.Code=FromFactor.Code ;
...

--
regards
Andrew Howell

AA
Wed Apr 05 13:46:53 CDT 2006

Jeff,

You've run into a limitation imposed on subqueries in VFP: one can't use
expressions on both sides of the predicate.
Change the WHERE clause in the correlated subquery to
(... WHERE ;
FromControls.Folio=FromFactor.folio AND;
FromControls.Code =FromFactor.Code )

Looks and reads better too, I think.
-Anders

"Jeff Grippe" <jgrippe@hilldun.com> skrev i meddelandet
news:1235bi0fmtk490d@news.supernews.com...
> Tim,
>
> Thanks for the help.
>
> As I understood it, it didn't work. Here is the code that I used based on
> your response....
>
> SELECT ;
> * ;
> INTO CURSOR ;
> MissingFromControls ;
> FROM ;
> FromFactor ;
> WHERE ;
> NOT EXISTS ( ;
> SELECT ;
> 'X' ;
> FROM ;
> FromControls ;
> WHERE ;
> STR(FromControls.Folio,6,0) + FromControls.Code = ;
> STR(FromFactor.Folio,6,0) + FromFactor.Code ;
> )
>
> the error returned was SQL: Error correlating fields
>
> thanks again
>
> Jeff
>
> "Tim Witort" <trw7at@ixdot.netcomdotcom> wrote in message
> news:Xns979B5C38BBE88timwitortwrotethis@81.174.50.80...
>> Jeff Grippe seemed to utter in news:12353ev2jaupqf8@news.supernews.com:
>>
>>> Hello and thanks in advance for the help...
>>>
>>> I have two cursors which are the result of two different queries. Each
>>> table has the following fields:
>>>
>>> Folio N (6,0)
>>> Code C(4)
>>>
>>> In a perfect world they are identical. When the are not I need the
>>> following two additional cursors:
>>>
>>> All records in the first cursor that are missing from the second
>>> All records from the second cursor that are missing from the first
>>>
>>> Right now I'm doing this using an index: STR(FOLIO,6,0)+CODE for both
>>> tables.
>>> Then I setup a relation from one to the other: SET RELATION TO
>>> STR(FOLIO,6,0)+CODE INTO TWO
>>> Finally I use a copy command: COPY TO NOTINONE FOR FOLIO <> TWO.FOLIO
>>>
>>> This works but copy will only create tables. There doesn't appear to be
>>> a "COPY TO CURSOR"
>>>
>>> I imagine that this could be done with another SQL query but I'm not
>>> sure how to setup that query.
>>
>> SELECT * ;
>> INTO CURSOR NOTINTWO ;
>> FROM ONE ;
>> WHERE NOT EXISTS ( ;
>> SELECT 'X' ;
>> FROM TWO ;
>> WHERE STR(TWO.FOLIO,6,0)+TWO.CODE = STR(ONE.FOLIO,6,0)+ONE.CODE ;
>> )
>>
>> And the same in the other direction. There are a couple
>> different ways to get the same information.
>>
>> -- TRW
>> _______________________________________
>> t r w 7
>> at
>> i x dot n e t c o m dot c o m
>> _______________________________________
>
>