>Thank you. Yes I can.
>
>Here is an example with the three macro expressions (the first three lines of the WHERE) changed into their text values.
>
>select medDetail.MedDtlKey ;
> , medDetail.PaNumber ;
> , medDetail.GroupKey ;
> , meddetail.GroupSuff ;
> , medDetail.InOutPat ;
> , padr( patient.LASTNAME - ;
> ( ", " + patient.FIRSTNAME - ;
> ( " " + patient.MIDINITIAL ) ), 40, " " ) ;
> AS "PtName" ;
> , patient.SSN ;
> , patient.Gender + " - " + stuff( dtoc( patient.DOB ), 7, 2, "" ) ;
> AS "GendDOB" ;
> , medDetail.RelaToIns ;
> , left( TypePick.PickDesc, 25 ) AS "Service" ;
> , left( CertKey.PickDesc, 12 ) AS "Status" ;
> , nvl( subscriber.SSN, {} ) AS "SubscrSSN" ;
> , space(50) AS "Provider" ;
> , space(30) AS "ProvStreet" ;
> , space(15) AS "ProvCity" ;
> , {} AS "ApprovFrom" ;
> , {} AS "ApprovTo" ;
> , AsInteger.IntFld AS "NbrVisits" ;
> , nvl( procDetail.ProcCode, space(6) ) AS "ProcCode" ;
> from "Patient" ;
> , "Subscriber" ;
> , "MedCert" ;
> , "MedMgmt!PickTabl" AS TypePick ;
> , "MedMgmt!PickTabl" AS CertKey ;
> , "MedDetail" ;
> left outer join "ProcDetail" on ;
> ( procDetail.ParentKey == medDetail.MedDtlKey ) AND ;
> ( procDetail.IsPrimary ) ;
> where ( meddetail.InOutPat=="O" ) AND ;
> ( upper(patient.LASTNAME)=="FAKELASTNAME " ) AND ;
> ( patient.DOB==date(1900,01,01) ) AND ;
> ( medDetail.PatientKey == patient.PatientKey ) AND ;
> ( subscriber.SubscrKey == medDetail.Subscriber ) AND ;
> ( medCert.MedDtlKey == medDetail.MedDtlKey ) AND ;
> ( TypePick.PickKey == medCert.TypeServ ) AND ;
> ( CertKey.PickKey == medCert.CertStatus ) ;
> into cursor "PriorAuth1"
>
>
>Thank you again.
>
>Spencer
I would suggest that you actually define the join conditions for each of the other tables defined in the query. It sounds like your looking at many more records than you want to because of no optimization and cartisian joins. Also I would recommend never using == unless you have values where you can get matches you don't want without them. Exact matches always seem to go much slower in my experience.
Cy Welch
Senior Programmer/Analyst
MetSYS Inc