help with cast function on sql statement

[omar999]

using sql and asp and need a little bit of help please. I'm calculating the cheapest prices from 3 columns which works fine

working sql statement

CARHIRE = "Select CheapestPrice = MIN(CheapestPrice) from " & _
"(Select MIN(Fully_Inclusive_Weekly_Price_Band_1) As CheapestPrice from National_Car_Prices UNION ALL" & _
" Select MIN(Fully_Inclusive_Weekly_Price_Band_2) from National_Car_Prices UNION ALL" & _
" Select MIN(Fully_Inclusive_Weekly_Price_Band_3) from National_Car_Prices )x"

but I also need to apply a cast function on the final value I output - just a simple division by 7. this is my attempt

CARHIRE = "Select CheapestPrice = MIN(CheapestPrice = Cast (CheapestPrice / 7 as dec (10,0)) from " & _
"(Select MIN(Fully_Inclusive_Weekly_Price_Band_1) As CheapestPrice from National_Car_Prices UNION ALL" & _
" Select MIN(Fully_Inclusive_Weekly_Price_Band_2) from National_Car_Prices UNION ALL" & _
" Select MIN(Fully_Inclusive_Weekly_Price_Band_3) from National_Car_Prices )x"

error

Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near '='.

thanks in advance
Omar

[NeoPa]

Why don't you start by posting the actual SQL the engine attempted to process, and the full error details including line and character. That's not complicated is it.

[ck9663]

Try converting this:

MIN(CheapestPrice = Cast (CheapestPrice / 7 as dec (10,0))

to

MIN(Cast (CheapestPrice / 7 as dec (10,0)))

Happy Coding!!!

~~ CK

[omar999]

CK your solution worked perfectly! thank you

my attempt wasnt too far off - syntax all wrong though!

thanks again : )