James
Possibly but i am not that good at sql. Any ideas wher eot look
Thanks
Peter
Any clever coders out there
- Antonio Linares
- Site Admin
- Posts: 37481
- Joined: Thu Oct 06, 2005 5:47 pm
- Location: Spain
- Contact:
- James Bott
- Posts: 4654
- Joined: Fri Nov 18, 2005 4:52 pm
- Location: San Diego, California, USA
- Contact:
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
>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
- James Bott
- Posts: 4654
- Joined: Fri Nov 18, 2005 4:52 pm
- Location: San Diego, California, USA
- Contact:
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
>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
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
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
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
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
- Posts: 223
- Joined: Thu Dec 01, 2005 3:34 pm
- Location: Nyack, New York, USA
- Contact: