Re: How do u add colums in SQL query and do a WHERE on the new column
Was wondering if anyone knows how to add up 4 columns in a query, give the result a new column name, and then use that new column name in the WHERE part of the query. Situation is where some customers are 30, 60, 90 or 120 past due. So I'm trying to add up the 30,60,90,120 column totals and save the result as a new column called TOTALOUTSTANDING Then we want to send emails to anyone where TOTALOUTSTANDING 5 (where the total outstanding is more than $5) I tried this script, but the dang thing never worked:( CFQUERY NAME=pastduenotice DATASOURCE=customers SELECT first_name, email_address, customer_number, total_due, (past_due_30) + (past_due_60) + (past_due_90) + (past_due_120) AS totaloutstanding FROM accountsreceivable where totaloutstanding 0 /CFQUERY Appreciate any enlightenment on this one:) __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: How do u add colums in SQL query and do a WHERE on the new column
What was the error message you were getting? IIRC, you can't use ali ased columns in your where clause, so you need to put your whole computati on into your where clause as well. How's that for code reuse :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: February 13, 2002 2:43 PM To: CF-Talk Subject: Re: How do u add colums in SQL query and do a WHERE on the n ew column Was wondering if anyone knows how to add up 4 columns in a query, giv e the result a new column name, and then use that new column name in the WH ERE part of the query. Situation is where some customers are 30, 60, 90 or 120 past due. So I'm trying to add up the 30,60,90,120 column totals and save the result a s a new column called TOTALOUTSTANDING Then we want to send emails to anyone where TOTALOUTSTANDING 5 (w here the total outstanding is more than $5) I tried this script, but the dang thing never worked:( CFQUERY NAME=pastduenotice DATASOURCE=customers SELECT first_name, email_address, customer_number, total_due, (past_d ue_30) + (past_due_60) + (past_due_90) + (past_due_120) AS totaloutstanding FROM accountsreceivable where totaloutstanding 0 /CFQUERY Appreciate any enlightenment on this one:) _ _ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: How do u add colums in SQL query and do a WHERE on the new column
Why not just loop the below query to generate more then 1 column: cfquery DATASOURCE=myDataSource ALTER TABLE myTable ADD COLUMN NewColumnName myColumnType /cfquery WHERE... myTable is the name of the table you're adding the column to. NewColumnName is the name of the new column. myColumnType is the type of field you're adding (TEXT, MEMO, CURRENCY, ETC). There are also rules governing more detailed aspects of column adding. EG: If I wanted to put a restriction of 50 characters on a new text field I was adding, I would use the following : cfquery DATASOURCE=myDataSource ALTER TABLE myTable ADD COLUMN NewColumnName TEXT(50) /cfquery Similarily you can delete Columns by using : cfquery DATASOURCE=myDataSource ALTER TABLE myTable DROP COLUMN NewColumnName /cfquery Thanks, Jason Larson [EMAIL PROTECTED] www.larzz.com P. 406.728.4422 F. 406.543.4266 You think the Cubs have sore arms? What about the fans in left field? -Original Message- From: Kwang Suh [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 3:14 PM To: CF-Talk Subject: RE: How do u add colums in SQL query and do a WHERE on the new column What was the error message you were getting? IIRC, you can't use ali ased columns in your where clause, so you need to put your whole computati on into your where clause as well. How's that for code reuse :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: February 13, 2002 2:43 PM To: CF-Talk Subject: Re: How do u add colums in SQL query and do a WHERE on the n ew column Was wondering if anyone knows how to add up 4 columns in a query, giv e the result a new column name, and then use that new column name in the WH ERE part of the query. Situation is where some customers are 30, 60, 90 or 120 past due. So I'm trying to add up the 30,60,90,120 column totals and save the result a s a new column called TOTALOUTSTANDING Then we want to send emails to anyone where TOTALOUTSTANDING 5 (w here the total outstanding is more than $5) I tried this script, but the dang thing never worked:( CFQUERY NAME=pastduenotice DATASOURCE=customers SELECT first_name, email_address, customer_number, total_due, (past_d ue_30) + (past_due_60) + (past_due_90) + (past_due_120) AS totaloutstanding FROM accountsreceivable where totaloutstanding 0 /CFQUERY Appreciate any enlightenment on this one:) _ _ Why Share? Dedicated Win 2000 Server . PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation . $99/Month . Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
