RE: Query Problem
OK, so I did not quite understand what I was doing. I thought that I was setting 100 cells, and the year range from 1920 - 2020 was 100 years; you see where I was going. So, then do I put 1920 in there then? I tried 2020 and of course it crated 2020 rows which sucked. Thanks -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Thursday, December 28, 2006 9:51 PM To: CF-Talk Subject: Re: Query Problem You have a query with 100 rows and you are trying to set a cell in row 1920. 1920 is greater than 100. On 12/29/06, Bruce Sorge [EMAIL PROTECTED] wrote: Hello. I have these three queries that I am creating below. The first two work great, but the third one gives me the following error: The row number, (1920) is out of bound. This is happening on line 48 which is this one: cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) What is wrong with it? Seems to me it should work. cfset monthQuery = QueryNew(Month_Number, Month_Name, Integer, varchar) cfset newRow = QueryAddRow(monthQuery, 12) cfloop index=loopcount from=1 to=12 cfset months[loopcount]=MonthAsString(loopcount) cfset monthNumber[loopcount] = loopcount cfset temp = QuerySetCell(monthQuery, Month_Number, #monthNumber[loopcount]#, #loopcount#) cfset temp = QuerySetCell(monthQuery, Month_Name, #months[loopcount]#, #loopcount#) /cfloop cfset dayQuery = QueryNew(Day_Number, Integer) cfset newRow = QueryAddRow(dayQuery, 31) cfloop index=loopcount from=1 to=31 cfset dayNumber[loopcount] = loopcount cfset temp = QuerySetCell(dayQuery, Day_Number, #dayNumber[loopcount]#, #loopcount#) /cfloop cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfloop index=year_number from=1920 to=2020 cfset yearnumber[year_number] = year_number cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) /cfloop Thanks, Bruce Sorge ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265290 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
OK, so you have this: cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfloop index=year_number from=1920 to=2020 cfset yearnumber[year_number] = year_number cfset temp = QuerySetCell(yearQuery,year_num,#yearnumber[year_number]#,#year_number#) /cfloop Based on what you are doing with the other queries and what I think you are trying to do, this will work: cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfset YearOffset = 1919 cfloop index=year_number from=1 to=100 cfset yearnumber[year_number + YearOffset] = year_number + YearOffset cfset temp = QuerySetCell(yearQuery,year_num,yearnumber[year_number + YearOffset],year_number) /cfloop I got rid of unnecessary # signs too. Why you have a struct of years containing the same data as the struct's key I don't know, but this will give you the result you were after. On 12/29/06, Bruce Sorge [EMAIL PROTECTED] wrote: OK, so I did not quite understand what I was doing. I thought that I was setting 100 cells, and the year range from 1920 - 2020 was 100 years; you see where I was going. So, then do I put 1920 in there then? I tried 2020 and of course it crated 2020 rows which sucked. Thanks -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Thursday, December 28, 2006 9:51 PM To: CF-Talk Subject: Re: Query Problem You have a query with 100 rows and you are trying to set a cell in row 1920. 1920 is greater than 100. On 12/29/06, Bruce Sorge [EMAIL PROTECTED] wrote: Hello. I have these three queries that I am creating below. The first two work great, but the third one gives me the following error: The row number, (1920) is out of bound. This is happening on line 48 which is this one: cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) What is wrong with it? Seems to me it should work. cfset monthQuery = QueryNew(Month_Number, Month_Name, Integer, varchar) cfset newRow = QueryAddRow(monthQuery, 12) cfloop index=loopcount from=1 to=12 cfset months[loopcount]=MonthAsString(loopcount) cfset monthNumber[loopcount] = loopcount cfset temp = QuerySetCell(monthQuery, Month_Number, #monthNumber[loopcount]#, #loopcount#) cfset temp = QuerySetCell(monthQuery, Month_Name, #months[loopcount]#, #loopcount#) /cfloop cfset dayQuery = QueryNew(Day_Number, Integer) cfset newRow = QueryAddRow(dayQuery, 31) cfloop index=loopcount from=1 to=31 cfset dayNumber[loopcount] = loopcount cfset temp = QuerySetCell(dayQuery, Day_Number, #dayNumber[loopcount]#, #loopcount#) /cfloop cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfloop index=year_number from=1920 to=2020 cfset yearnumber[year_number] = year_number cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) /cfloop Thanks, Bruce Sorge ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265292 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
That did it. Thanks. On 12/29/06, James Holmes [EMAIL PROTECTED] wrote: OK, so you have this: cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfloop index=year_number from=1920 to=2020 cfset yearnumber[year_number] = year_number cfset temp = QuerySetCell(yearQuery,year_num,#yearnumber[year_number]#,#year_number#) /cfloop Based on what you are doing with the other queries and what I think you are trying to do, this will work: cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfset YearOffset = 1919 cfloop index=year_number from=1 to=100 cfset yearnumber[year_number + YearOffset] = year_number + YearOffset cfset temp = QuerySetCell(yearQuery,year_num,yearnumber[year_number + YearOffset],year_number) /cfloop I got rid of unnecessary # signs too. Why you have a struct of years containing the same data as the struct's key I don't know, but this will give you the result you were after. On 12/29/06, Bruce Sorge [EMAIL PROTECTED] wrote: OK, so I did not quite understand what I was doing. I thought that I was setting 100 cells, and the year range from 1920 - 2020 was 100 years; you see where I was going. So, then do I put 1920 in there then? I tried 2020 and of course it crated 2020 rows which sucked. Thanks -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Thursday, December 28, 2006 9:51 PM To: CF-Talk Subject: Re: Query Problem You have a query with 100 rows and you are trying to set a cell in row 1920. 1920 is greater than 100. On 12/29/06, Bruce Sorge [EMAIL PROTECTED] wrote: Hello. I have these three queries that I am creating below. The first two work great, but the third one gives me the following error: The row number, (1920) is out of bound. This is happening on line 48 which is this one: cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) What is wrong with it? Seems to me it should work. cfset monthQuery = QueryNew(Month_Number, Month_Name, Integer, varchar) cfset newRow = QueryAddRow(monthQuery, 12) cfloop index=loopcount from=1 to=12 cfset months[loopcount]=MonthAsString(loopcount) cfset monthNumber[loopcount] = loopcount cfset temp = QuerySetCell(monthQuery, Month_Number, #monthNumber[loopcount]#, #loopcount#) cfset temp = QuerySetCell(monthQuery, Month_Name, #months[loopcount]#, #loopcount#) /cfloop cfset dayQuery = QueryNew(Day_Number, Integer) cfset newRow = QueryAddRow(dayQuery, 31) cfloop index=loopcount from=1 to=31 cfset dayNumber[loopcount] = loopcount cfset temp = QuerySetCell(dayQuery, Day_Number, #dayNumber[loopcount]#, #loopcount#) /cfloop cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfloop index=year_number from=1920 to=2020 cfset yearnumber[year_number] = year_number cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) /cfloop Thanks, Bruce Sorge ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265311 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
You have a query with 100 rows and you are trying to set a cell in row 1920. 1920 is greater than 100. On 12/29/06, Bruce Sorge [EMAIL PROTECTED] wrote: Hello. I have these three queries that I am creating below. The first two work great, but the third one gives me the following error: The row number, (1920) is out of bound. This is happening on line 48 which is this one: cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) What is wrong with it? Seems to me it should work. cfset monthQuery = QueryNew(Month_Number, Month_Name, Integer, varchar) cfset newRow = QueryAddRow(monthQuery, 12) cfloop index=loopcount from=1 to=12 cfset months[loopcount]=MonthAsString(loopcount) cfset monthNumber[loopcount] = loopcount cfset temp = QuerySetCell(monthQuery, Month_Number, #monthNumber[loopcount]#, #loopcount#) cfset temp = QuerySetCell(monthQuery, Month_Name, #months[loopcount]#, #loopcount#) /cfloop cfset dayQuery = QueryNew(Day_Number, Integer) cfset newRow = QueryAddRow(dayQuery, 31) cfloop index=loopcount from=1 to=31 cfset dayNumber[loopcount] = loopcount cfset temp = QuerySetCell(dayQuery, Day_Number, #dayNumber[loopcount]#, #loopcount#) /cfloop cfset yearQuery = QueryNew(year_num, Integer) cfset newRow = QueryAddRow(yearQuery, 100) cfloop index=year_number from=1920 to=2020 cfset yearnumber[year_number] = year_number cfset temp = QuerySetCell(yearQuery, year_num, #yearnumber[year_number]#, #year_number#) /cfloop Thanks, Bruce Sorge ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265282 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
Looks like you might need to add an additional field to your albums table. Then populate it with the artists id's that are associated with speccific albums. Also might want to add the same to your track table. albumid int(10) unsigned NOT NULL auto_increment, artistid int (10), albumtitle varchar(255), year smallint(5) unsigned - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, November 05, 2006 11:22 PM Subject: Query Problem I have a database of music CDs, something like: album - albumid int(10) unsigned NOT NULL auto_increment, albumtitle varchar(255), year smallint(5) unsigned track - trackid int(10) unsigned NOT NULL auto_increment, tracktitle varchar(255), tracknumber smallint(5) unsigned, albumid int(10) unsigned artist - artistid int(10) unsigned NOT NULL auto_increment, name varchar(255) artisttrack - artistid int(10) unsigned trackid int(10) unsigned There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? Here's what I have, but it can return only a single artist for the album: SELECT ar.name, a.albumtitle, a.year FROM album a LEFT JOIN track t ON t.album = a.albumid LEFT JOIN artisttrack at ON at.trackid = t.trackid LEFT JOIN artist ar ON ar.artistid = at.artistid GROUP BY a.albumid ORDER BY a.albumtitle; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259257 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query Problem
Adding the artistid to the album table would cause some SERIOUS redundant data... take Peeping Tom... there are at least 20 different artists on that album. Adding artistid to the album table would mean you needed to add the same album 20 times with a different artistid each time. I wouldnt add anything you didnt have to. You can do it with what you have I'm sure. I'll just need to set it up and test it to give you actual syntax. Everything appears to be related to everything else one way or another so it should be possible to do with one query using what you have. it's early but try this... seletc album.*, artist.* from album inner join ( (artist inner join artisttrack ON artist.artistid = artisttrack.artistid) Inner join track ON artisttrack.trackid = track.trackid ) on album.albumid = track.albumid where albumid = #TheAlbumIdYouWantToGrabArtistsFor# Let me know what you get from that. If it doesnt work ill set up a database and give it a better shot :-) I would consider renaming 'year' and 'name' if possible. They are both reserved words that may cause problems later. -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 7:37 AM To: CF-Talk Subject: Re: Query Problem Looks like you might need to add an additional field to your albums table. Then populate it with the artists id's that are associated with speccific albums. Also might want to add the same to your track table. albumid int(10) unsigned NOT NULL auto_increment, artistid int (10), albumtitle varchar(255), year smallint(5) unsigned - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, November 05, 2006 11:22 PM Subject: Query Problem I have a database of music CDs, something like: album - albumid int(10) unsigned NOT NULL auto_increment, albumtitle varchar(255), year smallint(5) unsigned track - trackid int(10) unsigned NOT NULL auto_increment, tracktitle varchar(255), tracknumber smallint(5) unsigned, albumid int(10) unsigned artist - artistid int(10) unsigned NOT NULL auto_increment, name varchar(255) artisttrack - artistid int(10) unsigned trackid int(10) unsigned There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? Here's what I have, but it can return only a single artist for the album: SELECT ar.name, a.albumtitle, a.year FROM album a LEFT JOIN track t ON t.album = a.albumid LEFT JOIN artisttrack at ON at.trackid = t.trackid LEFT JOIN artist ar ON ar.artistid = at.artistid GROUP BY a.albumid ORDER BY a.albumtitle; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259258 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query Problem
Actually I think that would be a grouping issue (once you fixed the 'seletc' type)... and I hate 'GROUP BY' clauses... they get me every time! At the risk of being bashed for using 'IN'... try this select albumtitle, name, year from album, artist where albumid = #TheAlbumIdYouWantToGrabArtistsFor# and artistid in ( select artistid from artisttrack where trackid in ( select trackid from track where albumid = #TheAlbumIdYouWantToGrabArtistsFor# ) ) Unless you have the album with the original We Are the World song in there, I dont think you'll return enough rows to make a difference in performance. Heh If that works for you id turn it into a stored procedure and stick with it. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.430 / Virus Database: 268.13.28/518 - Release Date: 11/4/2006 5:30 PM ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259261 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query Problem
Actually, you should use a inner join on that, so not to return records you do not need. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259263 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
I tend to find that exists is very helpful for these situation (and reads like the english version of what I want). SELECT artistid,name FROMartist WHERE EXISTS ( SELECT artistid FROMtrack INNER JOIN artisttrack ON track.trackid = artisttrack.trackid WHERE track.albumid = 2 AND artisttrack.artistid = artist.artistid ) This says Give me all of the artists that perform on a track for on album 2. You could change the 2 to any value, of course. Of course, I tend not to worry about performance unless testing shows it to be an issue. Steve Bryant 918-449-9440 Bryant Web Consulting LLC http://www.BryantWebConsulting.com/ http://steve.coldfusionjournal.com/ There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259264 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Problem
Im not sure which one you mean. The first one I posted DID use inner joins and the problem was of course returning the same artist multiple times... (probably the same number of tracks) The second should NOT need an inner join from what I see. It should be fine as is. -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 9:10 AM To: CF-Talk Subject: Re: Query Problem Actually, you should use a inner join on that, so not to return records you do not need. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259267 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query Problem
Weird. I had sent a query and it posted my last post and has not sent my other one...I was not speaking of your query Bobby. Sorry bout that. I was talking about my own. Ok, this is by adding a albumid to you artisttrack table. Of course you will have multiple id's listed in there, but it is just a list of id's. My naming convention are a little different but you get the picture. SELECT ar.artist_id, ar.name, a.album_id, a.album_name, a.year FROM artists ar INNER JOIN artists_albums_tracks aat ON ar.artist_id = aat.artist_id INNER JOIN albums a ON aat.album_id = a.album_id - Original Message - From: Bobby Hartsfield [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, November 06, 2006 7:14 AM Subject: RE: Query Problem Im not sure which one you mean. The first one I posted DID use inner joins and the problem was of course returning the same artist multiple times... (probably the same number of tracks) The second should NOT need an inner join from what I see. It should be fine as is. -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 9:10 AM To: CF-Talk Subject: Re: Query Problem Actually, you should use a inner join on that, so not to return records you do not need. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259268 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query Problem
Is this using the musicbrainz database? The db just wasn't made the way I would have done it and I opted to make a number of structural changes. With millions of records it was a bit of a pain. If so give me a shout off list if you like about what you are up to and maybe we can share ideas. Joshua -Original Message- From: Steve Bryant [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 8:03 AM To: CF-Talk Subject: Re: Query Problem I tend to find that exists is very helpful for these situation (and reads like the english version of what I want). SELECT artistid,name FROMartist WHERE EXISTS ( SELECT artistid FROMtrack INNER JOIN artisttrack ON track.trackid = artisttrack.trackid WHERE track.albumid = 2 AND artisttrack.artistid = artist.artistid ) This says Give me all of the artists that perform on a track for on album 2. You could change the 2 to any value, of course. Of course, I tend not to worry about performance unless testing shows it to be an issue. Steve Bryant 918-449-9440 Bryant Web Consulting LLC http://www.BryantWebConsulting.com/ http://steve.coldfusionjournal.com/ There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259269 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query Problem
Ahh gotcha. Still hasnt come through. Im sure you can see why I thought you meant mine thoguh ;-) cheers -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 9:20 AM To: CF-Talk Subject: Re: Query Problem Weird. I had sent a query and it posted my last post and has not sent my other one...I was not speaking of your query Bobby. Sorry bout that. I was talking about my own. Ok, this is by adding a albumid to you artisttrack table. Of course you will have multiple id's listed in there, but it is just a list of id's. My naming convention are a little different but you get the picture. SELECT ar.artist_id, ar.name, a.album_id, a.album_name, a.year FROM artists ar INNER JOIN artists_albums_tracks aat ON ar.artist_id = aat.artist_id INNER JOIN albums a ON aat.album_id = a.album_id - Original Message - From: Bobby Hartsfield [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, November 06, 2006 7:14 AM Subject: RE: Query Problem Im not sure which one you mean. The first one I posted DID use inner joins and the problem was of course returning the same artist multiple times... (probably the same number of tracks) The second should NOT need an inner join from what I see. It should be fine as is. -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 9:10 AM To: CF-Talk Subject: Re: Query Problem Actually, you should use a inner join on that, so not to return records you do not need. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259272 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
Use select distinct on the original query to eliminate duplicate records. I have a database of music CDs, something like: album - albumid int(10) unsigned NOT NULL auto_increment, albumtitle varchar(255), year smallint(5) unsigned track - trackid int(10) unsigned NOT NULL auto_increment, tracktitle varchar(255), tracknumber smallint(5) unsigned, albumid int(10) unsigned artist - artistid int(10) unsigned NOT NULL auto_increment, name varchar(255) artisttrack - artistid int(10) unsigned trackid int(10) unsigned There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? Here's what I have, but it can return only a single artist for the album: SELECT ar.name, a.albumtitle, a.year FROM album a LEFT JOIN track t ON t.album = a.albumid LEFT JOIN artisttrack at ON at.trackid = t.trackid LEFT JOIN artist ar ON ar.artistid = at.artistid GROUP BY a.albumid ORDER BY a.albumtitle; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259285 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
You are absolutely right. It is very early, and that would cause albums with multiple artists to be listed multiple times. Anyhow - Original Message - From: Bobby Hartsfield [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, November 06, 2006 6:06 AM Subject: RE: Query Problem Adding the artistid to the album table would cause some SERIOUS redundant data... take Peeping Tom... there are at least 20 different artists on that album. Adding artistid to the album table would mean you needed to add the same album 20 times with a different artistid each time. I wouldn't add anything you didn't have to. You can do it with what you have I'm sure. I'll just need to set it up and test it to give you actual syntax. Everything appears to be related to everything else one way or another so it should be possible to do with one query using what you have. it's early but try this... seletc album.*, artist.* from album inner join ( (artist inner join artisttrack ON artist.artistid = artisttrack.artistid) Inner join track ON artisttrack.trackid = track.trackid ) on album.albumid = track.albumid where albumid = #TheAlbumIdYouWantToGrabArtistsFor# Let me know what you get from that. If it doesn't work ill set up a database and give it a better shot :-) I would consider renaming 'year' and 'name' if possible. They are both reserved words that may cause problems later. -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 7:37 AM To: CF-Talk Subject: Re: Query Problem Looks like you might need to add an additional field to your albums table. Then populate it with the artists id's that are associated with speccific albums. Also might want to add the same to your track table. albumid int(10) unsigned NOT NULL auto_increment, artistid int (10), albumtitle varchar(255), year smallint(5) unsigned - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, November 05, 2006 11:22 PM Subject: Query Problem I have a database of music CDs, something like: album - albumid int(10) unsigned NOT NULL auto_increment, albumtitle varchar(255), year smallint(5) unsigned track - trackid int(10) unsigned NOT NULL auto_increment, tracktitle varchar(255), tracknumber smallint(5) unsigned, albumid int(10) unsigned artist - artistid int(10) unsigned NOT NULL auto_increment, name varchar(255) artisttrack - artistid int(10) unsigned trackid int(10) unsigned There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? Here's what I have, but it can return only a single artist for the album: SELECT ar.name, a.albumtitle, a.year FROM album a LEFT JOIN track t ON t.album = a.albumid LEFT JOIN artisttrack at ON at.trackid = t.trackid LEFT JOIN artist ar ON ar.artistid = at.artistid GROUP BY a.albumid ORDER BY a.albumtitle; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259316 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Problem
Ok, this is by adding a albumid to you artisttrack table. Of course you will have multiple id's listed in there, but it is just a list of id's. My naming convention are a little different but you get the picture. SELECT ar.artist_id, ar.name, a.album_id, a.album_name, a.year FROM artists ar LEFT OUTER JOIN artists_albums_tracks aat ON ar.artist_id = aat.artist_id LEFT OUTER JOIN albums a ON aat.album_id = a.album_id - Original Message - From: Bobby Hartsfield [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, November 06, 2006 6:06 AM Subject: RE: Query Problem Adding the artistid to the album table would cause some SERIOUS redundant data... take Peeping Tom... there are at least 20 different artists on that album. Adding artistid to the album table would mean you needed to add the same album 20 times with a different artistid each time. I wouldn't add anything you didn't have to. You can do it with what you have I'm sure. I'll just need to set it up and test it to give you actual syntax. Everything appears to be related to everything else one way or another so it should be possible to do with one query using what you have. it's early but try this... seletc album.*, artist.* from album inner join ( (artist inner join artisttrack ON artist.artistid = artisttrack.artistid) Inner join track ON artisttrack.trackid = track.trackid ) on album.albumid = track.albumid where albumid = #TheAlbumIdYouWantToGrabArtistsFor# Let me know what you get from that. If it doesn't work ill set up a database and give it a better shot :-) I would consider renaming 'year' and 'name' if possible. They are both reserved words that may cause problems later. -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 7:37 AM To: CF-Talk Subject: Re: Query Problem Looks like you might need to add an additional field to your albums table. Then populate it with the artists id's that are associated with speccific albums. Also might want to add the same to your track table. albumid int(10) unsigned NOT NULL auto_increment, artistid int (10), albumtitle varchar(255), year smallint(5) unsigned - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, November 05, 2006 11:22 PM Subject: Query Problem I have a database of music CDs, something like: album - albumid int(10) unsigned NOT NULL auto_increment, albumtitle varchar(255), year smallint(5) unsigned track - trackid int(10) unsigned NOT NULL auto_increment, tracktitle varchar(255), tracknumber smallint(5) unsigned, albumid int(10) unsigned artist - artistid int(10) unsigned NOT NULL auto_increment, name varchar(255) artisttrack - artistid int(10) unsigned trackid int(10) unsigned There can multiple artists per track (e.g. the album Back to Back by Duke Ellington and Johnny Hodges). For now, forget about albums that might have different artist lineups per track. I'd like a listing that shows _all_ of the artists on an album. Can this be done in a single query, and in SQL (MySQL) only, without any manipulation by CF after the query? Here's what I have, but it can return only a single artist for the album: SELECT ar.name, a.albumtitle, a.year FROM album a LEFT JOIN track t ON t.album = a.albumid LEFT JOIN artisttrack at ON at.trackid = t.trackid LEFT JOIN artist ar ON ar.artistid = at.artistid GROUP BY a.albumid ORDER BY a.albumtitle; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259317 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
On Friday 04 August 2006 17:19, Kris Jones wrote: (which just doesn't work at all) It was meant to be in a loop. O(N), of course, but that's not so bad with only a few cats. Going with a linking table is probably the right way to go. Better performance too. Very, very, much the +1'ness :-) -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248979 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query problem with lists of values
You can turn this SELECT catid FROM deep_images WHERE catid LIKE '#uid#' OR catid LIKE '#UID#,%' OR catid LIKE '%,#UID#' OR catid LIKE '%,#UID#,%' INTO this and it should do what your looking to do. SELECT catid FROM deep_images WHERE 0=0 AND (catid = '#uid#' OR catid LIKE '%,#uid#,%' OR catid LIKE '%,#uid#' OR catid LIKE '#uid#,%') I know it looks very close to what you posted initially but it it slightly different. -- Casey Dougall Web Applications Developer Ph: 518 743-9424 Fax: 743-0337 Mannix Marketing Inc. 33 Park St. Third Floor, Glens Falls, New York 12801 Marketing to New York State Destinations? We offer quality Travel Industry Directories, Including www.Albany.com, www.LakeGeorge.com www.Saratoga.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248980 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
On Thursday 03 August 2006 18:43, Kris Jones wrote: SELECT catid FROM deep_images WHERE #uid# IN (catid) IN is evil. It will randomly stop working when you hit the db's limit for items in a string list. Which is not fun :-) If you must have comma lists rather than a more sensible layout, why not just: SELECT catid FROM deep_images WHERE #uid# like '%#catid#%' % should match zero or more characters. -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248787 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query problem with lists of values
It will randomly stop working when you hit the db's limit for items in a string list. much like Outlook and its limitation on the length of disclaimers at the bottom of incoming mail... ;-) ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Tom Chiverton [mailto:[EMAIL PROTECTED] Sent: Friday, August 04, 2006 9:30 AM To: CF-Talk Subject: Re: Query problem with lists of values On Thursday 03 August 2006 18:43, Kris Jones wrote: SELECT catid FROM deep_images WHERE #uid# IN (catid) IN is evil. It will randomly stop working when you hit the db's limit for items in a string list. Which is not fun :-) If you must have comma lists rather than a more sensible layout, why not just: SELECT catid FROM deep_images WHERE #uid# like '%#catid#%' % should match zero or more characters. -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248791 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
but one item has entries of 15,16,30,49,50,52,75 and it is being put in
a catecory that has the id of 2 there are lots of these that are
behaving this way. any one think of a way to help with this?
SELECT catid FROM deep_images WHERE #uid# IN (catid)
IN is evil.
It will randomly stop working when you hit the db's limit for items in a
string list.
Which is not fun :-)
If you must have comma lists rather than a more sensible layout, why not just:
SELECT catid
FROM deep_images
WHERE #uid# like '%#catid#%'
% should match zero or more characters.
Yes, IN is not an ideal situation, and certainly hurts performance.
However, using like does not solve his original problem. In your
scenario, that would translate to (using Patrick's own example):
SELECT catid
FROM deep_images
WHERE 2 like '%15,16,30,49,50,52,75%'
(which just doesn't work at all)
But, if you use IN, it _can_ work as expected (if you are passing in
the list value):
SELECT catid
FROM deep_images
WHERE 2 IN (15,16,30,49,50,52,75)
returns no record, since 2 isn't in the set. But if you change #uid#
to 52, it would return the record since 52 is in the set. This works
whether or not the set is quoted. So
SELECT 1
FROM invoice
WHERE 2 in ('15','16','30','49','50','52','75')
works the same way as the previous IN shown above.
Unfortunately, at least in SQL Server, running the above with the list
being a reference to a database column will produce:
Syntax error converting the varchar value '15,16,30,49,50,52,75' to a
column of data type int.
Going with a linking table is probably the right way to go. Better
performance too.
Cheers,
Kris
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248814
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query problem with lists of values
but one item has entries of 15,16,30,49,50,52,75 and it is being put in a category that has the id of 2 there are lots of these that are behaving this way. any one think of a way to help with this? This is happening of course because two of your like clauses catid LIKE '#uid#' AND catid LIKE '#uid#,' means that the 2 will match the 2 in '52,'. This is the difficulty with this kind of set up. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA - | 1 | | - Binary Soduko | | | - C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248737 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query problem with lists of values
How about: SELECT catid FROM deep_images WHERE #uid# IN (catid) Cheers, Kris but one item has entries of 15,16,30,49,50,52,75 and it is being put in a category that has the id of 2 there are lots of these that are behaving this way. any one think of a way to help with this? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248739 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
that is kind of what I thought, but is there any way around this? This is happening of course because two of your like clauses catid LIKE '#uid#' AND catid LIKE '#uid#,' means that the 2 will match the 2 in '52,'. This is the difficulty with this kind of set up. -- Patrick Forsythe Tech Support Smallville Communications http://www.toto.net Guter Rat ist teuer. --Unbekannt ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248740 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
Kris Jones wrote: How about: SELECT catid FROM deep_images WHERE #uid# IN (catid) Cheers, Kris I had tried that originally and sql server didn't like that much at all -- Patrick Forsythe Tech Support Smallville Communications http://www.toto.net Guter Rat ist teuer. --Unbekannt ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248741 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query problem with lists of values
Why don't you use a join table via which to associate your items and categories? Josh -Original Message- From: Patrick Forsythe [mailto:[EMAIL PROTECTED] Sent: Thursday, August 03, 2006 1:47 PM To: CF-Talk Subject: Re: Query problem with lists of values that is kind of what I thought, but is there any way around this? This is happening of course because two of your like clauses catid LIKE '#uid#' AND catid LIKE '#uid#,' means that the 2 will match the 2 in '52,'. This is the difficulty with this kind of set up. -- Patrick Forsythe Tech Support Smallville Communications http://www.toto.net Guter Rat ist teuer. --Unbekannt ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248742 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query problem with lists of values
Patrick, You can create a UDF that allows for list functions. Here's blog post on the topic. http://mkruger.cfwebtools.com/index.cfm?mode=entryentry=87616A7F-D611-F201- A72DB4B567CFA1F7 -Mark -Original Message- From: Patrick Forsythe [mailto:[EMAIL PROTECTED] Sent: Thursday, August 03, 2006 12:49 PM To: CF-Talk Subject: Re: Query problem with lists of values Kris Jones wrote: How about: SELECT catid FROM deep_images WHERE #uid# IN (catid) Cheers, Kris I had tried that originally and sql server didn't like that much at all -- Patrick Forsythe Tech Support Smallville Communications http://www.toto.net Guter Rat ist teuer. --Unbekannt ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248743 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query problem with lists of values
On 8/3/06, Mark A Kruger [EMAIL PROTECTED] wrote: Patrick, You can create a UDF that allows for list functions. Here's blog post on the topic. http://mkruger.cfwebtools.com/index.cfm?mode=entryentry=87616A7F-D611-F201- A72DB4B567CFA1F7 The UDF that Mark is blogging about looks like it will get you what you want, but do keep in mind that if you use a UDF like this in the WHERE clause, it will be executed for every potential row. This might be fine if you have a small dataset, but for a large amount of data, it will slow your query down quite a bit. It would be better to put category information into a separate table, if possible. -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
the category information is in a seperate row these are only the uid s that are stored with the product. Jim Wright wrote: The UDF that Mark is blogging about looks like it will get you what you want, but do keep in mind that if you use a UDF like this in the WHERE clause, it will be executed for every potential row. This might be fine if you have a small dataset, but for a large amount of data, it will slow your query down quite a bit. It would be better to put category information into a separate table, if possible. -- Patrick Forsythe Tech Support Smallville Communications http://www.toto.net Guter Rat ist teuer. --Unbekannt ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248750 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query problem with lists of values
On 8/3/06, Patrick Forsythe [EMAIL PROTECTED] wrote: the category information is in a seperate row these are only the uid s that are stored with the product. Understood...but what you need is an intermediate table which links the two...something like... category table: uid description deep_images table: deepimagesid someothercolumn deep_images_category table: deepimagesid uid Then you would query your table with something like: SELECT someothercolumn FROM deep_images a LEFT JOIN deep_images_category b ON a.uid = b.uid WHERE b.uid = #uid# -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248751 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Problem - sorting
Mike, Thank you. I always forget about the case statement. Best Regards, Dennis Powers UXB Internet- A Website Design and Hosting Company 690 Wolcott Road - P.O. Box 6028 Wolcott, CT 06716 Tel: 203-879-2844 http://www.uxbinternet.com http://dennis.uxb.net -Original Message- From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 10:40 PM To: CF-Talk Subject: RE: Query Problem - sorting From: Dennis Powers [mailto:[EMAIL PROTECTED] SELECT FilesCategory.Cat_Name, Avg(Comments.Numeric1) AS Userrating, Count(Comments.Numeric1) AS Responses FROM (FilesData LEFT JOIN Comments ON FilesData.ID = Comments.LinkID) LEFT JOIN FilesCategory ON FilesData.FilesCategory = FilesCategory.CatID GROUP BY FilesCategory.Cat_Name SELECT FC.Cat_Name, AVG(C.Numeric1) AS Userrating, CASE WHEN COUNT(C.Numeric1) 9 THEN 0 WHEN COUNT(C.Numeric1) 8 THEN 1 END AS Responses FROM FilesData FD LEFT JOIN Comments C ON (FD.ID = C.LinkID) LEFT JOIN FilesCategory FC ON (FD.FilesCategory = FC.CatID) GROUP BY FC.Cat_Name ORDER BY Responses DESC, Userrating DESC ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235063 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem - sorting
From: Dennis Powers [mailto:[EMAIL PROTECTED] SELECT FilesCategory.Cat_Name, Avg(Comments.Numeric1) AS Userrating, Count(Comments.Numeric1) AS Responses FROM (FilesData LEFT JOIN Comments ON FilesData.ID = Comments.LinkID) LEFT JOIN FilesCategory ON FilesData.FilesCategory = FilesCategory.CatID GROUP BY FilesCategory.Cat_Name SELECT FC.Cat_Name, AVG(C.Numeric1) AS Userrating, CASE WHEN COUNT(C.Numeric1) 9 THEN 0 WHEN COUNT(C.Numeric1) 8 THEN 1 END AS Responses FROM FilesData FD LEFT JOIN Comments C ON (FD.ID = C.LinkID) LEFT JOIN FilesCategory FC ON (FD.FilesCategory = FC.CatID) GROUP BY FC.Cat_Name ORDER BY Responses DESC, Userrating DESC ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234780 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: query problem
[empty string] is CF's way of saying NULL, since ColdFusion has not native NULL value. Thus your where clause should be WHERE Publication_Type IS NOT NULL didn't work, but this did. Interestingly, != NULL didn't work. It had to be IS NOT NULL I never would have gotten this. I was thinking down a whole different path. Thanks alot for the help everyone. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219592 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: query problem
[empty string] is CF's way of saying NULL, since ColdFusion has not native NULL value. Thus your where clause should be WHERE Publication_Type IS NOT NULL -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219490 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: query problem
not equals in sql is , that may help On 9/28/05, Daniel Kessler [EMAIL PROTECTED] wrote: I can't seem to see what is wrong with this query. I added the WHERE line and it comes up with no records returned. Without the WHERE, it comes up with two records, but one of them has publication_type as an empty field. I know there are records that aren't empty. Here's the query: CFQUERY NAME=the_type DATASOURCE=dpch select publication_type from publications where publication_type !='' group by publication_type /CFQuery Here's a dump of the db: http://hhp.umd.edu/dbf/publications/db_display.cfm thanks! -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219492 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: query problem
From: Daniel Kessler [mailto:[EMAIL PROTECTED] CFQUERY NAME=the_type DATASOURCE=dpch select publication_type from publications where publication_type !='' group by publication_type /CFQuery If the field is NOT NULL try, where LEN(TRIM(publication_type)) == 0 *note... those are SQL Functions NOT CF ones. If the field is NULL try, where publication_type IS NULL HTH, Mike ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219494 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: query problem
In case the default value returned by the db is an empty string, you may need the belt and the suspenders: where NOT (publication_type IS NULL OR publication_type = '') -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219495 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
CAN YOU FIIP/FLOP VARIABLES AND DATA IN CF? This works in the SQL Query Analyzer. Is there a way to switch it to CF? When I run it I get the following error. [SQLServer JDBC Driver][SQLServer]Invalid column name 'DateFrom'. Because DateFrom is a passed variable. Select * RENTALS r Where NOT EXISTS ( Select 1 From PROPERTYCALENDAR p Where unitCode = r.selectUnit And (@DateFrom BETWEEN arrivalDate AND arrivalDate + nights Or @DateTo BETWEEN arrivalDate AND arrivalDate + nights)) ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213881 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
Impressive use of your noggin. Hopefully I can explain my problem a little better. I have changed my data import to create a date record for every night. Instead of the arrival date and number of nights. I can easily change back it this doesn't provide the results the best way. Below is some sample data. This seems like it should be so easy compared to some of the stuff I work on but I'm completely blocked. unitCode Date mc 2006-08-11 mc 2006-08-12 mc 2006-08-13 mc 2006-08-14 mc 2006-08-15 mc 2006-08-16 mc 2006-08-17 NS19 2005-08-14 NS19 2005-08-15 NS19 2005-08-16 NS19 2005-08-17 NS19 2005-08-18 If my search dates are (2005-8-12) to (2005-08-13) the result should be just NS19. Obviously the between does not work since both have dates outside the search and therefore they both match. Is there a way to group them so if there is one matched date it does not return that property? Thank you again for any help. Doug ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213778 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
This looks like a good use of a Tally table. A Tally table is a table that contains just an ID field with records from 1 to 10,000 (you can use any upper bound you need). You can then join this table to generate queries based on ranges. To test, I created the following table: ID int arrivalDate smalldatetime numDays int Then populated the table with this data: ID arrivalDate numDays --- 1 7/28/2005 2 2 7/20/2005 4 3 7/26/2005 1 Finally, I used the following query to determine the available dates: declare @startDate smalldatetime,@endDate smalldatetime select @startDate = '7/15/2005', @endDate = '7/30/2005' SELECT d.ID, dateAdd(day,(t.id -1),@startDate) as tallyDate, (CASE WHEN d.ID IS NULL THEN 1 ELSE 0 END) as isAvailable, d.arrivalDate, dateAdd(day,d.numDays,d.arrivalDate) as depDate, d.numDays FROMTally as t left outer join testDateRange as d on (dateAdd(day,(t.id-1),@startDate) = d.arrivalDate and dateAdd(day,(t.id-1),@startDate) dateAdd(day,numDays,arrivalDate)) WHERE t.ID = dateDiff(day,@startDate,@endDate) ORDER BY tallyDate Which returned the following: ID tallyDate isAvailable arrivalDate depDatenumDays --- -- --- --- -- --- NULL07/15/05 1 NULLNULL NULL NULL07/16/05 1 NULLNULL NULL NULL07/17/05 1 NULLNULL NULL NULL07/18/05 1 NULLNULL NULL NULL07/19/05 1 NULLNULL NULL 2 07/20/05 0 07/20/0507/24/05 4 2 07/21/05 0 07/20/0507/24/05 4 2 07/22/05 0 07/20/0507/24/05 4 2 07/23/05 0 07/20/0507/24/05 4 NULL07/24/05 1 NULLNULL NULL NULL07/25/05 1 NULLNULL NULL 3 07/26/05 0 07/26/0507/27/05 1 NULL07/27/05 1 NULLNULL NULL 1 07/28/05 0 07/28/0507/30/05 2 1 07/29/05 0 07/28/0507/30/05 2 Any row with a NULL id is an available date. The isAvailable col takes this into account and gives a 1 for available and 0 for not. HTH, Chris [EMAIL PROTECTED] 07/28/05 02:27PM here's a timeline in days (if you don't have fixed-width font, CP into something that does): 123456789 xxx reservation 1 xxx reservation 2 x reservation 3 q desired range there are two possible days available: 4 and 6. That's the problem he's trying to solve. cheers, barneyb On 7/28/05, Jennifer Larkin [EMAIL PROTECTED] wrote: Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213264 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
Change it so it has an arrival date and a departure date? :D On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote: I have 2 tables, one lists properties and the other contains arrival dates and number of nights for those properties. The common variable is 'unitCode'. The search parameters provide an arrival date and departure date. My question. How would you use the arrival date/number of nights to locate availability between two search dates. CFQUERY NAME=Check DATASOURCE=#db# SELECT DISTINCT property_ID FROM Property P LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode WHERE ??? /CFQUERY -- You can't destroy EVERYthing. Where would you sit? The Tick Now blogging http://www.blivit.org/blog/index.cfm http://www.blivit.org/mr_urc/index.cfm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213194 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
I would still have dates between the arrival date and newly created departure date that would need to be handled somehow. Originally, I had looping set up to create a record for every date but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213196 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
I don't think you can do it in a single SQL statement. At least not an efficient one. How about pull the list of reservations that overlap the desired dates, and then creating an array of dates (from arrival to departure), and then loop over the recordset and remove dates from the array that are already taken. When you're done, whatever dates are left are available. cheers, barneyb On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote: I have 2 tables, one lists properties and the other contains arrival dates and number of nights for those properties. The common variable is 'unitCode'. The search parameters provide an arrival date and departure date. My question. How would you use the arrival date/number of nights to locate availability between two search dates. CFQUERY NAME=Check DATASOURCE=#db# SELECT DISTINCT property_ID FROM Property P LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode WHERE ??? /CFQUERY -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213202 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem - Brain Cloud
Can you not use the SQL operator BETWEEN? where thedate BETWEEN arrivaldate AND departuredate ? -Original Message- From: Doug Bedient [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 2:47 PM To: CF-Talk Subject: Re: Query Problem - Brain Cloud I would still have dates between the arrival date and newly created departure date that would need to be handled somehow. Originally, I had looping set up to create a record for every date but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213206 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
Add a vacant field and check to see if vacant is 0 or 1 0 being not
vacant and 1 being vacant?
But the where clause would look something like
Select * from task t
WHERE TO_DATE('#arguments.new_to#','mm/dd/') = t.startDate
AND TO_DATE('#arguments.new_from#','mm/dd/') = t.finishDate
On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote:
I would still have dates between the arrival date and newly created departure
date that would need to be handled somehow. Originally, I had looping set up
to create a record for every date but it seemed like a lot of extra
processing that shouldn't be required.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213209
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem - Brain Cloud
WHERE date BETWEEN startdate AND enddate HTH -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Doug Bedient [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 11:47 AM To: CF-Talk Subject: Re: Query Problem - Brain Cloud I would still have dates between the arrival date and newly created departure date that would need to be handled somehow. Originally, I had looping set up to create a record for every date but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213205 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote: I would still have dates between the arrival date and newly created departure date that would need to be handled somehow. Originally, I had looping set up to create a record for every date but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213210 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
I've seen issues with support for the between operator. I don't recall offhand which database I've used that didn't support it. If between doesn't work, the paired = = is basically equivalent. On 7/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Can you not use the SQL operator BETWEEN? where thedate BETWEEN arrivaldate AND departuredate ? -Original Message- From: Doug Bedient [mailto:[EMAIL PROTECTED] Sent: Thursday, July 28, 2005 2:47 PM To: CF-Talk Subject: Re: Query Problem - Brain Cloud I would still have dates between the arrival date and newly created departure date that would need to be handled somehow. Originally, I had looping set up to create a record for every date but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213212 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
here's a timeline in days (if you don't have fixed-width font, CP into something that does): 123456789 xxx reservation 1 xxx reservation 2 x reservation 3 q desired range there are two possible days available: 4 and 6. That's the problem he's trying to solve. cheers, barneyb On 7/28/05, Jennifer Larkin [EMAIL PROTECTED] wrote: Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213215 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
Then you need a reference table of dates, so you can query what IS NOT there. You can hit this table like this: select date from datetable where date BETWEEN x AND y and date NOT IN (select distinct date from reservation where date between x AND y) right? -jc Barney Boisvert wrote: here's a timeline in days (if you don't have fixed-width font, CP into something that does): 123456789 xxx reservation 1 xxx reservation 2 x reservation 3 q desired range there are two possible days available: 4 and 6. That's the problem he's trying to solve. cheers, barneyb On 7/28/05, Jennifer Larkin [EMAIL PROTECTED] wrote: Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213229 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
Doug Bedient wrote: I have 2 tables, one lists properties and the other contains arrival dates and number of nights for those properties. The common variable is 'unitCode'. The search parameters provide an arrival date and departure date. My question. How would you use the arrival date/number of nights to locate availability between two search dates. CFQUERY NAME=Check DATASOURCE=#db# SELECT DISTINCT property_ID FROM Property P LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode WHERE ??? /CFQUERY I assume you are looking for a property that is available the entire period? In that case: SELECT * FROM Property WHERE unitCode NOT IN ( SELECT unitCode FROM propertyCalendar WHERE (#arrivalDate#, #departureDate#) NOT OVERLAPS (arrivalDate, nights) ) Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213234 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem.
SELECT max(stockid) as stockid, data1, data2 FROM table HAVING data1 = 5 Unfortunately this is returning the following data StockID | Data1 | Data2 --- 3 | 5 | 2 Ie: the correct stockid and data1 but data2 from a diferent row! The MySQL manual warns against that: http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html They also document how it should be done: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group -row.html Yeah, this all started to get a bit complicated, so I dumped it. Worked arround it by selecting the ones I wanted in a new query, setting a flag in the db for those by looping over the new query, then modified the existing query to select based on that flag. Thanks anyway. -- Jay ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211732 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem.
What RDBM are you using? Wouldn't this do it? -- MySQL SELECT * FROM table WHERE data1 = '5' ORDER BY DESC LIMIT 1 -- SQLServer SELECT Top 1 * FROM table WHERE data1 = '5' ORDER BY DESC - Original Message - From: James Smith [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, July 11, 2005 8:09 AM Subject: Query Problem. I have a problem I am sure is simple to solve but it has proved to be beyond me. Imagine I have the following data... StockID | Data1 | Data2 --- 1 | 1 | 2 2 | 3 | 4 3 | 5 | 6 And I want a query to return StockID | Data1 | Data2 --- 3 | 5 | 6 I am trying to use SELECT max(stockid) as stockid, data1, data2 FROM table HAVING data1 = 5 Unfortunately this is returning the following data StockID | Data1 | Data2 --- 3 | 5 | 2 Ie: the correct stockid and data1 but data2 from a diferent row! I have tried using a sub-query but it just crashes the server (trying to do WHERE StockID IN (over 7000 results from sub-query). I am using MySQL and the actual query in question is below for your ref... SELECTs.ItemID, s.ASIN, MAX(q.StockID) AS StockID, q.Condition, q.MaximumSalePrice, q.MinimumSalePrice, q.AddedToFile, q.CurrentAmazonPrice FROM stockitemdetails s JOIN stockquantities q ON (s.ItemID = q.ItemID) WHERE q.Quantity 0 AND s.ASIN != AND s.ASIN IS NOT NULL AND s.ASIN != N/A AND LEFT(s.ASIN,1) != GROUP BY s.ASIN HAVINGAddedToFile= 0 ORDER BY s.ItemID -- Jay ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211531 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem.
What RDBM are you using? Wouldn't this do it? -- MySQL SELECT * FROM table WHERE data1 = '5' ORDER BY DESC LIMIT 1 Unfortunately not. If you view the larger query you will get a better idea, and to explain the process... The query returns about 7500 records. AddedToFile is a flag that is set by this template from 0 (as shown in the query) to 1 (to indicate success). While this template is running other things may reset this flag to 0 to indicate a change to data, then once the program has finished it loops back to the top and performs this query again, this time it should only receive about 20-30 results (just the ones re-set). If the AddedToFile = 0 was moved from the HAVING section to the WHERE section it would change the rows that were returned. IE: if the data was such that the highest stockid had an addedtofile value of 1, using HAVING means it is left out of the result set, moving it toe the WHERE section would include the one below it instead which is not the desired result. Any other ideas? BTW: It is MySQL 4.1.11-nt -- Jay ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211543 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem.
James Smith wrote: SELECT max(stockid) as stockid, data1, data2 FROM table HAVING data1 = 5 Unfortunately this is returning the following data StockID | Data1 | Data2 --- 3 | 5 | 2 Ie: the correct stockid and data1 but data2 from a diferent row! The MySQL manual warns against that: http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html They also document how it should be done: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html I have tried using a sub-query but it just crashes the server Did you file a bug? (trying to do WHERE StockID IN (over 7000 results from sub-query). SELECTs.ItemID, s.ASIN, MAX(q.StockID) AS StockID, q.Condition, q.MaximumSalePrice, q.MinimumSalePrice, q.AddedToFile, q.CurrentAmazonPrice FROM stockitemdetails s JOIN stockquantities q ON (s.ItemID = q.ItemID) WHERE q.Quantity 0 AND s.ASIN != AND s.ASIN IS NOT NULL AND s.ASIN != N/A AND LEFT(s.ASIN,1) != GROUP BY s.ASIN HAVINGAddedToFile= 0 ORDER BY s.ItemID Could you show us the query that would give the right results, but crashes your server? The result of this query is undetermined (which is why every database except MySQL will throw an error, MySQL will happily return the wrong answer) so it is a bit hard to suggest alternatives. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211545 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query problem
this sounds like it should do it for you, but perhaps I'm missing something SELECT memberID, max(paidThru) AS paidThru FROM someTableName ORDER BY memberID cheers, barneyb On Thu, 17 Mar 2005 22:03:03 -0500, Mark Leder [EMAIL PROTECTED] wrote: I have a table with two columns, memberID (integer) and paidThru (date). I'm using SQL 2000. Most rows have only one member ID associated with one paidThru date. Problem is, some of the records have one memberID which can be associated with two or more paidThru dates (the multiple dates per member ID would not be duplicates (not be the same date). What I want to do is return the most recent date only and its associated memberID for all records. I'm stumped on how to do this correctly. I've tried distinct, group by, and max without success. cfquery name=getTransactions datasource=myDataSource SELECT memberID, paidThru FROM someTableName ORDER BY memberID asc /cfquery Thanks in advance. Mark -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199282 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query problem
Tried it, here's the error: [Macromedia][SQLServer JDBC Driver][SQLServer]Column apos;someTableName.memberIDapos; is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Mark -Original Message- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 10:00 PM To: CF-Talk Subject: Re: Query problem this sounds like it should do it for you, but perhaps I'm missing something SELECT memberID, max(paidThru) AS paidThru FROM someTableName ORDER BY memberID cheers, barneyb On Thu, 17 Mar 2005 22:03:03 -0500, Mark Leder [EMAIL PROTECTED] wrote: I have a table with two columns, memberID (integer) and paidThru (date). I'm using SQL 2000. Most rows have only one member ID associated with one paidThru date. Problem is, some of the records have one memberID which can be associated with two or more paidThru dates (the multiple dates per member ID would not be duplicates (not be the same date). What I want to do is return the most recent date only and its associated memberID for all records. I'm stumped on how to do this correctly. I've tried distinct, group by, and max without success. cfquery name=getTransactions datasource=myDataSource SELECT memberID, paidThru FROM someTableName ORDER BY memberID asc /cfquery Thanks in advance. Mark -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199283 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query problem
Whoops. Try this one. Meant 'group by' but typed 'order by'. Sorry about that. SELECT memberID, MAX(paidThru) AS paidThru FROM someTableName GROUP BY memberID cheers, barneyb On Thu, 17 Mar 2005 22:22:41 -0500, Mark Leder [EMAIL PROTECTED] wrote: Tried it, here's the error: [Macromedia][SQLServer JDBC Driver][SQLServer]Column apos;someTableName.memberIDapos; is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Mark -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199284 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query problem
That worked. Thanks Barney. Mark -Original Message- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 10:26 PM To: CF-Talk Subject: Re: Query problem Whoops. Try this one. Meant 'group by' but typed 'order by'. Sorry about that. SELECT memberID, MAX(paidThru) AS paidThru FROM someTableName GROUP BY memberID cheers, barneyb On Thu, 17 Mar 2005 22:22:41 -0500, Mark Leder [EMAIL PROTECTED] wrote: Tried it, here's the error: [Macromedia][SQLServer JDBC Driver][SQLServer]Column apos;someTableName.memberIDapos; is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Mark -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199285 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem
Ahh, the sound of yesterday, the delicious bouquet of simpler times, when I was younger, thinner, flexible, and spent my time in the pursuits of youth... Like sitting at my desk screaming WHY WON'T THIS EFFING THING WORK?!?!?!? Only to realize that I was making the most elemental of mistakes. A missing hash, a missing paren, trying to give JavaScript access to a CF Query object, directly... Simpler times, younger software, Segment Faults in CFStudio, WinNT 4, and mod_coldfusion.so with Apache 1.1 or Window PWS. Funny, almost jarring, how a question on a list can bring back a whole FLOOD of memories... I think back fondly, but very generically, of my earlier days working with CF. This question, for some reason, really REALLY brought back a whole fleet of very specific, very vivid memories. I suppose having found backup CDs from 1998 with my first half-dozen CF apps on them really added to the effect. Anyway, sorry for going OT... but wow, that was trippy. And yeah, you need to HASH (#...#) your CF variables within your CFQUERY blocks. That should be all you need to do, the rest of your query's fine. You DO need to remove the hashes from your assignment operations, though. Bad formation, even though it works. cfset newMonth = Month(DateAdd(n, 1, Now())) cfset newDay = Day(DateAdd(n, 1, Now())) cfset newYear = Year(DateAdd(n, 1, Now())) cfquery name=GetClosings Datasource=#Application.Speck.Codb# Select s.CorpNumber, s.UserID, s.EffectiveDate, s.ReportedDateTime, s.Closed, i.name as CorpName From status s, sites i Where s.corpNumber = i.id and Month(s.EffectiveDate) = #variables.newMonth# and Day(s.EffectiveDate) = #variables.newDay# and Year(s.effectiveDate) = #variables.newYear# and s.closed = 1 Order by CorpName /cfquery I've also taken to using the following format for addressing scoped variables: variables[newMonth] Why? Because it's more flexible in general to use this notation, and doing it most of the time keeps the habit fresh. There are areas where you simply CANNOT use that notation (like assigning an sproc resultset to a var'ed struct) and have to use dotted notation. But using the container[var] syntax makes it possible to use a variable with a string as the second value: if colName = user_id then variables[colName] resolves to variables[user_id]. VERY handy way to use this. It also works for queries using the queryName[colName'][rowIndex] syntax for accessing query data. I hope this helps. Laterz! J On Sun, 6 Mar 2005 22:34:57 -, Adrian Lynch [EMAIL PROTECTED] wrote: Put hashes(pound signs) around your variables: Day(Status.EffectiveDate) = #variables.newDay# and without them the SQL reads as is e.g. a table named variables with a column named newDay. Ade -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://cfobjective.neo.servequake.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197720 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem
use CFQUERYPARAM value=#newMonth# cfsqltype=cf_sql_integer / for both -Original Message- From: Graham Pearson [mailto:[EMAIL PROTECTED] Sent: Sunday, March 06, 2005 4:16 PM To: CF-Talk Subject: Query Problem I am running into a problem which I have not ran into before. Below is my query cfset newMonth = #Month(DateAdd(n, 1, Now()))# cfset newDay = #Day(DateAdd(n, 1, Now()))# cfset newYear = #Year(DateAdd(n, 1, Now()))# cfquery name=GetClosings Datasource=#Application.Speck.Codb# Select status.CorpNumber, status.UserID, status.EffectiveDate, status.ReportedDateTime, status.Closed, sites.name as CorpName From status, sites Where status.corpNumber = sites.id and Month(Status.EffectiveDate) = variables.newMonth and Day(Status.EffectiveDate) = variables.newDay and Year(status.effectiveDate) = variables.newYear and status.closed = 1 Order by CorpName /cfquery When I run the query I get an error message which states: Base table or view not found message from server: Unknown table 'variables' in where clause I have this same query in another place but instead of variables.newMonth I have #Month(Now())#, Day(Now()), and Year(Now()) which gives me the desired results for today. The above query is supposed to give me results for tomorrow when the current time is past 11:59 AM which I am testing via a cfif just above this query in my page. Anyone have suggestions? -- ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197621 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem
Put hashes(pound signs) around your variables: Day(Status.EffectiveDate) = #variables.newDay# and without them the SQL reads as is e.g. a table named variables with a column named newDay. Ade -Original Message- From: Graham Pearson [mailto:[EMAIL PROTECTED] Sent: 06 March 2005 22:14 To: CF-Talk Subject: Query Problem I am running into a problem which I have not ran into before. Below is my query cfset newMonth = #Month(DateAdd(n, 1, Now()))# cfset newDay = #Day(DateAdd(n, 1, Now()))# cfset newYear = #Year(DateAdd(n, 1, Now()))# cfquery name=GetClosings Datasource=#Application.Speck.Codb# Select status.CorpNumber, status.UserID, status.EffectiveDate, status.ReportedDateTime, status.Closed, sites.name as CorpName From status, sites Where status.corpNumber = sites.id and Month(Status.EffectiveDate) = variables.newMonth and Day(Status.EffectiveDate) = variables.newDay and Year(status.effectiveDate) = variables.newYear and status.closed = 1 Order by CorpName /cfquery When I run the query I get an error message which states: Base table or view not found message from server: Unknown table 'variables' in where clause I have this same query in another place but instead of variables.newMonth I have #Month(Now())#, Day(Now()), and Year(Now()) which gives me the desired results for today. The above query is supposed to give me results for tomorrow when the current time is past 11:59 AM which I am testing via a cfif just above this query in my page. Anyone have suggestions? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.6.2 - Release Date: 04/03/2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197623 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query problem
On Thursday 07 Oct 2004 11:22 am, James Smith wrote: To create the appropriate formatting but there are no such list functions in MySQL.Can anyone out there think of a way to do this? Loop over the query, and construct an array to use with queryAddColumn, such that each element in the array is the 'nice' version of the string. -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query problem
how about
SELECT sum(quantity) as TotalQuantity,
RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) as rack,
RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location , '.' ,
2), '.' , -1),2) as shelf
RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2) as box
FROM stockquantities
WHERE Quantity 0
GROUP BY rack,shelf,box
untested!I don't even have MYSQL but have a look here
http://dev.mysql.com/doc/mysql/en/String_functions.html/
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the
final delimiter (counting from the left) is returned. If count is
negative, everything to the right of the final delimiter (counting
from the right) is returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
HTH
Mike
On Thu, 7 Oct 2004 11:22:45 +0100, James Smith [EMAIL PROTECTED] wrote:
I have a fairly simple query that needs some modifications that are beyond
me.
Currently it reads.
SELECT sum(quantity) AS TotalQuantity, Location
FROM stockquantities
WHERE Quantity 0
GROUP BY location
Unfortunately the location field is text in the format xx.xx.xx where x is a
number, for example 01.01.01 or 34.12.02 which means rack 34, shelf 12, box
2.The problem is that being text 01.01.01 is different to 1.1.1 and I need
them to be grouped together in this query.
In CF I could use
numberformat(listfirst(location,.),00) .
To create the appropriate formatting but there are no such list functions in
MySQL.Can anyone out there think of a way to do this?
--
James Smith
[EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: Query problem
With the exception of the missing , after the shelf that works perfectly,
thanks.
--
Jay
-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED]
Sent: 07 October 2004 12:29
To: CF-Talk
Subject: Re: Query problem
how about
SELECT sum(quantity) as TotalQuantity,
RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) as rack,
RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location
, '.' , 2), '.' , -1),2) as shelf
RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2)
as box FROM stockquantities WHERE Quantity 0 GROUP BY rack,shelf,box
untested!I don't even have MYSQL but have a look here
http://dev.mysql.com/doc/mysql/en/String_functions.html/
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count
occurrences of the delimiter delim. If count is positive,
everything to the left of the final delimiter (counting from
the left) is returned. If count is negative, everything to
the right of the final delimiter (counting from the right) is
returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
HTH
Mike
On Thu, 7 Oct 2004 11:22:45 +0100, James Smith
[EMAIL PROTECTED] wrote:
I have a fairly simple query that needs some modifications that are
beyond me.
Currently it reads.
SELECT sum(quantity) AS TotalQuantity, Location FROM
stockquantities
WHERE Quantity 0 GROUP BY location
Unfortunately the location field is text in the format
xx.xx.xx where
x is a number, for example 01.01.01 or 34.12.02 which means
rack 34,
shelf 12, box 2.The problem is that being text 01.01.01
is different
to 1.1.1 and I need them to be grouped together in this query.
In CF I could use
numberformat(listfirst(location,.),00) .
To create the appropriate formatting but there are no such list
functions in MySQL.Can anyone out there think of a way to do this?
--
James Smith
[EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: Query problem
It would have spoilt all your fun if I hadn't made at least one mistake :-)
On Thu, 7 Oct 2004 12:42:36 +0100, James Smith [EMAIL PROTECTED] wrote:
With the exception of the missing , after the shelf that works perfectly,
thanks.
--
Jay
-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED]
Sent: 07 October 2004 12:29
To: CF-Talk
Subject: Re: Query problem
how about
SELECT sum(quantity) as TotalQuantity,
RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) as rack,
RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location
, '.' , 2), '.' , -1),2) as shelf
RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2)
as box FROM stockquantities WHERE Quantity 0 GROUP BY rack,shelf,box
untested!I don't even have MYSQL but have a look here
http://dev.mysql.com/doc/mysql/en/String_functions.html/
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count
occurrences of the delimiter delim. If count is positive,
everything to the left of the final delimiter (counting from
the left) is returned. If count is negative, everything to
the right of the final delimiter (counting from the right) is
returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
HTH
Mike
On Thu, 7 Oct 2004 11:22:45 +0100, James Smith
[EMAIL PROTECTED] wrote:
I have a fairly simple query that needs some modifications that are
beyond me.
Currently it reads.
SELECT sum(quantity) AS TotalQuantity, Location FROM
stockquantities
WHERE Quantity 0 GROUP BY location
Unfortunately the location field is text in the format
xx.xx.xx where
x is a number, for example 01.01.01 or 34.12.02 which means
rack 34,
shelf 12, box 2.The problem is that being text 01.01.01
is different
to 1.1.1 and I need them to be grouped together in this query.
In CF I could use
numberformat(listfirst(location,.),00) .
To create the appropriate formatting but there are no such list
functions in MySQL.Can anyone out there think of a way to do this?
--
James Smith
[EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: Query problem
For anyone interested, the final query is...
SELECT sum(quantity) as TotalQuantity,
cast(RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) AS
UNSIGNED) as rack,
cast(RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location , '.' ,
2), '.' , -1),2) AS UNSIGNED) as shelf,
cast(RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2) AS
UNSIGNED) as box
FROM stockquantities
WHERE Quantity 0
AND Location != 'NULL'
GROUP BY rack,shelf,box
ORDER BY rack,shelf,box
The addition of the cast was required for proper ordering, otherwise they
were ordered as text, i.e.: 1,10,2,20,3,30 etc...
--
Jay
-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED]
Sent: 07 October 2004 15:04
To: CF-Talk
Subject: Re: Query problem
It would have spoilt all your fun if I hadn't made at least
one mistake :-)
On Thu, 7 Oct 2004 12:42:36 +0100, James Smith
[EMAIL PROTECTED] wrote:
With the exception of the missing , after the shelf that works
perfectly, thanks.
--
Jay
-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED]
Sent: 07 October 2004 12:29
To: CF-Talk
Subject: Re: Query problem
how about
SELECT sum(quantity) as TotalQuantity,
RIGHT( '0' + SUBSTRING_INDEX(location , '.' ,
1),2) as rack,
RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location
, '.' , 2), '.' , -1),2) as shelf
RIGHT( '0' + SUBSTRING_INDEX(location , '.' ,
-1),2) as box
FROM stockquantities WHERE Quantity 0 GROUP BY rack,shelf,box
untested!I don't even have MYSQL but have a look here
http://dev.mysql.com/doc/mysql/en/String_functions.html/
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of
the delimiter delim. If count is positive, everything to
the left of
the final delimiter (counting from the left) is returned.
If count
is negative, everything to the right of the final delimiter
(counting from the right) is returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
HTH
Mike
On Thu, 7 Oct 2004 11:22:45 +0100, James Smith
[EMAIL PROTECTED]
wrote:
I have a fairly simple query that needs some modifications that
are beyond me.
Currently it reads.
SELECT sum(quantity) AS TotalQuantity, Location FROM
stockquantities
WHERE Quantity 0 GROUP BY location
Unfortunately the location field is text in the format
xx.xx.xx where
x is a number, for example 01.01.01 or 34.12.02 which means
rack 34,
shelf 12, box 2.The problem is that being text 01.01.01
is different
to 1.1.1 and I need them to be grouped together in this query.
In CF I could use
numberformat(listfirst(location,.),00) .
To create the appropriate formatting but there are no such list
functions in MySQL.Can anyone out there think of a
way to do this?
--
James Smith
[EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: Query problem
in that case you can slim down some of the string stuff that ensures a
single digit has a 0 in front so that 1.1.1 becomes 01.01.01 because 1
and 01 will both cast to the same integer.
On Thu, 7 Oct 2004 16:50:07 +0100, James Smith [EMAIL PROTECTED] wrote:
For anyone interested, the final query is...
SELECT sum(quantity) as TotalQuantity,
cast(RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) AS
UNSIGNED) as rack,
cast(RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location , '.' ,
2), '.' , -1),2) AS UNSIGNED) as shelf,
cast(RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2) AS
UNSIGNED) as box
FROM stockquantities
WHERE Quantity 0
AND Location != 'NULL'
GROUP BY rack,shelf,box
ORDER BY rack,shelf,box
The addition of the cast was required for proper ordering, otherwise they
were ordered as text, i.e.: 1,10,2,20,3,30 etc...
--
Jay
-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED]
Sent: 07 October 2004 15:04
To: CF-Talk
Subject: Re: Query problem
It would have spoilt all your fun if I hadn't made at least
one mistake :-)
On Thu, 7 Oct 2004 12:42:36 +0100, James Smith
[EMAIL PROTECTED] wrote:
With the exception of the missing , after the shelf that works
perfectly, thanks.
--
Jay
-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED]
Sent: 07 October 2004 12:29
To: CF-Talk
Subject: Re: Query problem
how about
SELECT sum(quantity) as TotalQuantity,
RIGHT( '0' + SUBSTRING_INDEX(location , '.' ,
1),2) as rack,
RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location
, '.' , 2), '.' , -1),2) as shelf
RIGHT( '0' + SUBSTRING_INDEX(location , '.' ,
-1),2) as box
FROM stockquantities WHERE Quantity 0 GROUP BY rack,shelf,box
untested!I don't even have MYSQL but have a look here
http://dev.mysql.com/doc/mysql/en/String_functions.html/
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of
the delimiter delim. If count is positive, everything to
the left of
the final delimiter (counting from the left) is returned.
If count
is negative, everything to the right of the final delimiter
(counting from the right) is returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
HTH
Mike
On Thu, 7 Oct 2004 11:22:45 +0100, James Smith
[EMAIL PROTECTED]
wrote:
I have a fairly simple query that needs some modifications that
are beyond me.
Currently it reads.
SELECT sum(quantity) AS TotalQuantity, Location FROM
stockquantities
WHERE Quantity 0 GROUP BY location
Unfortunately the location field is text in the format
xx.xx.xx where
x is a number, for example 01.01.01 or 34.12.02 which means
rack 34,
shelf 12, box 2.The problem is that being text 01.01.01
is different
to 1.1.1 and I need them to be grouped together in this query.
In CF I could use
numberformat(listfirst(location,.),00) .
To create the appropriate formatting but there are no such list
functions in MySQL.Can anyone out there think of a
way to do this?
--
James Smith
[EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: Query Problem
You can't use the alias in the WHERE clause -- you need to write the _expression_ again. Also, you'll probably need to CAST your productCode as an integer depending on your DBMS. Or perhaps simply do a string comparison and change the cfsqltype to cf_sql_varchar. _ From: Mark Leder [mailto:[EMAIL PROTECTED] Sent: Monday, 4 October 2004 6:10 p.m. To: CF-Talk Subject: Query Problem I have a submit form with two text fields (productPrefix and productColorCode). In the SQL db I'm pulling data from, I have to retrieve the first 6 characters from a table column to match to the FORM.productPrefix, and the last two characters from the same column to match the FORM.productColorCode. The code below throws an error, says the aliases (productPrefixNo) is undefined.Is there a way to solve this? === cfset VARIABLES.productCatCode = #Left(FORM.productPrefix, 2)# cfquery name=qProductNo datasource=#REQUEST.dsnSQL# username=#REQUEST.dsnUID# password=#REQUEST.dsnPWD# SELECT L.productID, Left(L.productNumber, 6) AS productPrefixNo, Right(L.productNumber, 2) AS productCode, L.productLineName, L.productColorName, L.productCategory FROM #REQUEST.prefix#_Products_List L WHERE productCategory = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#VARIABLES.productCatCode# AND productPrefixNo =cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#FORM.productPrefix# AND productCode = cfqueryparam cfsqltype=CF_SQL_NUMERIC value=#FORM.productColorCode# /cfquery == Thanks, Mark _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: query Problem
TRANSFORM sum (dp.cantidad) as totalt select sum (dp.cantidad) as total, dp.idproducto as codigo from pedidos p, detallesdepedido dp where p.idpedido=dp.idpedido and p.mesenvio = 7 group by dp.idproducto PIVOT p.diaenvio In CFMX i can make a query like this, if i can how i do the ouput checo I would not recommend using TRANSFORM and PIVOT in a cfquery. These belong to MS Access and are not supported by the SQL standards or by more scalable databases like SQL Server or Oracle. s. isaac dealey954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: query problem
You could convert the single quotes in the string to ASCII or PreserveSingleQuotes() might work (been awhile so I'm not 100% sure on the preserve) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder Director www.cfug-vancouverisland.com - Original Message - From: Chris Edwards [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 8:19 AM Subject: query problem Hi I've done this a 100 times, but now I have a problem... I'm trying to escape the single quote why am I getting this: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''testing\'s', '', '', '', '', '', '', '', '02/26/2003' )'. SQL = INSERT INTO JobListings ( position, reportsto, eduexp, jobknow, skillsabil, workcond, posiavail, deadline, created ) VALUES( 'testing\'s', '', '', '', '', '', '', '', '02/26/2003' ) -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query problem
Use cfqueryparam then you won't have to worry about it anymore. Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: Chris Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:19 AM To: CF-Talk Subject: query problem Hi I've done this a 100 times, but now I have a problem... I'm trying to escape the single quote why am I getting this: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''testing\'s', '', '', '', '', '', '', '', '02/26/2003' )'. SQL = INSERT INTO JobListings ( position, reportsto, eduexp, jobknow, skillsabil, workcond, posiavail, deadline, created ) VALUES( 'testing\'s', '', '', '', '', '', '', '', '02/26/2003' ) -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Problem
Instead of #IIf(Trim(form.confsent) EQ '', No, Yes)#, use #YesNoFormat(Trim(form.confsent) EQ '')# -Original Message- From: Jillian Carroll [mailto:jillian;koskie.com] Sent: Sunday, October 27, 2002 2:04 PM To: CF-Talk Subject: RE: Query Problem When I use cfqueryparam as you specified below, I get this error: An error occurred while evaluating the expression: #IIf(Trim(form.confsent) EQ '', No, Yes)# Error near line 63, column 95. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Query Problem
Jillian Carroll wrote: cfif #form.confsent# EQ ' ' cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = NULL WHERE users_id = #FORM.users_id# /cfquery cfelse cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = '#form.confsent#' WHERE users_id = #FORM.users_id# /cfquery /cfif Use cfqueryparam: cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = cfqueryparam cfsqltype=cf_sql_varchar value=#form.confsent# null=#IIf(Trim(form.confsent) EQ '', No, Yes)# WHERE users_id = cfqueryparam cfsqltype=cf_sql_integer value=#FORM.users_id# /cfquery Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Query Problem
When I use cfqueryparam as you specified below, I get this error: An error occurred while evaluating the expression: #IIf(Trim(form.confsent) EQ '', No, Yes)# Error near line 63, column 95. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Query Problem
Weird... my message got cut off somehow. This should have been included: An error has occurred while processing the expression: Yes Error near line 1, column 1. -Original Message- From: Jillian Carroll [mailto:jillian;koskie.com] Sent: Sunday, October 27, 2002 1:04 PM To: CF-Talk Subject: RE: Query Problem When I use cfqueryparam as you specified below, I get this error: An error occurred while evaluating the expression: #IIf(Trim(form.confsent) EQ '', No, Yes)# Error near line 63, column 95. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Query Problem
Jillian Carroll wrote: cfif #form.confsent# EQ ' ' cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = NULL WHERE users_id = #FORM.users_id# /cfquery cfelse cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = '#form.confsent#' WHERE users_id = #FORM.users_id# /cfquery /cfif Use cfqueryparam: cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = cfqueryparam cfsqltype=cf_sql_varchar value=#form.confsent# null=#IIf(Trim(form.confsent) EQ '', No, Yes)# WHERE users_id = cfqueryparam cfsqltype=cf_sql_integer value=#FORM.users_id# /cfquery May or may not be slightly better off with: cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = cfqueryparam cfsqltype=cf_sql_varchar value=#trim(form.confsent)# null=#yesnoformat(not len(trim(form.confsent)))# WHERE users_id = cfqueryparam cfsqltype=cf_sql_integer value=#FORM.users_id# /cfquery I've never time tested it, but I think yesnoformat(len(x)) may be a slightly more efficient ( and to many developers easier to understand ) than IIF() .. len() evaluates to false if the string is otherwise it evaluates true, so the not will reverse that for the yesnoformat(), so you'll get the same as if myvar eq then null = true else null = false. Not trying to take away from Jochem's suggestion ( which is good and works ) -- jut offering alternatives. :) Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Query Problem
The query below doesn't work... the first clause in the CFIF works fine, but it gives me the error: Error while executing the query (non-fatal); ERROR: Relation form does not exist for the CFELSE portion. Incedentally, this _sounds_ to me like a bug. The syntax of the query certainly looks good -- I can't imagine it not working on CF 3.0-5.0 for Windows. What OS / CF version are you using? You might try removing the # symbols in your cfif statement -- they're not necessary within cf tags and I suppose theoretically could be causing a problem. That's pure conjecture tho. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Query Problem
I'm going out of my mind with this one. I use the EXACT same code to do the initial entry of the user information and it works. It keeps telling me that Relation form does not exist... That leads me to believe one of two things... either it is looking in my database for a table named form... or for some reason when I hit submit on my form on the previous page, the values aren't being transferred. Any reason why the form fields wouldn't be sent along with the form? Hrm. I tried just removing the # symbols from the queries... and no difference. -Original Message- From: S. Isaac Dealey [mailto:info;turnkey.to] Sent: Sunday, October 27, 2002 1:26 PM To: CF-Talk Subject: Re: Query Problem The query below doesn't work... the first clause in the CFIF works fine, but it gives me the error: Error while executing the query (non-fatal); ERROR: Relation form does not exist for the CFELSE portion. Incedentally, this _sounds_ to me like a bug. The syntax of the query certainly looks good -- I can't imagine it not working on CF 3.0-5.0 for Windows. What OS / CF version are you using? You might try removing the # symbols in your cfif statement -- they're not necessary within cf tags and I suppose theoretically could be causing a problem. That's pure conjecture tho. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: Query Problem
Jillian Carroll wrote: When I use cfqueryparam as you specified below, I get this error: An error occurred while evaluating the expression: #IIf(Trim(form.confsent) EQ '', No, Yes)# My mistake: #IIf(Trim(form.confsent) EQ '', DE(No), DE(Yes))# I think you might have to switch the Yes and the No too. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Query Problem
Jillian Carroll wrote: I'm going out of my mind with this one. I use the EXACT same code to do the initial entry of the user information and it works. It keeps telling me that Relation form does not exist... That leads me to believe one of two things... either it is looking in my database for a table named form... or for some reason when I hit submit on my form on the previous page, the values aren't being transferred. It is looking in the database. I would expect that somewhere higher on the page, you have done something like: cfset FORM.users_id = FORM.users_id But even if that is the case and you fix it by removing the quotes, go for cfqueryparam in one way or another. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Query Problem
Do a cfdump var=#form# cfabort before running query. And see if the field you are expecting is there. Is by any chance the field a check box? Those are submitted only if they are checked. You can also try: cfparam name=form.confsent default= at the top of the page but this can hide a problem you may having. Marius Milosav www.scorpiosoft.com It's not about technology, it's about people. Virtual Company (VICO) Application Demo www.scorpiosoft.com/vicodemo/login.cfm - Original Message - From: Jillian Carroll [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Sunday, October 27, 2002 2:29 PM Subject: RE: Query Problem I'm going out of my mind with this one. I use the EXACT same code to do the initial entry of the user information and it works. It keeps telling me that Relation form does not exist... That leads me to believe one of two things... either it is looking in my database for a table named form... or for some reason when I hit submit on my form on the previous page, the values aren't being transferred. Any reason why the form fields wouldn't be sent along with the form? Hrm. I tried just removing the # symbols from the queries... and no difference. -Original Message- From: S. Isaac Dealey [mailto:info;turnkey.to] Sent: Sunday, October 27, 2002 1:26 PM To: CF-Talk Subject: Re: Query Problem The query below doesn't work... the first clause in the CFIF works fine, but it gives me the error: Error while executing the query (non-fatal); ERROR: Relation form does not exist for the CFELSE portion. Incedentally, this _sounds_ to me like a bug. The syntax of the query certainly looks good -- I can't imagine it not working on CF 3.0-5.0 for Windows. What OS / CF version are you using? You might try removing the # symbols in your cfif statement -- they're not necessary within cf tags and I suppose theoretically could be causing a problem. That's pure conjecture tho. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Query Problem
Jillian Carroll wrote: When I use cfqueryparam as you specified below, I get this error: An error occurred while evaluating the expression: #IIf(Trim(form.confsent) EQ '', No, Yes)# My mistake: #IIf(Trim(form.confsent) EQ '', DE(No), DE(Yes))# I think you might have to switch the Yes and the No too. yep, they're reversed... I didn't look that closely before. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Query Problem
How about you replace it with this? cfquery name=adduser datasource=#DSN# UPDATE attendee SET confsent = cfqueryparam cfsqltype=cf_sql_varchar value=#form.confsent# null=#YesNoFormat(form.confsent EQ ' ')# WHERE users_id = cfqueryparam cfsqltype=cf_sql_integer value=#FORM.users_id# /cfquery ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Query Problem
Thank you guys for your help... I'm FINALLY in business with this form! -Original Message- From: S. Isaac Dealey [mailto:info;turnkey.to] Sent: Sunday, October 27, 2002 1:53 PM To: CF-Talk Subject: Re: Query Problem Jillian Carroll wrote: When I use cfqueryparam as you specified below, I get this error: An error occurred while evaluating the expression: #IIf(Trim(form.confsent) EQ '', No, Yes)# My mistake: #IIf(Trim(form.confsent) EQ '', DE(No), DE(Yes))# I think you might have to switch the Yes and the No too. yep, they're reversed... I didn't look that closely before. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Query Problem
You want to display the latest docs before a selection in the drop down? If so, why not just display the latest 5. Assumign your'e using SQL server, try this. cfquery name=q_news datasource=#REQUEST.dsn1# SELECT TOP 5 t_art_category.*, t_committees.*, t_articles.id_art, t_articles.art_title, t_articles.art_category_ID, t_articles.art_subcat_ID, t_articles.art_teaser, t_articles.publication_month, t_articles.publication_year FROM t_art_category, t_committees, t_articles WHERE t_articles.art_category_ID = t_art_category.category_ID AND t_committees.ID_comm = t_articles.art_subcat_ID ORDER BY t_articles.publication_year DESC, t_articles.publication_month DESC, cat_order /cfquery -Original Message- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: 19 August 2002 12:40 To: CF-Talk Subject: Query Problem Good Morning all, I've set up a newsletter db which permits my client to enter in articles and select the current month / year of publication from a select box. There could be 5 - 6 articles posted for, say August 2002. Since those articles are the most recent, I want them to display in a CFOUTPUT. So in September, when new articles get posted, the most recent month becomes the current issue. Problem is, I've tried using a MAX(dbfield) SQL query statement without success. Can a MAX statement pull more than one database row (single article) or am I trying to do a query the wrong way? What would be the best way to write the statement? Here's what I have now: cfquery name=q_news datasource=#REQUEST.dsn1# SELECT t_art_category.*, t_committees.*, t_articles.id_art, t_articles.art_title, t_articles.art_category_ID, t_articles.art_subcat_ID, t_articles.art_teaser, t_articles.publication_month, t_articles.publication_year FROM t_art_category, t_committees, t_articles WHERE t_articles.art_category_ID = t_art_category.category_ID AND t_committees.ID_comm = t_articles.art_subcat_ID AND t_articles.publication_month = MAX(t_articles.publication_month) AS newPubMonth AND t_articles.publication_year = MAX(t_articles.publication_year) AS newPubYear ORDER BY cat_order Thanks, Mark __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm 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: Query Problem
The only issue here is that the amount of articles could vary from month to month. Thanks, Mark -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 7:56 AM To: CF-Talk Subject: RE: Query Problem You want to display the latest docs before a selection in the drop down? If so, why not just display the latest 5. Assumign your'e using SQL server, try this. cfquery name=q_news datasource=#REQUEST.dsn1# SELECT TOP 5 t_art_category.*, t_committees.*, t_articles.id_art, t_articles.art_title, t_articles.art_category_ID, t_articles.art_subcat_ID, t_articles.art_teaser, t_articles.publication_month, t_articles.publication_year FROM t_art_category, t_committees, t_articles WHERE t_articles.art_category_ID = t_art_category.category_ID AND t_committees.ID_comm = t_articles.art_subcat_ID ORDER BY t_articles.publication_year DESC, t_articles.publication_month DESC, cat_order /cfquery -Original Message- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: 19 August 2002 12:40 To: CF-Talk Subject: Query Problem Good Morning all, I've set up a newsletter db which permits my client to enter in articles and select the current month / year of publication from a select box. There could be 5 - 6 articles posted for, say August 2002. Since those articles are the most recent, I want them to display in a CFOUTPUT. So in September, when new articles get posted, the most recent month becomes the current issue. Problem is, I've tried using a MAX(dbfield) SQL query statement without success. Can a MAX statement pull more than one database row (single article) or am I trying to do a query the wrong way? What would be the best way to write the statement? Here's what I have now: cfquery name=q_news datasource=#REQUEST.dsn1# SELECT t_art_category.*, t_committees.*, t_articles.id_art, t_articles.art_title, t_articles.art_category_ID, t_articles.art_subcat_ID, t_articles.art_teaser, t_articles.publication_month, t_articles.publication_year FROM t_art_category, t_committees, t_articles WHERE t_articles.art_category_ID = t_art_category.category_ID AND t_committees.ID_comm = t_articles.art_subcat_ID AND t_articles.publication_month = MAX(t_articles.publication_month) AS newPubMonth AND t_articles.publication_year = MAX(t_articles.publication_year) AS newPubYear ORDER BY cat_order Thanks, Mark __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm 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: Query Problem
Remove the 'top 5' and add a 'where' clause... WHERE t_articles.publication_year = #year(now())# AND t_articles.publication_month = #month(now())# OR in SQl server you could try.. WHERE t_articles.publication_year = year(Getdate()) AND t_articles.publication_month = month(getdate()) That will give you all articles from the current month. Altough it's highly likley that early in the month you'll get 0 records. I'd store and actual publication date to be honest, rather than 2 separate fields (year/month). -Original Message- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: 19 August 2002 13:22 To: CF-Talk Subject: RE: Query Problem The only issue here is that the amount of articles could vary from month to month. Thanks, Mark -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 7:56 AM To: CF-Talk Subject: RE: Query Problem You want to display the latest docs before a selection in the drop down? If so, why not just display the latest 5. Assumign your'e using SQL server, try this. cfquery name=q_news datasource=#REQUEST.dsn1# SELECT TOP 5 t_art_category.*, t_committees.*, t_articles.id_art, t_articles.art_title, t_articles.art_category_ID, t_articles.art_subcat_ID, t_articles.art_teaser, t_articles.publication_month, t_articles.publication_year FROM t_art_category, t_committees, t_articles WHERE t_articles.art_category_ID = t_art_category.category_ID AND t_committees.ID_comm = t_articles.art_subcat_ID ORDER BY t_articles.publication_year DESC, t_articles.publication_month DESC, cat_order /cfquery -Original Message- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: 19 August 2002 12:40 To: CF-Talk Subject: Query Problem Good Morning all, I've set up a newsletter db which permits my client to enter in articles and select the current month / year of publication from a select box. There could be 5 - 6 articles posted for, say August 2002. Since those articles are the most recent, I want them to display in a CFOUTPUT. So in September, when new articles get posted, the most recent month becomes the current issue. Problem is, I've tried using a MAX(dbfield) SQL query statement without success. Can a MAX statement pull more than one database row (single article) or am I trying to do a query the wrong way? What would be the best way to write the statement? Here's what I have now: cfquery name=q_news datasource=#REQUEST.dsn1# SELECT t_art_category.*, t_committees.*, t_articles.id_art, t_articles.art_title, t_articles.art_category_ID, t_articles.art_subcat_ID, t_articles.art_teaser, t_articles.publication_month, t_articles.publication_year FROM t_art_category, t_committees, t_articles WHERE t_articles.art_category_ID = t_art_category.category_ID AND t_committees.ID_comm = t_articles.art_subcat_ID AND t_articles.publication_month = MAX(t_articles.publication_month) AS newPubMonth AND t_articles.publication_year = MAX(t_articles.publication_year) AS newPubYear ORDER BY cat_order Thanks, Mark __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm 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: Query Problem
Try using single quotes around 'Parametric'. -Original Message- From: ronmyers [SMTP:[EMAIL PROTECTED]] Sent: 30 April 2002 14:41 To: CF-Talk Subject: Query Problem Can someone tell my why I am getting this error. If I take the WHERE line out it returns all the data just fine Thanks Ron ODBC Error Code = 07001 (Wrong number of parameters) [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (5:70) in the template file e:\ns-server\docs\webapps\MFG\Probe\CardTrack\Card_Info\ParamOutput.cfm. cfquery name=pcards dbtype = dynamic ConnectString=DRIVER=Microsoft Access Driver (*.mdb); DBQ=\\bodata.micron.com\webapps\MFG\Probe\prbcardtrack\prbcards.mdb; SELECT Card_Type FROM tblCardData WHERE Card_Type=Parametric; /cfquery __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm 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: Query Problem
also, make sure Card_Type is really the name of the field (check spelling) in the table tblCardData. this is the error i always see when i misspell a field name. not that it happens often... :) v/r, Jeff -Original Message- From: Paul Bowley [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 9:52 AM To: CF-Talk Subject: RE: Query Problem Try using single quotes around 'Parametric'. -Original Message- From: ronmyers [SMTP:[EMAIL PROTECTED]] Sent: 30 April 2002 14:41 To: CF-Talk Subject: Query Problem Can someone tell my why I am getting this error. If I take the WHERE line out it returns all the data just fine Thanks Ron ODBC Error Code = 07001 (Wrong number of parameters) [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (5:70) in the template file e:\ns-server\docs\webapps\MFG\Probe\CardTrack\Card_Info\ParamOutput.cfm. cfquery name=pcards dbtype = dynamic ConnectString=DRIVER=Microsoft Access Driver (*.mdb); DBQ=\\bodata.micron.com\webapps\MFG\Probe\prbcardtrack\prbcards.mdb; SELECT Card_Type FROM tblCardData WHERE Card_Type=Parametric; /cfquery __ Get the mailserver that powers this list at http://www.coolfusion.com 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: Query Problem
Thanks it worked Getting up to early to see straight -Original Message- From: Paul Bowley [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 7:52 AM To: CF-Talk Subject: RE: Query Problem Try using single quotes around 'Parametric'. -Original Message- From: ronmyers [SMTP:[EMAIL PROTECTED]] Sent: 30 April 2002 14:41 To: CF-Talk Subject: Query Problem Can someone tell my why I am getting this error. If I take the WHERE line out it returns all the data just fine Thanks Ron ODBC Error Code = 07001 (Wrong number of parameters) [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (5:70) in the template file e:\ns-server\docs\webapps\MFG\Probe\CardTrack\Card_Info\ParamOutput.cfm. cfquery name=pcards dbtype = dynamic ConnectString=DRIVER=Microsoft Access Driver (*.mdb); DBQ=\\bodata.micron.com\webapps\MFG\Probe\prbcardtrack\prbcards.mdb; SELECT Card_Type FROM tblCardData WHERE Card_Type=Parametric; /cfquery __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm 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: query problem
All working nowthe delete date field was having a problem when no data
was being added.
Cheers
Will
-Original Message-
From: Will Swain [mailto:[EMAIL PROTECTED]]
Sent: 21 February 2002 11:30
To: CF-Talk
Subject: query problem
Hi all,
Ok, having an annoying little problem, and I'm sure that it's something
really obvious that I should know, but I guess I have just looked at it for
too long.
This is my query:
cfquery name=addevent datasource=#Request.App.dsn#
INSERT INTO tbl_events(name, borough, full_address, event_date, event_time,
description, delete_date, authorised)
VALUES ('#Form.name#', #Form.borough#, '#Form.full_address#',
#Form.event_date#, '#Form.event_time#', '#Form.description#',
#Form.delete_date#, '#Form.authorised#')
/cfquery
The field borough is a number type and the two date fields are date types.
This is the error:
Error Code: -3502 SQL State: 37000 Detail: [Microsoft][ODBC Microsoft Access
Driver] Syntax error in INSERT INTO statement.
SQL = INSERT INTO tbl_events(name, borough, full_address, event_date,
event_time, description, delete_date, authorised) VALUES ('fdhdfh', 1,
'sdfhsdfh', 12/08/02, '', 'asfasf', , 'yes')
Any ideas?
Will Swain
Tecnical Director
Hot Horse Ltd
e: [EMAIL PROTECTED]
t: 01273 675375
This email is intended for the recipient only and contains confidential
information, some or all of which may be legally privileged. If you are not
the intended recipient, you must not use, save, disclose, distribute, copy,
print or rely on this email or any information contained within it. Please
notify the sender by return and delete it from your computer. Thank you.
__
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: query problem
Will,
Your date needs to be an ODBC Date.
Use CreateODBCDate() on your Date form field and CreateODBCTime() on your
Time form Field.
Regards
Stephen
- Original Message -
From: Will Swain [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, February 21, 2002 11:29 AM
Subject: query problem
Hi all,
Ok, having an annoying little problem, and I'm sure that it's something
really obvious that I should know, but I guess I have just looked at it
for
too long.
This is my query:
cfquery name=addevent datasource=#Request.App.dsn#
INSERT INTO tbl_events(name, borough, full_address, event_date,
event_time,
description, delete_date, authorised)
VALUES ('#Form.name#', #Form.borough#, '#Form.full_address#',
#Form.event_date#, '#Form.event_time#', '#Form.description#',
#Form.delete_date#, '#Form.authorised#')
/cfquery
The field borough is a number type and the two date fields are date types.
This is the error:
Error Code: -3502 SQL State: 37000 Detail: [Microsoft][ODBC Microsoft
Access
Driver] Syntax error in INSERT INTO statement.
SQL = INSERT INTO tbl_events(name, borough, full_address, event_date,
event_time, description, delete_date, authorised) VALUES ('fdhdfh', 1,
'sdfhsdfh', 12/08/02, '', 'asfasf', , 'yes')
Any ideas?
Will Swain
Tecnical Director
Hot Horse Ltd
e: [EMAIL PROTECTED]
t: 01273 675375
This email is intended for the recipient only and contains confidential
information, some or all of which may be legally privileged. If you are
not
the intended recipient, you must not use, save, disclose, distribute,
copy,
print or rely on this email or any information contained within it. Please
notify the sender by return and delete it from your computer. Thank you.
__
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: query problem
Hi Stephenalready done that. The problem, I think, was that the delete
date field was spazzing out when it was empty, causing the error. I'm
putting a default date in there now, if none is entered, and it seems to
work fine.
Cheers
Will
-Original Message-
From: Stephen Moretti [mailto:[EMAIL PROTECTED]]
Sent: 21 February 2002 12:42
To: CF-Talk
Subject: Re: query problem
Will,
Your date needs to be an ODBC Date.
Use CreateODBCDate() on your Date form field and CreateODBCTime() on your
Time form Field.
Regards
Stephen
- Original Message -
From: Will Swain [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, February 21, 2002 11:29 AM
Subject: query problem
Hi all,
Ok, having an annoying little problem, and I'm sure that it's something
really obvious that I should know, but I guess I have just looked at it
for
too long.
This is my query:
cfquery name=addevent datasource=#Request.App.dsn#
INSERT INTO tbl_events(name, borough, full_address, event_date,
event_time,
description, delete_date, authorised)
VALUES ('#Form.name#', #Form.borough#, '#Form.full_address#',
#Form.event_date#, '#Form.event_time#', '#Form.description#',
#Form.delete_date#, '#Form.authorised#')
/cfquery
The field borough is a number type and the two date fields are date types.
This is the error:
Error Code: -3502 SQL State: 37000 Detail: [Microsoft][ODBC Microsoft
Access
Driver] Syntax error in INSERT INTO statement.
SQL = INSERT INTO tbl_events(name, borough, full_address, event_date,
event_time, description, delete_date, authorised) VALUES ('fdhdfh', 1,
'sdfhsdfh', 12/08/02, '', 'asfasf', , 'yes')
Any ideas?
Will Swain
Tecnical Director
Hot Horse Ltd
e: [EMAIL PROTECTED]
t: 01273 675375
This email is intended for the recipient only and contains confidential
information, some or all of which may be legally privileged. If you are
not
the intended recipient, you must not use, save, disclose, distribute,
copy,
print or rely on this email or any information contained within it. Please
notify the sender by return and delete it from your computer. Thank you.
__
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: QUERY PROBLEM
Date is probably a date/time function. So your definition of 'today' needs to be a little more exact. My point being, you probably have the date (4/9/2001) stored as: 04/09/2001 00:00:00.000 and when you do the now() function Access is seeing that as 04/09/2001 15:30:29.001 which is actually later then the date you have stored. This will always be something to look for with datetime fields. I work mostly with SQL, not Access, so this may or not be the case for you. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 2:53 PM To: CF-Talk Subject: QUERY PROBLEM I am trying to pull the next baseball game from a schedule I have in an Access database. When it processes it pulls the next game to a degree. If there is a game today it will not pull that game. Example there is a game today 4/9/2001 and the ext game it displays is 4/10/2001 Any ideas? cfset today = dateformat(NOW(), 'm/d/') CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = #today# /CFQUERY ALSO HAD IT AS CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = NOW() /CFQUERY Thanks. Brian Hasselback [EMAIL PROTECTED] http://www.intent.net http://www.hasselback.com Web Hosting Development (502) 452-1851 ~~ 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: QUERY PROBLEM
Comparing dates are fun - here's a couple suggestions: -check your formatting of the date object you're comparing and format your where-clause date to be exactly the same format as appears in the DB (i.e. smalldate time, ODBC d/t obj. etc.); relying on the auto conversion within ODBC has yielded unpredictable results for me... -Make sure it's a Date/Time object in the DB... -Pass in (as defaults if necessary) all date/time elements that exist in the DB data (i.e. if seconds exist, have them in the compare value even if its :00... -If you want all games later than today, get rid of the = in your where clause(s) -If you only want the next (1) record, use the "top" function in the SQL to get the first record -It will help performance if you set up an index on your dates in the DB table... Hope these quick comments help. If I think of others I'll post 'em! Truman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 12:53 PM To: CF-Talk Subject: QUERY PROBLEM I am trying to pull the next baseball game from a schedule I have in an Access database. When it processes it pulls the next game to a degree. If there is a game today it will not pull that game. Example there is a game today 4/9/2001 and the ext game it displays is 4/10/2001 Any ideas? cfset today = dateformat(NOW(), 'm/d/') CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = #today# /CFQUERY ALSO HAD IT AS CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = NOW() /CFQUERY Thanks. Brian Hasselback [EMAIL PROTECTED] http://www.intent.net http://www.hasselback.com Web Hosting Development (502) 452-1851 ~~ 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: QUERY PROBLEM
My formatting for the dates in the database is m/d/ It matches my today variable. Also if there isnt a game today I want tit to display the next game. It is a Date/Time object in the database. Thanks -Original Message- From: Truman Esmond III [mailto:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 3:24 PM To: CF-Talk Subject: RE: QUERY PROBLEM Comparing dates are fun - here's a couple suggestions: -check your formatting of the date object you're comparing and format your where-clause date to be exactly the same format as appears in the DB (i.e. smalldate time, ODBC d/t obj. etc.); relying on the auto conversion within ODBC has yielded unpredictable results for me... -Make sure it's a Date/Time object in the DB... -Pass in (as defaults if necessary) all date/time elements that exist in the DB data (i.e. if seconds exist, have them in the compare value even if its :00... -If you want all games later than today, get rid of the = in your where clause(s) -If you only want the next (1) record, use the "top" function in the SQL to get the first record -It will help performance if you set up an index on your dates in the DB table... Hope these quick comments help. If I think of others I'll post 'em! Truman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 12:53 PM To: CF-Talk Subject: QUERY PROBLEM I am trying to pull the next baseball game from a schedule I have in an Access database. When it processes it pulls the next game to a degree. If there is a game today it will not pull that game. Example there is a game today 4/9/2001 and the ext game it displays is 4/10/2001 Any ideas? cfset today = dateformat(NOW(), 'm/d/') CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = #today# /CFQUERY ALSO HAD IT AS CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = NOW() /CFQUERY Thanks. Brian Hasselback [EMAIL PROTECTED] http://www.intent.net http://www.hasselback.com Web Hosting Development (502) 452-1851 ~~ 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: QUERY PROBLEM
Order BY GDATE 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 2:53 PM To: CF-Talk Subject: QUERY PROBLEM I am trying to pull the next baseball game from a schedule I have in an Access database. When it processes it pulls the next game to a degree. If there is a game today it will not pull that game. Example there is a game today 4/9/2001 and the ext game it displays is 4/10/2001 Any ideas? cfset today = dateformat(NOW(), 'm/d/') CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = #today# /CFQUERY ALSO HAD IT AS CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = NOW() /CFQUERY Thanks. Brian Hasselback [EMAIL PROTECTED] http://www.intent.net http://www.hasselback.com Web Hosting Development (502) 452-1851 ~~ 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: QUERY PROBLEM
Hi, The default format of date function is date with time. Ms-Access has also the same format. If you want to compare two date values, change the target value to default format and make the comparision. Like cfset today=Now() which is the default for both CF MS-Access. Now try with this default format, good luck, Regards kishore - Original Message - From: [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Tuesday, April 10, 2001 12:23 AM Subject: QUERY PROBLEM I am trying to pull the next baseball game from a schedule I have in an Access database. When it processes it pulls the next game to a degree. If there is a game today it will not pull that game. Example there is a game today 4/9/2001 and the ext game it displays is 4/10/2001 Any ideas? cfset today = dateformat(NOW(), 'm/d/') CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = #today# /CFQUERY ALSO HAD IT AS CFQUERY name="NextGame" datasource="#dsource#" maxrows="1" SELECT * FROM schedule WHERE gdate = NOW() /CFQUERY Thanks. Brian Hasselback [EMAIL PROTECTED] http://www.intent.net http://www.hasselback.com Web Hosting Development (502) 452-1851 ~~ 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: Query problem
Will, Are you using access? ID is a reserved word, I think. So, number one would be to change the name of your field in the table. Also, is ID in members the same as id in fields? If so, you can do something like this: SELECTm.email, m.id FROMmembers m, main_details d WHERE(d.body LIKE '%m.watch1%' ORd.body LIKE '%m.watch2%' ORd.body LIKE '%m.watch3%') ANDd.id = m.id Make sense? -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 ~~ 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
