sqlcoldfusionqoq

Query of queries not extracting data with data type mismatches

My client has two databases, one for the latlng of places and another database for every other table (why I don't know). I'm working off of coldfusion and from what I can tell I need to do a query of queries since CF doesn't allow for multiple datasources in the same cfquery tag. Now for some reason the primary key in the latlng table is a string and the other table is a double (again why I don't know). But in the query I have now I am testing I am not pulling any data. I'm still trying to understand how the QoQ works exactly but I think I have the general idea down. Thanks!

<cfquery name="get_restrauntinfo" datasource="#listings#" dbtype="odbc">
SELECT *
FROM listings_new
WHERE pub_code = 'GridR'
</cfquery>

<cfquery name="get_latlng" datasource="#latlng#" dbtype="odbc">
SELECT member_id, mlat, mlong
FROM maps
</cfquery> 

<cfquery name="join_rest" dbtype="query">
SELECT *
FROM get_latlng, get_restrauntInfo 
WHERE 'parseInt(get_restrauntInfo.cli_number)' = get_latlng.member_id
</cfquery>
Solution

  • I would, and have seconded Charlie's answer, but to fix your existing code, you could try replacing

    'parseInt(get_restrauntInfo.cli_number)'

    with

    CAST( get_restrauntInfo.cli_number AS INTEGER )

    in your WHERE clause.