Query of queries not extracting data with data type mismatches

Question

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>

score 2 · Answer 1 · answered Jul 16 '11 at 17:59

2

While CF doesn't allow multiple datasources within the came cfquery tag, you should still be able to join across databases. See: http://www.google.com/search?q=join+across+databases

Assuming that'll work for you, it seems a lot simpler than 2 separate queries and a QoQ on top of that.

answered Jul 16 '11 at 17:59

charliegriefer

3,342
1
18
20

sadly I already tried that and Coldfusion didn't like me trying to work around their tag design. – rajh2504 Jul 16 '11 at 21:21
1

As long as it's valid SQL between the query tags, ColdFusion shouldn't care. Can you elaborate as to what didn't work? What was the error message? – charliegriefer Jul 16 '11 at 21:38

score 1 · Accepted Answer · answered Jul 16 '11 at 18:42

1

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.

answered Jul 16 '11 at 18:42

CfSimplicity

2,338
15
17

do You think the QoQ will be more efficient then my answer below Or are the essentially doing the same thing? – rajh2504 Jul 16 '11 at 21:24
1

Yes it will, because in your answer you are making another complete database call for each iteration of the first query loop. With QoQ there are only 2 database calls. But if you persevere with Charlie's advice you will have just one query, which is the ideal solution. – CfSimplicity Jul 17 '11 at 08:23

score 0 · Answer 3 · answered Jul 16 '11 at 21:24

So what I ended up doing was using CFLOOP while I'm making probably more queries then I have to it works! Here is the code:

<cfloop query="get_restrauntinfo">
    <cfquery name="get_latlng" datasource="#latlng#" dbtype="odbc">
        SELECT member_id, mlat, mlong
        FROM maps
        WHERE member_id = '#get_restrauntinfo.cli_number#'
    </cfquery>
    <cfset title = #get_restrauntinfo.publish_as#>
    var content = addContent(#get_restrauntinfo#);
    <cfoutput query="get_latlng">
        var #toScript(title, "title")#;
        addMarker(#get_latlng.mlat#,#get_latlng.mlong#, title, content, map);
    </cfoutput> 
</cfloop>

Query of queries not extracting data with data type mismatches

3 Answers3