| Author |
Still having problems with join missing records
|
|
| Dan Anderson 2005-04-04, 8:03 pm |
| db+ This join works for the majority of the records but not for all. That
is a major problem. At issue is a field in 'apcustom.dbf' called 'Data1.'
I have checked to be certain and the records DO exist in the 'apcustom.dbf'
table. But for some reason a few of them are not joining. This field
contains critical information for insurance and must be available. Needless
to say the records in each table are not in any kind of order. Is there a
way to indicate an index in an sql statement? I really need this to work.
Can anyone offer a suggestion?
query1 = new query()
query1.sql = 'SELECT * ' + ;
'FROM "PennAm1.dbf" PA ' + ;
'LEFT OUTER JOIN "APCUSTOM.dbf" AP ' +;
'ON (PA.POL_IDX = AP.POL_IDX)' +;
'ORDER BY POL_IDX'
query1.active = true
u = new UpdateSet()
u.source = query1.rowset
u.destination = " K:\applications\PA\U
NIT1.dbf"
u.copy()
--
Dan Anderson
UBI Processing Dept.
andersond@ubinc.com
800-444-4824 ext 101
| |
| Michael Nuwer [dBVIPS] 2005-04-04, 8:03 pm |
| Dan Anderson wrote:
> db+ This join works for the majority of the records but not for all. That
> is a major problem. At issue is a field in 'apcustom.dbf' called 'Data1.'
> I have checked to be certain and the records DO exist in the 'apcustom.dbf'
> table. But for some reason a few of them are not joining. This field
> contains critical information for insurance and must be available. Needless
> to say the records in each table are not in any kind of order. Is there a
> way to indicate an index in an sql statement? I really need this to work.
> Can anyone offer a suggestion?
SQL is not one of my strengths. You might try another newsgroup where
the SQL gurus hangout. If you can't get the Join to work as you need,
perhaps a standard dBASE parent/child relation would work.
You can not use an index with an sql join. dBASE creates a temp file
when it runs the SELECT and that file has no index.
>
> query1 = new query()
> query1.sql = 'SELECT * ' + ;
> 'FROM "PennAm1.dbf" PA ' + ;
> 'LEFT OUTER JOIN "APCUSTOM.dbf" AP ' +;
> 'ON (PA.POL_IDX = AP.POL_IDX)' +;
In the above line the quote mark does not have a space after the ).
This will render as:
= AP.POL_IDX)ORDER BY POL_IDX
> 'ORDER BY POL_IDX'
> query1.active = true
>
> u = new UpdateSet()
> u.source = query1.rowset
> u.destination = " K:\applications\PA\U
NIT1.dbf"
> u.copy()
>
--
Michael Nuwer
http://www.ChelseaData.ca/dLearn/
http://www.nuwermj.potsdam.edu/dSamples/
| |
| Claus Mygind 2005-04-04, 8:03 pm |
| Early on when I was starting to develop web apps, I ran across the same
problem with Joins and other complicated expressions in the select
statement. I was advised by the people at dBASE stick to as simple select
statement and use indexes instead. That has worked for me. If you are
going to some sort of SQL server and tables that would be the time to
develop more complex select statments as SQL tables cannot use expressions
in their indexes.
"Dan Anderson" <andersond@ubinc.com> wrote in message
news:lNCHwqH2EHA.1488@news-server...
> db+ This join works for the majority of the records but not for all.
> That is a major problem. At issue is a field in 'apcustom.dbf' called
> 'Data1.' I have checked to be certain and the records DO exist in the
> 'apcustom.dbf' table. But for some reason a few of them are not joining.
> This field contains critical information for insurance and must be
> available. Needless to say the records in each table are not in any kind
> of order. Is there a way to indicate an index in an sql statement? I
> really need this to work. Can anyone offer a suggestion?
>
> query1 = new query()
> query1.sql = 'SELECT * ' + ;
> 'FROM "PennAm1.dbf" PA ' + ;
> 'LEFT OUTER JOIN "APCUSTOM.dbf" AP ' +;
> 'ON (PA.POL_IDX = AP.POL_IDX)' +;
> 'ORDER BY POL_IDX'
> query1.active = true
>
> u = new UpdateSet()
> u.source = query1.rowset
> u.destination = " K:\applications\PA\U
NIT1.dbf"
> u.copy()
>
> --
> Dan Anderson
> UBI Processing Dept.
> andersond@ubinc.com
> 800-444-4824 ext 101
>
|
|
|
|