Any clever coders out there

[Peterg]

James
Possibly but i am not that good at sql. Any ideas wher eot look

Thanks
Peter

[Antonio Linares]

Peter,

You should use AND and OR in your SQL statement and your SQL server will optimize it to solve it as fast as possible

[James Bott]

Peter,

>select * from skippric
where site_id = mSiteID and sp_custno = mcustacc and uniq_nbr = mUniwnbr and sp_contype = mContainer and sp_waste = mWaste and
sp_effdate<=mDate

Isn't the above going to return a recordset? Are all possible matches in this recordset? If not, then can you loosen up the query to include all possible matches?

>but if this fails then it should test for all other combinations until there is nothing left to test.

What do you mean by other combinations? When is a record acceptable?

James

[Peterg]

The result should only return one record which should be the best match

[James Bott]

Peter,

>sp_effdate<=mDate

But when you are using something like the above, it would seem there could be multiple records with that criteria.

How do you define a best match?

What is an acceptable match, and what isn't? I.E. which criteria are musts, and which are not?

I am having a hard time grasping what you are doing. I have done some complex pricing schemes before but they were all either matching or not matching; I have never done a best match pricing.

James

[Peterg]

I have created the following stored procedure which works but is just as slow as 32 seeks

Any ideas

Use iwscompa
go

IF OBJECT_ID ('usp_find_price') IS NOT NULL
DROP PROCEDURE usp_find_price
go

CREATE PROCEDURE usp_find_price
@mSite numeric(2),
@mCustType varchar(10),
@mCustNbr varchar(10),
@mUniqNbr varchar(10),
@mContainer varchar(10),
@mWaste varchar(10),
@mArea varchar(10),
@mDisposer varchar(10),
@mEffective datetime,
@result numeric (10)
AS
/*1*/
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
/*2*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*3*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*4*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*5*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*6*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*7*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*8*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

IF @result = 0
/*9*/
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*10*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*11*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*12*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*13*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*14*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*15*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*16*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*17*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*18*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*19*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*20*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*21*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*22*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*23*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*24*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*25*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*26*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*27*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*28*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*29*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*30*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*31*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*32*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
RETURN @result

[Peterg]

Hi all
I have recoded the function using arrays to store the seek definition and then a series of IF/THEN conditions to test each of the seek definitions prior to actually seeking in the table.

This has resulted in an 8 fold increase in speed and is now as fast in sql as it was in dbf (and of course it is now 8 times faster in dbf)

Many thanks for all of your help and pointing me in the direction of arrays

Peter

[Roger Seiler]

You're welcome. Glad it works for you.