Errors with querying a query table using non-QoQ query and QoQ query

Report · Aug 28, 2009

HELP ! ! ! Going into testing soon. I need this to work to get correct report results ! ! ! !

My issue seems similar to the one just answered about Query of Queries RunTime Error

and the reply from lawhite01 caught my eye.

This is a 2 parter. The second part is the QoQ part, but the 1st part has a line in the query that is similar to the QoQ one and it uses the same data. Part 1 also throws an error.

PART # 1.

I'm trying to use a query table created through QueryNew and then query it.

I need multiple columns in the query table I create:

<cfscript>
tot_AllCurrentDraftListing = QueryNew("AnnounceNum, JP_PDLoc, JP_JS_Title, JP_JS, JP_KW_1, JP_JobTitle, JP_Open, JP_Close, JP_CloseType, JP_CloseName, JP_PosNeed, JP_DirectHire, JP_Desc, JP_Draft, JP_Archived, JP_State, JP_AreaName, JP_AreaID, JP_AreaAlias, JP_Fac_SU, JP_Fac_Facility, JP_FAC_ID, JP_Grade1, JP_sal_low1, JP_sal_high1, JP_Grade2, JP_sal_low2, JP_sal_high2, JP_Grade3, JP_sal_low3, JP_sal_high3, JP_Grade4, JP_sal_low4, JP_sal_high4, JP_Grade5, JP_sal_low5, JP_sal_high5, JP_Posted, JP_TypeHire, JP_HRemail");
</cfscript>

......

Then I populate all the cells of the query table.

Then I set up to use the created query table.

I do this first:

    <cfquery name="qAltPostID" datasource="#at_datasource#">
         SELECT AltPoster, fk_Job_AnnounceNum
         from JOB_JPContacts
         Where AltJPContactType = 'AltPosterID'
         and AltPoster = '#session.IHSUID#'
         </cfquery>

Then, in my first query using the created query, I expect to need to choose from multiple values, so I'm using this line in the query (this is NOT a QoQ query):

and AnnounceNum IN (<cfqueryparam cfsqltype="CF_SQL_varchar" value="#ValueList(qAltPostID.fk_Job_AnnounceNum)#">)

I've also tried:

and AnnounceNum IN (#ValueList(qAltPostID.fk_Job_AnnounceNum)#)

and:

             and JOB_AnnounceNum IN
          (
                SELECT fk_Job_AnnounceNum
                from JOB_JPContacts
                Where AltJPContactType = 'AltPosterID'
                and AltPoster = '#session.IHSUID#'
            )

ERROR is: one record should return. I get 0.

PART # 2: Here's the QoQ part.

I get the error:

Query Of Queries runtime error.
Comparison exception while executing IN.
Unsupported Type Comparison Exception: The IN operator does not support comparison between the following types:
Left hand side expression type = "LONG".
Right hand side expression type = "STRING".

A tutorial I found gave an example using only one column for this part of the fix:

tot_AllCurrentDraftListing = QueryNew("AnnounceNum", "CF_SQL_VARCHAR")

How would I set up the query with the datatype when I'm using multiple columns:

<cfscript>
tot_AllCurrentDraftListing = QueryNew("AnnounceNum, JP_PDLoc, JP_JS_Title, JP_JS, JP_KW_1, JP_JobTitle, JP_Open, JP_Close, JP_CloseType, JP_CloseName, JP_PosNeed, JP_DirectHire, JP_Desc, JP_Draft, JP_Archived, JP_State, JP_AreaName, JP_AreaID, JP_AreaAlias, JP_Fac_SU, JP_Fac_Facility, JP_FAC_ID, JP_Grade1, JP_sal_low1, JP_sal_high1, JP_Grade2, JP_sal_low2, JP_sal_high2, JP_Grade3, JP_sal_low3, JP_sal_high3, JP_Grade4, JP_sal_low4, JP_sal_high4, JP_Grade5, JP_sal_low5, JP_sal_high5, JP_Posted, JP_TypeHire, JP_HRemail");
</cfscript>

I used this code after all the cells contained values and before running my QoQ query:

</cfloop>

Is that correct?

Thanks.

Whoever can help me with this should be awarded extra points ! ! ! !

Report · Aug 28, 2009

and AnnounceNum IN (<cfqueryparam cfsqltype="CF_SQL_varchar" value="#ValueList(qAltPostID.fk_Job_AnnounceNum)#">)

If you're passing a list as a param, you need to tell <cfqueryparam> it's a list. Read:

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html#1102474

ERROR is: one record should return. I get 0.

It's a bit hard to comment on this sort of thing without knowing the data involved.

A tutorial I found gave an example using only one column for this part of the fix:
tot_AllCurrentDraftListing = QueryNew("AnnounceNum", "CF_SQL_VARCHAR")
How would I set up the query with the datatype when I'm using multiple columns:

Again, this is a matter of reading the relevant docs:

http://livedocs.adobe.com/coldfusion/8/htmldocs/functions_m-r_19.html#292759

As a general rule, if you're having trouble with the syntax of a CFML statement, look it up in the docs.

--

Adam

Report · Aug 28, 2009

The data is a mix of number values and text values. The database storing the data is "varchar".

I've already tried using the "list" attribute. Still doesn't work. I've read the documentation.

Perhaps if someone could answer the second part of my question, I could figure out the rest.

Thanks.

Report · Aug 28, 2009

I've already tried using the "list" attribute. Still doesn't work. I've read the documentation.

What do you mean by "doesn't work"? Did it error? Did you simply get unexpected results? If you manually eyeball the data and check the SQL being executed, are you sure it's "not working"?

Perhaps if someone could answer the second part of my question, I could figure out the rest.

The second part of your question seemed to be this:

How would I set up the query with the datatype when I'm using multiple columns:

Which I did answer. Well I pointed you to the page of the docs that answers it, anyhow.

Was there some other "second part of your question" that wasn't connected to that?

--

Adam

Report · Aug 29, 2009

PART # 1.
and AnnounceNum IN (#ValueList(qAltPostID.fk_Job_AnnounceNum)#)

You want something like this ('abc', 'def'), instead of something like this (abc, def). So, apply single quotes, like this, for example

<cfset unquotedItemsList = valueList(qAltPostID.fk_Job_AnnounceNum)>

<cfset quotedItemsList = listQualify(unquotedItemsList, "'", ",", "CHAR")>

Then use a clause like:
and AnnounceNum IN (#preserveSingleQuotes(quotedItemsList)#)

PART # 2: Here's the QoQ part.
How would I set up the query with the datatype when I'm using multiple columns

Be aware that the function queryNew() takes 2 arguments. The first is the list of column names, the second the respective datatypes. You have omitted the second argument. That's possible, of course, as it is optional. However, this means that Coldfusion will have to guess what the datatype is, later when the data comes in. This is inefficient and error-prone.

Correct example
<cfscript>
myQuery = queryNew("userID, username, password, date_of_birth, isMale", "integer, varchar, varchar, date, bit");
</cfscript>

Check out the livedocs on querynew()

Report · Aug 31, 2009

Thank you both !