> Cast (and Convert) do not work in Query of Querys bugger!
Scott, is it for CF6.1 or CF7? maybe that's the issue (wild guess, clutching at straws) > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Scott > Thornton > Sent: Wednesday, 24 August 2005 12:22 PM > To: CFAussie Mailing List > Subject: [cfaussie] RE: Query of Query Problem - Combine two fields > together into one > > > 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/ww > himpl/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/ > --- 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/
