help with Scan

[psvialli]

Need a litter help on searching tables?

I have two tables my main table that holds a certs and premium i.e. <cert> 00001 <Value> £15,250.00 <Premium> £3.00.

I need to search another table <premiums> and check the premium to make sure it is correct, this table holds values and costs as follows:-

£5000 £1.00
£10,000 £2.00
£15,000 £3.00
£20,000 £4.00
etc...

These are Max values so if the value in my main table is £10,001 I need it to search the premium table and find £3.00 as it has gone over the £10,000 and if the value is £9,999 is should pull back £2.00

If the premium in the main table matches the premium in the premium lookup table carry on if it does not mark the flag fields as .T. so I know that certificate has failed the match.

So I need a scan /endscan I presume? But not sure how to do it

Many thanks
Paul

 

[bborissov]

CREATE CURSOR Certs (Cert C(5), Value Y, Premium Y)
INSERT INTO Certs VALUES([00001],15250,3)
CREATE CURSOR Premiums (Value Y, Premium Y)
INSERT INTO Premiums  VALUES (5000,1)
INSERT INTO Premiums  VALUES (10000,2)
INSERT INTO Premiums  VALUES (15000,3)
INSERT INTO Premiums  VALUES (20000,2)
INDEX ON Value  TAG Premiums
SELECT Certs
SET NEAR ON
SCAN
   SEEK Certs.Value IN Premiums
   IF Premiums.Premium == Certs.Premium
      *** We have exact match
   ELSE
      *** Nope, premiums didn't match 
   ENDIF
ENDSCAN
SET NEAR OFF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[TheRambler]

I would say it is easier to do it with an array and a UDF, like this:

SELECT value, premium ;
   FROM premiums ;
   INTO ARRAY aIntervals

USE certs
REPLACE ALL flagField WITH search(certs.value)

FUNCTION search
PARAMETER tnValue
FOR lnI=1 to ALEN(aIntervals,1)
    IF tnValue <= aIntervals(lnI,1)
       RETURN(aIntervals(lnI,2)==certs.premium)
    ENDIF
ENDFOR

If you use SCAN/ENDSCAN you would probably have to use CASE/ENDCASE or nested IFs; it could be done using IIF() or ICASE() functions as well, but I have not tried to do so.

 

[JimWWinter]

Something like:

SELECT Certs
SCAN
   SELECT Premium, MAX(InsuranceValue) ;
     FROM Premium ;
    WHERE Premium.InsuranceValue <= Certs.InsuranceValue ;
    GROUP BY Premium
     INTO CURSOR TestPremium
   IF TestPremium # Certs.Premium
     REPLACE Certs.lBadPremium WITH .T.
   ENDIF 
ENDSCAN

Regards,
Jim

 

[psvialli]

Many thanks to you all for you help it is much appreciated!

Paul

 

[psvialli]

Another question on this if possible.

All of the above coding works really good but i have just hit another problem that i did not think of.

My premium table holds a Scheme FK

VAlue Premium SCheme_FK
£5000 £1.00 1
£10,000 £2.00 1
£5,000 £3.00 2
£20,000 £4.00 1
£15,000 £4.00 2
£20,000 £4.00 1

And my Certs holds this as well so all the certs will have a scheme_fk. So I also need to make sure that the Scheme_FK matches as well i.e. if certs is using scheme_FK 1 should only check scheme_fk 1 for the correct Valkues and premiums, the next cert could be 20 so only find Scheme_fk 20 in premiums and so on .

Have tried to get this working but have had no joy

Thanks advance

Paul

 

[JimWWinter]

OK, how about:

SELECT Certs
SCAN
   SELECT Premium, MAX(InsuranceValue) ;
     FROM Premium ;
    WHERE Premium.InsuranceValue <= Certs.InsuranceValue ;
      AND Premium.Scheme_FK = Certs.Scheme_FK ;
    GROUP BY Premium
     INTO CURSOR TestPremium
   IF TestPremium # Certs.Premium
     REPLACE Certs.lBadPremium WITH .T.
   ENDIF
ENDSCAN

Regards,
Jim

 

[psvialli]

Hi Jim,

I just tried to use this code but for some reason I can not get it to work, it generates the Cursor, but then when it goes through the Certificates it only looks at the first record in the TestPremium Cursor. So it always reports a badpremium as it does not look through the Cursor , how do I get it to look through the cursor to find a match ?

Thanks

Paul

 

[JimWWinter]

Sorry about that! Maybe this will work a little better.

LOCAL nValue, nScheme
SELECT Certs
SCAN
   nValue = Certs.InsuranceValue
   nScheme = Certs.Scheme_FK
   SELECT Premium, MAX(InsuranceValue) ;
     FROM Premium ;
    WHERE Premium.InsuranceValue <= nValue ;
      AND Premium.Scheme_FK = nScheme ;
    GROUP BY Premium
     INTO CURSOR TestPremium
   IF TestPremium # Certs.Premium
     REPLACE Certs.lBadPremium WITH .T.
   ENDIF
ENDSCAN

Regards,
Jim