RE: Help with Q of Q
Seems as if you are trying to SUM up non numeric data. You will have to convert or cast the data into a numeric format to use the SUM function. Mike -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 8:33 AM To: CF-Talk Subject: Help with Q of Q Hello All, I am trying to run a query of a query and I am running into a little of SQL trouble.I keep getting this error Query Of Queries runtime error. The aggregate function [SUM(_expression_)] cannot operate on an operand of type [VARCHAR]. Here is the code I am trying to run cfquery name=MasterQuery datasource=#appdsn# Select client_debt.debt_id , Orig_amt , Cur_Bal , Sum(Pmt_amt) as SUMPmtamt , Count(*) as TotalPayments from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id group by client_debt.debt_id , Orig_amt , Cur_Bal /cfquery !--- cfdump var=#masterquery# --- cfquery name=qry_GetTrackingReport dbtype=query select count(*) as TotalAccounts , Sum(Orig_amt) as SumOrigAmt , Sum(Cur_Bal) as SumCurBal , Sum(SUMPmtamt) as SUMSUMPmtAmt , Sum(TotalPayments) as SUMTotalPayments >From MasterQuery /cfquery cfdump var=#qry_GetTrackingReport# It look ok to me, I am not sure what I am not seeing, can someone point me in the right direction. Thanks Mike _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help with Q of Q
I don't know for sure, but from the error, it looks like you are trying to perform a math operation on a datatype that is textual.if the column isnt a number (like int or bigInt), I would imagine...you cant do a sum operation on it. ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 8:33 AM To: CF-Talk Subject: Help with Q of Q Hello All, I am trying to run a query of a query and I am running into a little of SQL trouble.I keep getting this error Query Of Queries runtime error. The aggregate function [SUM(_expression_)] cannot operate on an operand of type [VARCHAR]. Here is the code I am trying to run cfquery name=MasterQuery datasource=#appdsn#Select client_debt.debt_id , Orig_amt , Cur_Bal , Sum(Pmt_amt) as SUMPmtamt , Count(*) as TotalPayments from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id group by client_debt.debt_id , Orig_amt , Cur_Bal /cfquery !--- cfdump var=#masterquery# --- cfquery name=qry_GetTrackingReport dbtype=query select count(*) as TotalAccounts , Sum(Orig_amt) as SumOrigAmt , Sum(Cur_Bal) as SumCurBal , Sum(SUMPmtamt) as SUMSUMPmtAmt , Sum(TotalPayments) as SUMTotalPayments From MasterQuery /cfquery cfdump var=#qry_GetTrackingReport# It look ok to me, I am not sure what I am not seeing, can someone point me in the right direction. Thanks Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Help with Q of Q
if you are just after the SUM of the columns, you could try something like CFOUTPUT #ArraySum(ListToArray(ValueList(MasterQuery.Orig_amt)))# /CFOUTPUT HTH -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 3, 2003 13:33 To: CF-Talk Subject: Help with Q of Q Hello All, I am trying to run a query of a query and I am running into a little of SQL trouble.I keep getting this error Query Of Queries runtime error. The aggregate function [SUM(_expression_)] cannot operate on an operand of type [VARCHAR]. Here is the code I am trying to run cfquery name=MasterQuery datasource=#appdsn# Select client_debt.debt_id , Orig_amt , Cur_Bal , Sum(Pmt_amt) as SUMPmtamt , Count(*) as TotalPayments from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id group by client_debt.debt_id , Orig_amt , Cur_Bal /cfquery !--- cfdump var=#masterquery# --- cfquery name=qry_GetTrackingReport dbtype=query select count(*) as TotalAccounts , Sum(Orig_amt) as SumOrigAmt , Sum(Cur_Bal) as SumCurBal , Sum(SUMPmtamt) as SUMSUMPmtAmt , Sum(TotalPayments) as SUMTotalPayments >From MasterQuery /cfquery cfdump var=#qry_GetTrackingReport# It look ok to me, I am not sure what I am not seeing, can someone point me in the right direction. Thanks Mike _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Help with Q of Q
Should have looked at that first, this is my first query of a query, thought it had something to do with QofQ Thanks Mike - Original Message - From: Tony Weeg To: CF-Talk Sent: Wednesday, December 03, 2003 8:38 AM Subject: RE: Help with Q of Q I don't know for sure, but from the error, it looks like you are trying to perform a math operation on a datatype that is textual.if the column isnt a number (like int or bigInt), I would imagine...you cant do a sum operation on it. ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 8:33 AM To: CF-Talk Subject: Help with Q of Q Hello All, I am trying to run a query of a query and I am running into a little of SQL trouble.I keep getting this error Query Of Queries runtime error. The aggregate function [SUM(_expression_)] cannot operate on an operand of type [VARCHAR]. Here is the code I am trying to run cfquery name=MasterQuery datasource=#appdsn#Select client_debt.debt_id , Orig_amt , Cur_Bal , Sum(Pmt_amt) as SUMPmtamt , Count(*) as TotalPayments from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id group by client_debt.debt_id , Orig_amt , Cur_Bal /cfquery !--- cfdump var=#masterquery# --- cfquery name=qry_GetTrackingReport dbtype=query select count(*) as TotalAccounts , Sum(Orig_amt) as SumOrigAmt , Sum(Cur_Bal) as SumCurBal , Sum(SUMPmtamt) as SUMSUMPmtAmt , Sum(TotalPayments) as SUMTotalPayments From MasterQuery /cfquery cfdump var=#qry_GetTrackingReport# It look ok to me, I am not sure what I am not seeing, can someone point me in the right direction. Thanks Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
