Claudeless
|
| Posted: 05/26/2005, 7:30 AM |
|
We have Tier Pricing...
This is what i am Looking to so
I 'm doing PHP5 and mysql 5.03 on Win2003 with Apache.
I have tried using a Stored Prodecdure on but the Value always get set to the last value....
@level:=(SELECT IF SUM(assets.MultiNo)=1
then @level:=1
elseIF SUM(assets.MultiNo) BETWEEN 2 AND 6 then @level:=2
elseIF SUM(assets.MultiNo) BETWEEN 7 AND 10 then @level:=3
elseIF SUM(assets.MultiNo) BETWEEN 11 AND 25 then @level:=4
elseIF SUM(assets.MultiNo) BETWEEN 26 AND 50 then @level:=5
elseIF SUM(assets.MultiNo) BETWEEN 51 AND 100 then @level:=6
elseIF SUM(assets.MultiNo) BETWEEN 101 AND 200 then @level:=7
elseIF SUM(assets.MultiNo) BETWEEN 201 AND 400 then @level:=8
elseIF SUM(assets.MultiNo) BETWEEN 401 AND 800 then @level:=9
elseIF SUM(assets.MultiNo) BETWEEN 801 AND 1500 then @level:=10
ELSE @LEVEL:=11
END IF),
@rtp := (SELECT pricebook.Retail
FROM pricebook
Where ((pricebook.Category = classofsoftware.Catagory)
AND (pricebook.Level = @level)))
This is the Query I Run...
SELECT
assets.MasterCusID AS MID,
assets.CurrentVersion AS CVER,
assets.QuoteAsset AS QA,
assets.VOIDED AS VOIDED,
SUM(assets.MultiNo) AS QTY,
assets.`Orginal MODEL_NO` AS MODEL,
assets.`Orginal PartNo` AS PART,
classofsoftware.Catagory AS CAT ,
classofsoftware.CatDesciption AS DESC,
classofsoftware.Level AS LEVEL,
FROM assets
INNER JOIN classofsoftware ON (assets.`Orginal MODEL_NO` = classofsoftware.ModelNo )
WHERE (MasterCusID = 20031511)
AND (QuoteAsset = 0)
AND (VOIDED = 0)
Group BY
assets.CurrentVersion,
classofsoftware.Catagory
Does anyone have any suggestions...
Thanks.
|
|
|
 |
DonB
|
| Posted: 05/26/2005, 11:58 AM |
|
I think you need to look at the CCOpenRS() function,which will let you pass
into it a complete (complex) query. CCDLookUp() takes the column list,
table and where portions of a query and combines them.
Also, CCDLookUp() can only return a scalar result (single value).
CCOpenRS() will return a recordset (multiple rows).
--
DonB
http://www.gotodon.com/ccbth
"Claudeless" <Claudeless@forum.codecharge> wrote in message
news:24295dd85752cb@news.codecharge.com...
> We have Tier Pricing...
> This is what i am Looking to so
>
> I 'm doing PHP5 and mysql 5.03 on Win2003 with Apache.
>
> I have tried using a Stored Prodecdure on but the Value always get set to
the
> last value....
>
>
> @level:=(SELECT IF SUM(assets.MultiNo)=1
> then @level:=1
> elseIF SUM(assets.MultiNo) BETWEEN 2 AND 6 then @level:=2
> elseIF SUM(assets.MultiNo) BETWEEN 7 AND 10 then @level:=3
> elseIF SUM(assets.MultiNo) BETWEEN 11 AND 25 then @level:=4
> elseIF SUM(assets.MultiNo) BETWEEN 26 AND 50 then @level:=5
> elseIF SUM(assets.MultiNo) BETWEEN 51 AND 100 then @level:=6
> elseIF SUM(assets.MultiNo) BETWEEN 101 AND 200 then @level:=7
> elseIF SUM(assets.MultiNo) BETWEEN 201 AND 400 then @level:=8
> elseIF SUM(assets.MultiNo) BETWEEN 401 AND 800 then @level:=9
> elseIF SUM(assets.MultiNo) BETWEEN 801 AND 1500 then @level:=10
> ELSE @LEVEL:=11
> END IF),
>
> @rtp := (SELECT pricebook.Retail
> FROM pricebook
> Where ((pricebook.Category = classofsoftware.Catagory)
> AND (pricebook.Level = @level)))
>
>
> This is the Query I Run...
>
> SELECT
> assets.MasterCusID AS MID,
> assets.CurrentVersion AS CVER,
> assets.QuoteAsset AS QA,
> assets.VOIDED AS VOIDED,
> SUM(assets.MultiNo) AS QTY,
> assets.`Orginal MODEL_NO` AS MODEL,
> assets.`Orginal PartNo` AS PART,
>
> classofsoftware.Catagory AS CAT ,
> classofsoftware.CatDesciption AS DESC,
> classofsoftware.Level AS LEVEL,
>
> FROM assets
> INNER JOIN classofsoftware ON (assets.`Orginal MODEL_NO` =
> classofsoftware.ModelNo )
>
>
> WHERE (MasterCusID = 20031511)
> AND (QuoteAsset = 0)
> AND (VOIDED = 0)
>
> Group BY
> assets.CurrentVersion,
> classofsoftware.Catagory
>
>
> Does anyone have any suggestions...
>
> Thanks.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
