Cannot mix types VARCHAR and DOUBLE

[TiGGi]

Hi all,

I got a bit of the problem here, I am doing zip code radius and trying to return the results to a select box.
I want to display city, state, zip and distance in the select box and that's where I get the problem.
So in my search page I got zip and radius field that bind to a cfc that gets the data once those 2 fields are entered and returns it to the select box (cfselect)
so in my cfc I have query that queries the original query in order to combine the fields so I can bind it to the select box:
<cfquery ...>
select zipcode, (city + ', ' + state + ' - ' + zipcode + distance ) as location,
</cfquery>
It''s not letting me mix my data with distance and giving me error:
Query Of Queries runtime error.
Cannot mix types VARCHAR and DOUBLE in a + binary operation.

Anyone knows how to avoid this??????

[-__cfSearching__-]

TiGGi wrote:
I have query that queries the original query in order to combine the fields so I can bind it to the select box

Why not do this in the original query?

(city + ', ' + state + ' - ' + zipcode + distance )

If the parenthesis ( ) are part of the result string, they should be enclosed in quotes. ' ( '+ city ...... +' ) '

Cannot mix types VARCHAR and DOUBLE in a + binary operation.

When using + with a mix of varchar and double/numeric values, CF may not understand what you are trying to do. It might get confused about whether you are trying to concatenate strings or add numbers. Using an explicit CAST to convert the numbers to VARCHAR should prevent that problem. That said, QoQ can still be "quirky" at times.

[TiGGi]

thanks allot, that's exactly what I need, you da man!!!!!!
One issue though, I had results ordered by distance and now that's a varchar it's not ordering it correctly, any idea how can I fix this?

[-__cfSearching__-]

You should still be able to order by distance. Though you may have to add it to your SELECT list (ie as a separate column. SELECT zipcode, distance, ... concatenated columns...

[TiGGi]

Well since the distance is varchar now it's sorting it as a character not a number so order would be something like 1,10, 11....2, 20, 21 instead of 1,2,3,.......

[-__cfSearching__-]

Include the column twice. Once separately, and once as part of the total 'location' string. But only convert the one used in the concatentation. Then it will sort correctly and you probably do not need a QoQ.

SELECT
zipcode, distance,
city + ', ' + ... + cast(distance as varchar) as location
...

[Guest]

I'm running into the same issue but my datatype is datetime.

Does anyone know how to do the same thing but with a datetime field? with the format: dd-mmm-yy. I have tried CAST() but this returns the full date and time stamp. I'm unable to get date formatting for the field.

Any help greatly appreciated.

Thanks in advanced,
Jennifer

[Guest]

I'm running into the same issue but my datatype is datetime.

Does anyone know how to do the same thing but with a datetime field? with the format: dd-mmm-yy. I have tried CAST() but this returns the full date and time stamp. I'm unable to get date formatting for the field.

Any help greatly appreciated.

Thanks in advanced,
Jennifer

[JR__Bob__Dobbs-qSBHQ2]

CFJen,

Options:
1. Don't format the date field in your query, using the DateFormat function in your CF code to handle formatting when a date is displayed

2. To keep a formatted date in your query create a separate varchar field for it. This was previously suggested by cfSearching for TiGGi's issue.

If this doesn't help please post your code and what you are trying to accomplish, and any error messages you are receiving.

[Guest]

Thank you JR 'Bob' Dobbs,

I created an alias in the one query, converting the datetime to varchar and then concatenated it in the query of queries.

Jennifer