minimum value of multiple columns as integer not working

[omar999]

hi - im trying to return the minimum value from selected columns. however for some non obvious reason it only works when 3digit long numbers are entered in to the columns in question. [i.e Price_Band_1, Price_Band_2, Price_Band_3 & Price_Band_4]

the below code returns 100 when it really should return 2... any ideas as to why?

FLIGHTROUTES = "Select CheapestPrice = MIN(CheapestPrice) from (" & _
 
"Select MIN(Price_Band_1) As CheapestPrice from TEST where Id in (1,2,3) " & _
"UNION ALL " & _
"Select MIN(Price_Band_2) from TEST where Id in (1,2,3) " & _
"UNION ALL  " & _
"Select MIN(Price_Band_3) from TEST where Id in (1,2,3) " & _ 
"UNION ALL  " & _
"Select MIN(Price_Band_4) from TEST where Id in (1,2,3) " & _ 
 
")x"
 

this is my table

ID Flight_Route Price_Band_1 Month_Band_1 Date_Band_1 Price_Band_2 Month_Band_2 Date_Band_2 Price_Band_3 Month_Band_3 Date_Band_3 Price_Band_4 Month_Band_4 Date_Band_4
1    EDMONTON to GATWICK    99    July    13, 20    2    Aug    27    85    Sept    28    108    Oct    NO FLIGHT    'YEG','LGW'    <a name="YEGLGW"></a>
2    GATWICK to EDMONTON    100    July    20, 27    4    Aug    24    99    Sept    21, 28    350    Oct    NO FLIGHT    'YEG','LGW'    <a name="YEGLGW"></a>
3    HALIFAX to GATWICK    99    July    15, 22    9    Aug    5    101    Sept    30    999    Oct    NO FLIGHT    'YHZ','LGW'    <a name="YHZLGW"></a>
 

the exact same sql statement/code works perfectly for another table but as mentioned above all of its 'Price_Band's have values higher than 100...

please advise
Omar.

[Christopher Nigro]

What is the type of all the Price_Band_X columns? It looks like they are text types instead of numeric types.

[omar999]

hi Christopher - thanks for the quick reply. they are set to varchar(50) rather than int as sometimes a N/A value is required to be input in the table

[Christopher Nigro]

Right, so of course the string "100" is less than the string "2". You need to cast those values in the comparison. You also need to filter out those "N\A" values. I do not know if you developed the DB but in the future, use numeric types where appropriate to avoid situations like this. Is "N/A" really "required" to be input into the table? I don't think that would ever be a valid business or functional requirement. It sounds like a convention that was agreed on as a valid solution when all you really needed to do was allow nulls in that field or use some integer value, like -1, that could be filtered out in queries.

[omar999]

ok no worries - I'll set these columns to type integer & to allow nulls.

then if the column = NULL then I'll replace with N/A via asp

thanks again

[Christopher Nigro]

OK, no problem.