RE: SQL Concat Issue
With some DB's moving your criteria from the WHERE clause to the HAVING clause will take care of it since HAVING is evaluated after the results have been calculated... sometimes... -- Jay -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 13 October 2007 00:01 To: CF-Talk Subject: SQL Concat Issue I am probably being dense or showing my ignorance here, but I have a legacy database that I need to run a query on like the following. Can anyone tell me how I can achieve the following. Right now I am getting an error that PubYear is not a valid column. Thank you! SELECT AdINFO_Publication, ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear FROMcrAdInfo AND PubYear BETWEEN #dateformat(startdate,"-mm-dd")# AND #dateformat(enddate,"-mm-dd")# Any Help is appreciated. Thanks! ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291081 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Concat Issue
It is early and my brain my not be functioning 100% but may need to add a GROUP BY for the HAVING to function on some DBs. On 10/15/07, James Smith <[EMAIL PROTECTED]> wrote: > > With some DB's moving your criteria from the WHERE clause to the HAVING > clause will take care of it since HAVING is evaluated after the results > have > been calculated... sometimes... > > -- > Jay > > -Original Message- > From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 13 October 2007 00:01 > To: CF-Talk > Subject: SQL Concat Issue > > I am probably being dense or showing my ignorance here, but I have a > legacy > database that I need to run a query on like the following. Can anyone tell > me how I can achieve the following. Right now I am getting an error that > PubYear is not a valid column. Thank you! > > SELECT AdINFO_Publication, > ADINFO_PUBLICATIONDATE_YEAR+ '-' + > ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear > FROMcrAdInfo > AND PubYear BETWEEN #dateformat(startdate,"-mm-dd")# > AND #dateformat(enddate,"-mm-dd")# > > Any Help is appreciated. Thanks! > > > > ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291082 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Concat Issue
>>Right now I am getting an error that PubYear is not a valid column Then I'll try to make sure that PubYear is a valid column in table crAdInfo ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291010 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Concat Issue
Try this SELECT AdINFO_Publication, ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear FROMcrAdInfo AND ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' BETWEEN #dateformat(startdate,"-mm-dd")# AND #dateformat(enddate,"-mm-dd")# > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, October 12, 2007 7:01 PM > To: CF-Talk > Subject: SQL Concat Issue > > I am probably being dense or showing my ignorance here, but I have a > legacy database that I need to run a query on like the following. Can > anyone tell me how I can achieve the following. Right now I am getting an > error that PubYear is not a valid column. Thank you! > > SELECT AdINFO_Publication, > ADINFO_PUBLICATIONDATE_YEAR+ '-' + > ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear > FROMcrAdInfo > AND PubYear BETWEEN #dateformat(startdate,"-mm-dd")# AND > #dateformat(enddate,"-mm-dd")# > > Any Help is appreciated. Thanks! > > ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290995 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
