Re: What's wrong with this query?
Doesn't #variables.comparisondate# need to be quoted? I think it might be trying to find a field called 16-Feb-01. Ryan At 14:26 2/16/01 -, you wrote: >Okay, I give up. Can someone take a peek at this query and tell me what's >wrong? I keep getting an Oracle error, "Invalid column name". >If I take the line out "AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, >'DD-MON-YY') <= #variables.comparisondate#" I have no problems. Oh, curse >these date problems! ;) > > > >SELECT SUG_SUGGESTIONS.SUGGESTION_ID, > SUG_SUGGESTIONS.SUGGESTION_NAME, > SUG_SUGGESTIONS.STATUS_CODE, > SUG_SUGGESTIONS.STATUS_DATE, > SUG_SUGGESTIONS.PRIMARY_SUGGESTOR_USERID, > SUG_SUGGESTIONS.EVAL_DISPOSITION, > SUG_SUGGESTIONS.SAVINGS_TOTAL_AMOUNT, > SUG_MORE_SUGGESTORS.SUGGESTOR_USERID > FROM CF_3IS.SUG_SUGGESTIONS, CF_3IS.SUG_MORE_SUGGESTORS > WHERE SUG_SUGGESTIONS.STATUS_CODE= 'C' > AND SUG_SUGGESTIONS.EVAL_DISPOSITION = 'A' > AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MON-YY') <= >#variables.comparisondate# > AND SUG_SUGGESTIONS.SUGGESTION_ID = SUG_MORE_SUGGESTORS.SUGGESTION_ID > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What's wrong with this query?
Dont you need quotes around the comparison date ? And is "MON" a valid month argument ? SELECT SUG_SUGGESTIONS.SUGGESTION_ID, SUG_SUGGESTIONS.SUGGESTION_NAME, SUG_SUGGESTIONS.STATUS_CODE, SUG_SUGGESTIONS.STATUS_DATE, SUG_SUGGESTIONS.PRIMARY_SUGGESTOR_USERID, SUG_SUGGESTIONS.EVAL_DISPOSITION, SUG_SUGGESTIONS.SAVINGS_TOTAL_AMOUNT, SUG_MORE_SUGGESTORS.SUGGESTOR_USERID FROM CF_3IS.SUG_SUGGESTIONS, CF_3IS.SUG_MORE_SUGGESTORS WHERE SUG_SUGGESTIONS.STATUS_CODE= 'C' AND SUG_SUGGESTIONS.EVAL_DISPOSITION = 'A' AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MON-YY') <= #variables.comparisondate# AND SUG_SUGGESTIONS.SUGGESTION_ID = SUG_MORE_SUGGESTORS.SUGGESTION_ID Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What's wrong with this query?
TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MON-YY') <= #variables.comparisondate# Changed to TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MMM-YY') <= #variables.comparisondate# Thank You, Peter Peter J. MacDonald II Creative Computing, Inc. 100 Middle Street Lincoln, RI 02865 Phone: 401.727.0183 x123 Fax: 401.727.4998 Portable: 401.965.3661 E-MAIL: [EMAIL PROTECTED] Web Page: www.creatcomp.com -Original Message- From: Terri Stocke [mailto:[EMAIL PROTECTED]] Sent: Friday, February 16, 2001 09:27 To: CF-Talk Subject: What's wrong with this query? Okay, I give up. Can someone take a peek at this query and tell me what's wrong? I keep getting an Oracle error, "Invalid column name". If I take the line out "AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MON-YY') <= #variables.comparisondate#" I have no problems. Oh, curse these date problems! ;) SELECT SUG_SUGGESTIONS.SUGGESTION_ID, SUG_SUGGESTIONS.SUGGESTION_NAME, SUG_SUGGESTIONS.STATUS_CODE, SUG_SUGGESTIONS.STATUS_DATE, SUG_SUGGESTIONS.PRIMARY_SUGGESTOR_USERID, SUG_SUGGESTIONS.EVAL_DISPOSITION, SUG_SUGGESTIONS.SAVINGS_TOTAL_AMOUNT, SUG_MORE_SUGGESTORS.SUGGESTOR_USERID FROM CF_3IS.SUG_SUGGESTIONS, CF_3IS.SUG_MORE_SUGGESTORS WHERE SUG_SUGGESTIONS.STATUS_CODE= 'C' AND SUG_SUGGESTIONS.EVAL_DISPOSITION = 'A' AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MON-YY') <= #variables.comparisondate# AND SUG_SUGGESTIONS.SUGGESTION_ID = SUG_MORE_SUGGESTORS.SUGGESTION_ID Thanks! Terri ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: What's wrong with this query?
Ryan, You da man. Thanks! ;) Terri Original Message Follows From: Ryan <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]> Subject: Re: What's wrong with this query? Date: Fri, 16 Feb 2001 08:38:08 -0600 Doesn't #variables.comparisondate# need to be quoted? I think it might be trying to find a field called 16-Feb-01. Ryan At 14:26 2/16/01 -, you wrote: >Okay, I give up. Can someone take a peek at this query and tell me what's >wrong? I keep getting an Oracle error, "Invalid column name". >If I take the line out "AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, >'DD-MON-YY') <= #variables.comparisondate#" I have no problems. Oh, curse >these date problems! ;) > > > >SELECT SUG_SUGGESTIONS.SUGGESTION_ID, > SUG_SUGGESTIONS.SUGGESTION_NAME, > SUG_SUGGESTIONS.STATUS_CODE, > SUG_SUGGESTIONS.STATUS_DATE, > SUG_SUGGESTIONS.PRIMARY_SUGGESTOR_USERID, > SUG_SUGGESTIONS.EVAL_DISPOSITION, > SUG_SUGGESTIONS.SAVINGS_TOTAL_AMOUNT, > SUG_MORE_SUGGESTORS.SUGGESTOR_USERID > FROM CF_3IS.SUG_SUGGESTIONS, CF_3IS.SUG_MORE_SUGGESTORS > WHERE SUG_SUGGESTIONS.STATUS_CODE= 'C' > AND SUG_SUGGESTIONS.EVAL_DISPOSITION = 'A' > AND TO_CHAR(SUG_SUGGESTIONS.STATUS_DATE, 'DD-MON-YY') <= >#variables.comparisondate# > AND SUG_SUGGESTIONS.SUGGESTION_ID = SUG_MORE_SUGGESTORS.SUGGESTION_ID > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
