Author: Cindy Winegarden
Posted: 2000-03-31 at 12:45:21
Nicole,
Matt's comments about indexes are important.
You appear to join Hret to RevData 3 times, when I think you really
want to join on 4 fields. Also, returning Revdata.hcfa_cc may be
optional since you know it is always "TOT", but it's nice to make
sure. Returning a lot extra data can slow things down on a slow
network.
Will this do?
SELECT Revdata.revcode, Revdata.hcfa_cc, ;
Revdata.dutnum, Revdata.cnttot, ;
Hret98.flag93;
FROM hret!revdata INNER JOIN hret!hret98 ;
ON Revdata.disdat = Hret98.disdat ;
AND Revdata.mrecno = Hret98.mrecno ;
AND Revdata.hospnm = Hret98.hospnm ;
AND Revdata.ptctln = Hret98.ptctln ;
WHERE Revdata.hcfa_cc <> "TOT" ;
ORDER BY Revdata.revcode, Hret98.flag93
Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro
Duke Children's Information Systems
Duke University Medical Center
cindyw@duke.edu
----- Original Message -----
From: "NICOLE MORTELLO" <NMORTELLO@njha.com>
To: "Multiple recipients of ProFox" <profox@leafe.com>
Sent: Friday, March 31, 2000 12:15 PM
Subject: Re: VFP5.0 - SQL error creating a view
I found the error, two fields that should have been the same type were
not so I fixed it. Now what I want to know is there a better way to
write this SQL so that it does not take so long? I am dealing with a
lot of data (one file has 170000+ records, the other has 1.4 million
records.) It has been running for about 5 minutes and seems to take
over my computer. I do have limited hard drive space on the drive I
am using. Can this be the cause of the slow processing?
Nicole
>>> "NICOLE MORTELLO" <NMORTELLO@njha.com> 03/31 12:06 PM >>>
Hi All,
I am trying to create a view with the following code:
SELECT Revdata.revcode, Revdata.hcfa_cc, Revdata.dutnum,
Revdata.cnttot,;
Hret98.flag93;
FROM hret!revdata INNER JOIN hret!hret98;
INNER JOIN hret!revdata;
INNER JOIN hret!revdata;
INNER JOIN hret!revdata ;
ON Revdata.disdat = Hret98.disdat ;
ON Revdata.mrecno = Hret98.mrecno ;
ON Revdata.hospnm = Hret98.hospnm ;
ON Revdata.ptctln = Hret98.ptctln;
WHERE Revdata.hcfa_cc <> "TOT";
ORDER BY Revdata.revcode, Hret98.flag93
This code is from the View Wizard, I am not very good with SQL yet so
I still use the wizard a lot. I get a message data type mismatch.
Can someone tell me where?
The view is supposed to take two file and pull all from the first file
except if HCFA_CC = "TOT" and one field from the second file if the
HospNm, Ptctln, MRecNo and DisDat are the same in the two files. The
first file can contain multiple occurrences but the second file should
only contain one occurrence.
TIA
Nicole
----------------------------------------
Subscription maintenance at:
http://leafe.com/mailListMaint.html
----------------------------------------
Subscription maintenance at: