Can we use convert(int,Employeenumber) function in a ColdFusion Query of Query?

Question

Can we use convert(int,Employeenumber) function in ColdFusion query of query feature? When I try it I am getting some error.

I am getting records from a stored procedure. When I try to re-query those records with a query of query, using the convert() function, it is throwing an error.

Actually the column is of type varchar and I want to convert that column into an int. Then I want to order by that column, but it is not working. Here is the QofQ:

<cfquery name="qEmployees" dbtype="query">
    Select convert(int,employeenumber) as employeenumber
    ...
</cfquery>

Answer 1

From Conversation:

CONVERT() is usually language-dependent SQL, whereas CAST() is a more general method of essentially doing the same thing, but in this case, it's also what the QoQ engine chose to use.

It's important to remember that the version of SQL in ColdFusion Query of Query is not the same as traditional SQL.There are a lot of things you can't do with a Query of Query, and I definitely wouldn't recommend it for large initial data sets, since it's an in-memory operation.

Also, this issue doesn't appear to be a data type conversion issue. It seems to be an issue with NULL in the data. ColdFusion, especially anything older than the ACF 2018 version, doesn't play nice with NULLs. QoQ doesn't have an ISNULL() or COALESCE() functionality and work-arounds would probably be more trouble than they're worth. The easiest thing would be to modify the original base query to return the needed values.