ColdFusion Query Join vs My Syntax

Question

I am trying to do a ColdFusion Query of Query. The first query qEL returns several records. One of the rows is:

ELINKACCOUNTNUMBER                  WINACCNT                WSTREAMBUSINESSUNIT     WSTREAMOBJ  WSTREAMSUB
101-40001-0000-15-001-00000-0000    216000012.32103.1750    216000012               32103       1750 

Note in above: WSBusinessUnit value is 216000012, WSTREAMOBJ is 32103 and WSTREAMSUB is 1759

The second query is qEL2 which does return a bunch of records but only one row matches the record above with columns names of First, Second, and Third respectively.

So I need to do a join or something to and here is my code:

<cfquery name="qFinal" dbtype="query"> 
    SELECT 
       qEL.ELINKACCOUNTNUMBER AS ELAccountNum, qEL.WINACCNT AS WSAccountNum, qEL2.Description AS DESCRIPTION, qEL2.Posting_Edit_Code AS Posting_Edit_Code

    FROM 
        qEL, qEL2
    WHERE 
        qEL.WSTREAMBUSINESSUNIT =  <cfqueryparam value="#qEL2.First#" CFSQLType="cf_sql_varchar" />
    AND
        qEL.WSTREAMOBJ = <cfqueryparam value="#qEL2.Second#" CFSQLType="cf_sql_varchar" /> 
    AND 
        qEL.WSTREAMSUB = <cfqueryparam value="#qEL2.Third#" CFSQLType="cf_sql_varchar" /> 
</cfquery>

But this query doesn't return anything. Should I do a join instead somehow?

Thanks!

Edit 1: Maybe some syntax like this instead? Having trouble with a SQL Join in ColdFusion

Answer 1

To get started change this type of thing:

qEL.WSTREAMBUSINESSUNIT =  <cfqueryparam value="#qEL2.First#" CFSQLType="cf_sql_varchar" />

to this type of thing

qEL.WSTREAMBUSINESSUNIT =  qEL2.First

The query parameters are restricting your join to the first record of qEL2. Something else that might mess you up is whitespace. Make sure none of these values have leading or trailing spaces.

Answer 2

To expand on Dan's answer, there are a few issues that may be preventing the code from working:

1. When comparing two columns, do not use pound signs orcfqueryparam. That syntax will end up comparing the values in one column to a single, literal value. For example:

   • WHERE qEL.someID = 2
   • WHERE qEL.firstName = 'Bob'
     
     

   To compare all of the values in one column to all of the values in another, use this syntax:

   WHERE someQuery1.ColumnName = otherQuery2.ColumnName

2. QoQ's are extremely rudimentary. They only support a small subset of SQL that does not include things like outer joins or string functions like trim(). If needed, those must be done in the original database queries.

3. The word "First" is very likely to be a reserved word and must be escaped with square brackets.

4. Unlike some databases, string comparisons in QoQ's are case-sensitive.

   Unlike the rest of ColdFusion, Query of Queries is case-sensitive. However, Query of Queries supports two string functions, UPPER() and LOWER(), which you can use to achieve case-insensitive matching.

   Along those same lines, string comparisons are hyper sensitive to differences. So watch out for extra white space, as even a single tab or space difference can prevent a match.

5. May not apply here, but check for data type differences. When comparing columns with different data types, the QoQ must convert both columns to the same data type first. Do not rely on implicit conversion. If needed, use and explicit CAST() to convert the columns to the appropriate data type. For example:

   • WHERE CAST(someQuery1.ColumnName AS INTEGER) = CAST(otherQuery2.ColumnName AS INTEGER)

   • WHERE CAST(someQuery1.ColumnName AS VARCHAR) = CAST(otherQuery2.ColumnName AS VARCHAR)

Finally, start small. Match on a single column first, and check the results. If it works, move to the next one. Otherwise, check the column data types and values to try and spot why things are not matching. Keep in mind browsers are famous for collapsing white space, so check the values character by character if needed.

Answer 3

The problem is resolved based on the feedback/answer from @Dan Bracuk and @Leigh. Thank you all!

The problem of no-return had mostly to do with QEL2's Third column with blank values. So, in QEL2, I put this in the WHERE clauses:

And  length(RTRIM(GMSUB)) > 2

And, yes, I changed the column names in queries from first, second, third to more meaningful ones :)

So the WHERE clause now forces some values which are to be queried in the final query. And here is the final query--again, column names have been changed.

<cfquery name="qFinal" dbtype="query"> 
        SELECT 
        qEL.ELINKACCOUNTNUMBER AS ELAccountNum, qEL.WINACCNT AS WSAccountNum, qEL2.Description AS DESCRIPTION, qEL2.Posting_Edit_Code AS Posting_Edit_Code

        FROM 
        qEL, qEL2

        WHERE               
        CAST(qEL.WSTREAMBUSINESSUNIT AS INTEGER) = CAST(qEL2.WSBusinessUnit AS INTEGER)
        AND 
        CAST(qEL.WSTREAMOBJ AS INTEGER) = CAST(qEL2.WSBusinessObject AS INTEGER)
        AND
        CAST(qEL.WSTREAMSUB AS INTEGER) = CAST(qEL2.WSBusinessSub AS INTEGER) 


  </cfquery>

Thank you all! My Answer is definitely a combination of your feedback/inputs.