1

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>
kdauria
  • 6,300
  • 4
  • 34
  • 53
Techleadz Team
  • 198
  • 2
  • 15
  • can any body helps me ?? actually i am getting records form store proceedure and upon these records applying query and in that query i applied the convert() function of sql but throwing error. Can you you please help me? – Techleadz Team Oct 26 '18 at 11:41
  • 4
    Please include the error message in your question. If `convert()` does not work, try using `cast()` instead. Although `convert` is listed as one of the reserved words available to use - https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html So it probably depends on what error message you are getting. – Miguel-F Oct 26 '18 at 11:53
  • 2
    If you mean the MS SQL Server's convert function, then no, it isn't possible to use that in query of queries, because qoq is an in-memory sql-like feature, but it's not actually being sent to your datasource, it remains within the coldfusion application server. Coldfusion does not try to implement all the functions of, essentially, a third party rdbms. – drew sutherland Oct 26 '18 at 12:26
  • thanks @drewsutherland I get my answer. Can you tell me any other method to convert datatype from 'varchar' to 'int' in query on query feature of the coldfusion. Thanks – Techleadz Team Oct 26 '18 at 13:32
  • 1
    If you can't do what you need through QoQ, you may need to go back and create a new stored procedure. It's usually a lot easier to do query manipulations on the SQL server than it is to try to shoehorn a query through CF. QoQ is often like a hammer when your database hands you a screw. – Shawn Oct 26 '18 at 13:32
  • @TechleadzTeam What data are you trying to convert? `varchar` should coerce to `int` pretty easily in most cases. – Shawn Oct 26 '18 at 13:34
  • @Shawn yes i want to convert 'varchar' to 'int' in query on query feature. – Techleadz Team Oct 26 '18 at 13:35
  • 1
    You should be able to `SELECT cast(employeenumber AS integer) as employeenumber......ORDER BY employeeNumber`. But again, the main question is what are you trying to do with that QoQ data? You may be better off just fixing it at the initial query level rather than trying to QoQ your way into new results. – Shawn Oct 26 '18 at 13:40
  • 1
    Note: QoQ can be useful if you don't have access to the underlying query, but 1) it is its own flavor of sql syntax and 2) if you can modify the base query, it's often _much_ easier to just edit the query to return what you want. Databases aren't just a place to hold data. They also have some pretty powerful features to retrieve the data you want in the way you want it. – Shawn Oct 26 '18 at 13:45
  • @shawn if we can use CAST() then why we cant use CONVERT()? can you elaborate? – Techleadz Team Oct 26 '18 at 13:46
  • 1
    `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. – Shawn Oct 26 '18 at 13:50
  • 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 datasets, since it's an in-memory operation. – Shawn Oct 26 '18 at 13:55
  • @Shawn but the error coming when using cast. Actually when we are casting 'employeenumber' and it can be null. and error comes that empty string cant be converted to INT – Techleadz Team Oct 26 '18 at 14:03
  • What is the error? You should edit your original question to include some of these details. The actual error message and the fact that your value can be NULL are both important to your question. – Shawn Oct 26 '18 at 14:03
  • @Shawn Actually when we are casting 'employeenumber' and it can be null. and error comes that empty string cant be converted to INT – Techleadz Team Oct 26 '18 at 14:08
  • Are you able to create a new sproc or use the query from the original sproc? – Shawn Oct 26 '18 at 14:19
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/182595/discussion-between-techleadz-team-and-shawn). – Techleadz Team Oct 26 '18 at 14:38
  • I wonder what the solution ended up being – James A Mohler Oct 27 '18 at 19:18
  • The correct answer is to use cast(), which @Shawn mentioned in a comment. I think he should put that information into an answer. – Dan Bracuk Oct 29 '18 at 03:51
  • @DanBracuk the cast is also not working. – Techleadz Team Oct 29 '18 at 11:02

1 Answers1

2

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.

Shawn
  • 4,758
  • 1
  • 20
  • 29