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 =3D Hret98.disdat ;
ON Revdata.mrecno =3D Hret98.mrecno ;
ON Revdata.hospnm =3D Hret98.hospnm ;
ON Revdata.ptctln =3D 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 =3D "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
charset="windows-1252"
Do you have CDX tags for all the join conditions i.e. Revdata.disdat,
Hret98.disdat, Revdata.mrecno, Hret98.mrecno, Revdata.hospnm, Hret98.hospnm,
Revdata.ptctln, Hret98.ptctln and Revdata.hcfa_cc ?
That's where I would start....
mj
> -----Original Message-----
> From: NICOLE MORTELLO [mailto:NMORTELLO@njha.com]
> Sent: Friday, March 31, 2000 9:16 AM
> To: Multiple recipients of ProFox
> 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:
> http://leafe.com/mailListMaint.html
>
>
>
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 =3D Hret98.disdat ;
ON Revdata.mrecno =3D Hret98.mrecno ;
ON Revdata.hospnm =3D Hret98.hospnm ;
ON Revdata.ptctln =3D 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 =3D "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:
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:
This is not on a network. This is all on my second hard drive. I saw the =
same thing you did with the joining and changed it to what you have. With =
the HCFA_CC I want all entries that are NOT =3D "TOT". There are about a =
dozen or so other things that HCFA_CC can equal and I want all of =
those.=20
Nicole
>>> "Cindy Winegarden" <cindyw@acpub.duke.edu> 03/31 12:45 PM >>>
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 =3D Hret98.disdat ;
AND Revdata.mrecno =3D Hret98.mrecno ;
AND Revdata.hospnm =3D Hret98.hospnm ;
AND Revdata.ptctln =3D 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=20
----- 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 =3D Hret98.disdat ;
ON Revdata.mrecno =3D Hret98.mrecno ;
ON Revdata.hospnm =3D Hret98.hospnm ;
ON Revdata.ptctln =3D 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 =3D "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=20
----------------------------------------
Subscription maintenance at:
http://leafe.com/mailListMaint.html=20
----------------------------------------
Subscription maintenance at:
http://leafe.com/mailListMaint.html=20
=20
Oops - read
Revdata.hcfa_cc <> "TOT"
as
Revdata.hcfa_cc = "TOT"
I guess I needed lunch!
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 1:12 PM
Subject: Re: VFP5.0 - SQL error creating a view
This is not on a network. This is all on my second hard drive. I saw
the same thing you did with the joining and changed it to what you
have. With the HCFA_CC I want all entries that are NOT = "TOT".
There are about a dozen or so other things that HCFA_CC can equal and
I want all of those.
Nicole
>>> "Cindy Winegarden" <cindyw@acpub.duke.edu> 03/31 12:45 PM >>>
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:
http://leafe.com/mailListMaint.html
----------------------------------------
Subscription maintenance at:
http://leafe.com/mailListMaint.html
----------------------------------------
Subscription maintenance at:
I am having a problem with running the SQL now because of lack of disk =
space. The end result I need is one entry per rev code per patient type. =
There are a few dozen rev codes and 5 patient types. Can I get the SQL to =
create the view and calculate the count and sum I need in one pass and =
maybe use less space? Or is it still going to create a temp file for the =
view and then do the calculation?
Nicole
>>> "Cindy Winegarden" <cindyw@acpub.duke.edu> 03/31 12:45 PM >>>
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 =3D Hret98.disdat ;
AND Revdata.mrecno =3D Hret98.mrecno ;
AND Revdata.hospnm =3D Hret98.hospnm ;
AND Revdata.ptctln =3D 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=20
----- 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 =3D Hret98.disdat ;
ON Revdata.mrecno =3D Hret98.mrecno ;
ON Revdata.hospnm =3D Hret98.hospnm ;
ON Revdata.ptctln =3D 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 =3D "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=20
----------------------------------------
Subscription maintenance at:
http://leafe.com/mailListMaint.html=20
----------------------------------------
Subscription maintenance at:
http://leafe.com/mailListMaint.html=20
=20