aha. thanks carl. the orderprice in the ORDER BY was actually working fine. but including the IF statement in...
AND IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) >= <cfqueryparam cfsqltype="cf_sql_integer" > value="#listfirst(session.pricerange)#"> works perfectly. sa weet! > You'll need to put the same IF statement into the order by clause. > SQL will only sort on columns that actually exist in the table, not > dynamically calculated columns (such as your orderprice alias). > > SELECT SQL_CALC_FOUND_ROWS P.normalprice, P.saleprice, > IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) as > orderprice > FROM product P > <cfif len(session.pricerange)> > AND orderprice >= <cfqueryparam cfsqltype="cf_sql_integer" > value="#listfirst(session.pricerange)#"> > AND orderprice <= <cfqueryparam cfsqltype="cf_sql_integer" > value="#listlast(session.pricerange)#"> > </cfif> > ORDER BY IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) > > HTH, > Carl > > > extra for experts... > > > > though the following would work, but gives error... > > > > SELECT SQL_CALC_FOUND_ROWS P.normalprice, P.saleprice, > > IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) as > > orderprice > > FROM product P > > <cfif len(session.pricerange)> > > AND orderprice >= <cfqueryparam cfsqltype="cf_sql_integer" > > value="#listfirst(session.pricerange)#"> > > AND orderprice <= <cfqueryparam cfsqltype="cf_sql_integer" > > value="#listlast(session.pricerange)#"> > > </cfif> > > ORDER BY orderprice > > > > if i replace orderprice with "P.normalprice >= ..." it works. > however > > i want it to see which is the lowest first. > > > > mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335131 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
