Hi, Cast (and Convert) do not work in Query of Querys
Query Of Queries syntax error. Encountered "CAST" at line 0, column 0. Incorrect Select List, Incorrect select column, I think I will " turn that number field into a char anyhoo" :-) stupid java. >>> [EMAIL PROTECTED] 08/24/05 12:11 pm >>> from the docs: http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00001271.htm (although you might still need to turn that number field into a char anyhoo...) Using the CAST function In some cases, a column's data type may not be compatible with the processing you want to do. For example, query columns returned by the cfhttp tag are all of type CF_SQL_VARCHAR, even though the contents may be numeric. In this case, you can use the Query of Queries CAST function to convert a column value into an expression of the correct data type. <cfquery name="hello" dbtype="query"> SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER)) AS SUMNOW from qStockItems </cfquery> or in your case (not tried) > <cfquery dbtype="query" name="ECG_LOAD"> > SELECT > (CAST(PRVSP_REFNO as VARCHAR) + SERVICING_PROVIDER_NO) AS > GROUPING, > * > FROM > OUTPATIENT_LOAD > WHERE > TRANSACTION_TYPE = 'ECG' > ORDER BY > PRVSP_REFNO > </cfquery> > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Scott > Thornton > Sent: Wednesday, 24 August 2005 11:54 AM > To: CFAussie Mailing List > Subject: [cfaussie] Query of Query Problem - Combine two > fields together > into one > > > Hi, > > I wish to combine a numeric field with a varchar field into a > single field withing a Query of Query. > > eg 12358989 + '123456X' = 12358989123456X > > The error I get is : > > Query Of Queries runtime error. > Cannot mix types "BIGDECIMAL" and "VARCHAR" in a "+" binary > operation. > > SQL: > > <cfquery dbtype="query" name="ECG_LOAD"> > SELECT > PRVSP_REFNO + SERVICING_PROVIDER_NO AS GROUPING, > * > FROM > OUTPATIENT_LOAD > WHERE > TRANSACTION_TYPE = 'ECG' > ORDER BY > PRVSP_REFNO > </cfquery> > > Scott Thornton, Programmer > Application Development > Information Services and Telecommunications > Hunter-New England Area Health Service > Phone RNH +61 2 49236066 > Fax +61 2 49236076 > > [EMAIL PROTECTED] > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > [EMAIL PROTECTED] > Aussie Macromedia Developers: http://lists.daemon.com.au/ > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
