|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
April 2nd, 2004, 10:26 AM
|
|||
|
|||
|
SQL Statement Case selection...
Continuation from this post http://forums.aspfree.com/t26343/s.html
Trying to determine a price range, based on 3 columns of prices. Simply trying to get THE lowest price offered for an item and the highest price offered for an item, and the item is offered by multiple vendors. This post above is very long and this is a contuation of memnochs suggestion, whichs sounds wonderful, but I'm having trouble getting the select statement to run.. This selection is in the middle of another loop running through products. I've tried putting the statement through QA, but dont know enough about it to evaluate the statements and errors from QA. k so.. here goes. table queried is called ttInventory, looking at 3 columns, newprice, refurbprice and oosprice (basically new used and out of service) some fields are filled with a 0.00 (if vendor does not have part used..or new..or what have you. here's mem's select statement. Code:
Dim rangeRs, maxPrice, minPrice, SQL
set rangeRS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT" &_
SQL = SQL & "Case" &_
SQL = SQL & "When newprice > refurbprice AND newprice > oosprice Then newprice" &_
SQL = SQL & "When refurbprice > newprice AND refurbprice > oosprice THEN refurbprice" &_
SQL = SQL & "When oosprice > newprice AND oosprice > refurbprice THEN oosprice" &_
SQL = SQL & "End As 'maxPrice'" &_
SQL = SQL & "Case" &_
SQL = SQL & "When newprice < refurbprice AND newprice < oosprice Then newprice" &_
SQL = SQL & "When refurbprice < newprice AND refurbprice < oosprice THEN refurbprice" &_
SQL = SQL & "When oosprice < newprice AND oosprice < refurbprice THEN oosprice" &_
SQL = SQL & "End As 'minPrice'" &_
SQL = SQL & "FROM ttInventory WHERE prodID = '" & itemRs("prodId") & "'"
rangeRs.open SQL, objCn, 3, 3
When I run the statement online in a browser, it returns : Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /itemlist.asp, line 110 line 110 = rangeRs.open SQL, objCn, 3, 3 --------------------------------- IN QA I ran the statement as so.. Code:
SELECT
Case
When newprice > refurbprice AND newprice > oosprice Then newprice
When refurbprice > newprice AND refurbprice > oosprice THEN refurbprice
When oosprice > newprice AND oosprice > refurbprice THEN oosprice
End As 'maxPrice'
Case
When newprice < refurbprice AND newprice < oosprice Then newprice
When refurbprice < newprice AND refurbprice < oosprice THEN refurbprice
When oosprice < newprice AND oosprice < refurbprice THEN oosprice
End As 'minPrice'
FROM ttInventory WHERE prodID = '" & itemRs("prodId") & "'
and it returned : Server: Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'Case'. line 7 being the second instance of the word CASE so i ran again in QA as Code:
SELECT Case When newprice > refurbprice AND newprice > oosprice Then newprice When refurbprice > newprice AND refurbprice > oosprice THEN refurbprice When oosprice > newprice AND oosprice > refurbprice THEN oosprice End As maxPrice FROM ttInventory where prodID='1' and it returned maxPrice ---------- 145.00 147.00 (2 row(s) affected) Any ideas on why i would get the arguments error... or anything else for that matter.. Im graspin 
|
|
#2
May 4th, 2004, 06:13 AM
|
|||
|
|||
|
Try This
-------------------- SELECT Case When newprice > refurbprice AND newprice > oosprice Then newprice When refurbprice > newprice AND refurbprice > oosprice THEN refurbprice When oosprice > newprice AND oosprice > refurbprice THEN oosprice End As 'maxPrice', Case When newprice < refurbprice AND newprice < oosprice Then newprice When refurbprice < newprice AND refurbprice < oosprice THEN refurbprice When oosprice < newprice AND oosprice < refurbprice THEN oosprice End As 'minPrice' FROM ttInventory WHERE prodID = '" & itemRs("prodId") & "' ------------------- Try "," Before second case.
|
|
| Viewing: ASP Free Forums > Database > SQL Development > SQL Statement Case selection... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
