Re: query help
Hard to say what is going on. The following works fine for me: SELECT node.userCity,count(node.userCity) FROM core.users AS node, core.users AS parent WHERE parent.userName = node.userFirstName AND node.userCity = 'Pasadena' GROUP BY node.userCity I seem to get the correct number based on the node.userCity where clause. On Fri, Jun 20, 2008 at 4:23 PM, Jessica Kennedy <[EMAIL PROTECTED]> wrote: > no luck... > > I do need the group by clause, my actual query is much larger than the one > posted. the problem, for example is this: > > for five people, paid="no", for five others, paid="yes" under one parent > sponsor. > I am now getting a result of "10", but I need the result to be "5", only > counting those people under sponsor who have paid. > > what am I doing wrong?? =( > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307865 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 help
no luck... I do need the group by clause, my actual query is much larger than the one posted. the problem, for example is this: for five people, paid="no", for five others, paid="yes" under one parent sponsor. I am now getting a result of "10", but I need the result to be "5", only counting those people under sponsor who have paid. what am I doing wrong?? =( ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307864 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 help
> SELECT COUNT(node.name) > FROM tbl1 as node, tbl1 as parent > WHERE parent.sponsor=node.name AND node.paid='yes' > GROUP BY node.name GROUP BY should only be used if you're selecting non-aggregates along with your aggregate. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307633 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 help
Well, is the paid field a character field or a number field in the DB? and just FYI, you can always go #querryName.Recordcount# to get a count via CF. :) On Tue, Jun 17, 2008 at 3:23 PM, Jessica Kennedy <[EMAIL PROTECTED]> wrote: > I'm pretty sure I will smack my head when I hear the answer, but I'll ask > anyway... > > I am using a select count() query to get the number of people directly > sponsored by a person... this works fine, the problem is that I only want to > have the query count people that meet a certain qualification. There has to > be a simple way to do this, my query pretty much looks like this: > > SELECT COUNT(node.name) > FROM tbl1 as node, tbl1 as parent > WHERE parent.sponsor=node.name AND node.paid='yes' > GROUP BY node.name > > the "AND node.paid='yes'" seems to be doing nothing... I'm pretty sure I'm > missing something really simple, help! > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307632 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 Help - include
daniel kessler wrote: > I had continued to try and figure it out after I sent the email. I did > figure out a way to make it work. > > FROM expenditures e, people p > WHERE e.approved_by = people.id (+) > > In Oracle, the + does the outer join. Yes, that is the original outer join syntax for Oracle. The ... OUTER JOIN ... ON ... syntax is the SQL 92 Standard syntax. Oracle didn't adopt this standard until Version 9.x IIRC. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304990 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 Help - include
I had continued to try and figure it out after I sent the email. I did figure out a way to make it work. FROM expenditures e, people p WHERE e.approved_by = people.id (+) In Oracle, the + does the outer join. And it worked well, but it wasn't explicit. I don't know sql well enough to read that alter and go, "oh, I'm doing an outer join there". I wanted explicit and that's what your code gave me. Interestingly, it happens in the FROM rather than the WHERE cause of the ON. thank you. > Yes it will be an LEFT or RIGHT outer join. The direction determines > which table you want ALL records from. So if the expense table is on > the left of the join use expense LEFT OUTER JOIN people on aField = > bField. If yo want it on the right then it would be people RIGHT OTER > > JOIN expense on aField = bField. Thank you too for the explanation. It was helpful. And the concatenation worked great too. daniel ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304988 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 Help - include
daniel kessler wrote: > 1 - I'm not so good at sql and the join only works if there's an id in the > approved_by field. This is going to be empty unless the ticket has been > approved, but I still want it to work whether it's approved or not. Is that > an outter join? > Yes it will be an LEFT or RIGHT outer join. The direction determines which table you want ALL records from. So if the expense table is on the left of the join use expense LEFT OUTER JOIN people on aField = bField. If yo want it on the right then it would be people RIGHT OTER JOIN expense on aField = bField. > 2. Currently, I do p.fname AS approved_by_fname,p.fname AS > approved_by_lname. I'd like it to be one variable though, but I'm not sure > how to format p.fname + p.lname AS approved_by_name. > In Oracle '||' is the concatenate operator so it would be p.fname || ' ' || p.lname AS approved_by_name. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304982 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 Help Please
>I dont believe this is working with Access DB > SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound I think Access's version of CASE is IIF(..). Try using IIF instead and also try the query Barney suggested. One of them should work. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304392 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 Help Please
I dont believe this is working with Access DB SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound C S <[EMAIL PROTECTED]> wrote: >select *, exists ( >select * >from orders >where date between #date1# and #date2# >) as hasOrdered >from customers >order by name, id > >Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROM tblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304382 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 Help Please
>select *, exists ( >select * >from orders >where date between #date1# and #date2# >) as hasOrdered >from customers >order by name, id > >Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROMtblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304381 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 Help Please
select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. cheers, barneyb On Mon, Apr 28, 2008 at 8:57 PM, Brian Sheridan <[EMAIL PROTECTED]> wrote: > I will try to make this as simple as possible. Any help would be greatly > appreciated. I have 2 tables like below. > > tblCustomers > === > ID | Name | Email | > 1, John, [EMAIL PROTECTED] > 2, Bob, [EMAIL PROTECTED] > 3, Steve, [EMAIL PROTECTED] > === > > > tblOrders > === > UserID | Date| > 1, 12/22/2007 > 3, 1/2/2008 > > === > > > I want to do a query like a scorecard view, that will list all CUSTOMERS > and show "yes or "no" if they had an order bewtween 2 dates. > > > > RESULTS WOULD BE LIKE THIS IF I SEARCH BETWEEN 12/01/2007 - 1/30/2008 > - > ID | Name | Email | > 1, John, YES > 2, Bob, NO > 3, Steve, YES > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304375 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 help
Did that work for you? On Mon, Mar 10, 2008 at 12:25 PM, Sonny Savage <[EMAIL PROTECTED]> wrote: > Now that I understand your data structures, I understand how to solve the > problem. I hope this works on MySQL. I tested it using OpenOffice.orgBase. > > SELECT users.user_id > , users.user_name > , docs.doc_id > , docs.doc_name > , user_docs.signoff_id > FROM users, docs > LEFT OUTER JOIN user_docs > ON users.user_id = user_docs.user_id > AND docs.doc_id = user_docs.doc_id > WHERE user_docs.signoff_id IS NULL > > > On Mon, Mar 10, 2008 at 10:01 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: > > > I think you've made a bit of an error there. With inner joins, the query > > returns no results. This is what I expected. Using a left outer join > > also > > returns no rows. This makes sense as you're maintaining the rows from > > the > > wrong table. Changing it to a right outer join creates a cross > > dependency > > error. > > > > The user table has all of the users. The doc table has all of the docs. > > The > > user_doc table has one entry for each time a user has signed off on > > having > > read a document. A row is inserted with the user id and the doc id. > > > > What I'm trying to return is the users who have not signed off on > > viewing > > one or more documents and the documents on which they've failed to sign > > off. > > > > > > > > > > > > > > Subject: query help > > From: Sonny Savage > > Date: Mon, 10 Mar 2008 08:39:57 -0400 > > Thread: > > > > http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55471#300843 > > > > Question: Do you also want a result in the case where there is no > > USER_DOCS > > record for a given USER and DOC record (FULL OUTER JOIN)? > > > > On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: > > - Excess quoted text cut - see Original Post for more - > > > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300928 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 help
Now that I understand your data structures, I understand how to solve the problem. I hope this works on MySQL. I tested it using OpenOffice.orgBase. SELECT users.user_id , users.user_name , docs.doc_id , docs.doc_name , user_docs.signoff_id FROM users, docs LEFT OUTER JOIN user_docs ON users.user_id = user_docs.user_id AND docs.doc_id = user_docs.doc_id WHERE user_docs.signoff_id IS NULL On Mon, Mar 10, 2008 at 10:01 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: > I think you've made a bit of an error there. With inner joins, the query > returns no results. This is what I expected. Using a left outer join also > returns no rows. This makes sense as you're maintaining the rows from the > wrong table. Changing it to a right outer join creates a cross dependency > error. > > The user table has all of the users. The doc table has all of the docs. > The > user_doc table has one entry for each time a user has signed off on having > read a document. A row is inserted with the user id and the doc id. > > What I'm trying to return is the users who have not signed off on viewing > one or more documents and the documents on which they've failed to sign > off. > > > > > > > Subject: query help > From: Sonny Savage > Date: Mon, 10 Mar 2008 08:39:57 -0400 > Thread: > > http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55471#300843 > > Question: Do you also want a result in the case where there is no > USER_DOCS > record for a given USER and DOC record (FULL OUTER JOIN)? > > On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: > - Excess quoted text cut - see Original Post for more - > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300859 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 help
I think you've made a bit of an error there. With inner joins, the query returns no results. This is what I expected. Using a left outer join also returns no rows. This makes sense as you're maintaining the rows from the wrong table. Changing it to a right outer join creates a cross dependency error. The user table has all of the users. The doc table has all of the docs. The user_doc table has one entry for each time a user has signed off on having read a document. A row is inserted with the user id and the doc id. What I'm trying to return is the users who have not signed off on viewing one or more documents and the documents on which they've failed to sign off. Subject: query help From: Sonny Savage Date: Mon, 10 Mar 2008 08:39:57 -0400 Thread: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55471#300843 Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: - Excess quoted text cut - see Original Post for more - ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300853 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 help
This query will return all users and docs that have a relationship established but don't have a sign-off record: SELECT u.user_name , d.doc_name FROM user_docs ud INNER JOIN users u ON ud.user_id = u.user_id INNER JOIN docs d ON ud.doc_id = d.doc_id WHERE ud.signoff_id IS NULL It will not return records that have non-matching foreign keys (non-existent user or doc). Converting the INNER JOINS to LEFT OUTER JOINS would change that behavior. On Mon, Mar 10, 2008 at 8:39 AM, Sonny Savage <[EMAIL PROTECTED]> wrote: > Question: Do you also want a result in the case where there is no > USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? > > > On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: > > > I've got three tables like this (simplified): > > > > USERS > > user_id > > user_name > > > > DOCS > > doc_id > > doc_name > > > > USER_DOCS > > signoff_id > > user_id > > doc_id > > > > I need all user names and doc names where the combination doesn't exist > > in the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc > > 9 and user 6 hasn't signed off on doc 44, I need to know that. > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300844 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 help
Question: Do you also want a result in the case where there is no USER_DOCS record for a given USER and DOC record (FULL OUTER JOIN)? On Sat, Mar 8, 2008 at 12:12 AM, Josh McKinley <[EMAIL PROTECTED]> wrote: > I've got three tables like this (simplified): > > USERS > user_id > user_name > > DOCS > doc_id > doc_name > > USER_DOCS > signoff_id > user_id > doc_id > > I need all user names and doc names where the combination doesn't exist in > the signoff table USER_DOCS. For example, user 12 hasn't signed of on doc 9 > and user 6 hasn't signed off on doc 44, I need to know that. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300843 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 help
Josh, The SQL I posted worked for me in my tests. If you deviated from that, can you post the SQL that you used? Assuming the join table doesn't have any duplicate rows (and no rows exist in the join table without a match in both of the other tables) then the resultset of the query should have a number of rows equal to the following equation: (number of row in USERS * number of rows in DOCS) - number of rows in USERS_DOCS > OK, so let's just say that the "where not exists" option is comparable > in efficiency. I ran the query and was greeted by every row in the > tables. 70K results instead of the 60 or so I should be getting. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300801 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 help
OK, so let's just say that the "where not exists" option is comparable in efficiency. I ran the query and was greeted by every row in the tables. 70K results instead of the 60 or so I should be getting. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300798 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 help
Josh, I use WHERE EXISTS and WHERE NOT EXISTS with some frequency and I have never run into any performance problems with it. In SQL Server, each join statement needs an "on" clause, even if it is 1 = 1. I ran some quick tests with about 50 rows in one table, 200 in another, and about a dozen in the join table. Despite returning over 10,000 results, neither table took even a second to execute in SQL Server (and that without any indexes). They did, however, return a different number of results. Hope that helps, Steve The syntax I used should work in any database. Your syntax >I'm really looking for a solution with only one query using joins. It should >be possible to do this with a couple of joins. > >This query, suggested by Steve, would work well if the tables remained >small: > >SELECT user_id,user_name,doc_id,doc_name FROM USERS,DOCS WHERE NOT EXISTS >( SELECT signoff_id FROM USER_DOCS WHERE user_id = >USERS.user_id AND doc_id = DOCS.doc_id) > > >In large tables, though, I don't think it's going to be a very good >solution. > >It seems like something along the lines of this should work: > >select u.user_name, d.doc_name >from docs d left join( > users u left join user_docs ud on u.user_id = ud.user_id >) >where ud.user_id is null > >Actually, I just tried that and it does work on my test db, which is mysql. >Strangely enough, I tried it on the production db which is SQL Server >(tables are different, but relationships are the same) and it doesn't work. >Anybody know why this doesn't work in SQL Server 2K??? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300797 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 help
> > In large tables, though, I don't think it's going to be a very good > solution. > Why not? It should perform fine if there are indexes on the tables. Make a couple of test tables and populate them with a whole load of numbers and test it before rejecting it ;) > Anybody know why this doesn't work in SQL Server 2K??? It doesn't look syntactically correct (I have only ever used SQL Server); the left join (...) would be causing the problem I think - should be a valid SQL statement selecting a recordset between those parenthesis for the query to work. Another approach would be to change the design of the db slightly. Every user that needs to sign-off a given document would have a user_docs entry and the user_entry table would have a bit flag to say whether the doc has been signed off or not. Then the query to select user docs not signed off is very straightforward: SELECT u.*, d.* FROM user_docs ud INNER JOIN users u ON u.user_id = ud.user_id INNER JOIN docs d ON d.doc_id = ud.doc_id WHERE ud.signed_off = 0 HTH Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300796 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 help
I'm really looking for a solution with only one query using joins. It should be possible to do this with a couple of joins. This query, suggested by Steve, would work well if the tables remained small: SELECT user_id,user_name,doc_id,doc_name FROM USERS,DOCS WHERE NOT EXISTS ( SELECT signoff_id FROM USER_DOCS WHERE user_id = USERS.user_id AND doc_id = DOCS.doc_id) In large tables, though, I don't think it's going to be a very good solution. It seems like something along the lines of this should work: select u.user_name, d.doc_name from docs d left join( users u left join user_docs ud on u.user_id = ud.user_id ) where ud.user_id is null Actually, I just tried that and it does work on my test db, which is mysql. Strangely enough, I tried it on the production db which is SQL Server (tables are different, but relationships are the same) and it doesn't work. Anybody know why this doesn't work in SQL Server 2K??? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300794 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 help
> > As a side note, I generally recommend that you have a compound primary key > in a join table. So, USERS_DOCS would have a compound primary key of user_id > and doc_id and wouldn't need another column. This helps to enforce > referential integrity. Or create a unique index / constraint of the two columns and have a surrogate primary key should you need to join a single row to another table. A primary key should not be what makes a row unique, simply a reference to the row for relationship definitions - every table should have at least one unique constraint / index other than the primary key constraint. A simplified example where you might want a surrogate key: Actors (ActorId, Name) Films (FilmId, Name) FilmActor(FilmActorId, FilmId, ActorId) <--- surrogate key (FilmActorId) instead of compound pk, unique index on FilmId & ActorId FilmActorReview(ReviewId, FilmActorId, ReviewText) My tuppence, Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300793 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 help
The nice thing about SQL is you can often write the query almost how you word the problem: SELECT user_id,user_name,doc_id,doc_name FROMUSERS,DOCS WHERE NOT EXISTS ( SELECT signoff_id FROMUSER_DOCS WHERE user_id = USERS.user_id AND doc_id = DOCS.doc_id ) Basically, this gets all users and all docs (returning a row for every combination thereof) and then filters out any with a match in USERS_DOCS. As a side note, I generally recommend that you have a compound primary key in a join table. So, USERS_DOCS would have a compound primary key of user_id and doc_id and wouldn't need another column. This helps to enforce referential integrity. Steve > I need all user names and doc names where the combination doesn't > exist in the signoff table USER_DOCS. For example, user 12 hasn't > signed of on doc 9 and user 6 hasn't signed off on doc 44, I need to > know that. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300791 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 help
I've never worked in MySQL, but take a look at the below. It should get you in the right direction. BTW, it's a 2 step process. 1st, your combining the User and Docs tables to get a list. Then you take that list and bounce it agaist the USER_DOCS table and show anything where SIGNOFF is NULL. SELECT a.USER_ID, a.USER_NAME, b.DOC_ID, b.DOC_NAME INTO #TEMPTBL FROM USERS a, DOCS b SELECT a.USER_ID, a.USER_NAME, a.DOC_ID, a.DOC_NAME, b.SIGNOFF_ID FROM #TEMPTBL a LEFT JOIN USER_DOCS b ON (a.USER_ID = b.USER_ID) AND ( a.DOC_ID = b.DOC_ID) WHERE b.SIGNOFF_ID is null Good Luck!! - Dae ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300790 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 help
By the way, this is a MySQL DB. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300789 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 Help - find unique rows in two similar tables
It was pretty close. Running it in "one direction", knowing that table B has extra records, works fine. - Original Message - From: "Dawson, Michael" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, February 11, 2008 7:59 PM Subject: RE: Query Help - find unique rows in two similar tables > You know... This probably won't work. I don't have access to a > database server right now to test this. I'm trying to picture the > results, but it's late and my brain is foggy. > > M!ke > > -Original Message- > From: Dawson, Michael [mailto:[EMAIL PROTECTED] > Sent: Monday, February 11, 2008 8:45 PM > To: CF-Talk > Subject: RE: Query Help - find unique rows in two similar tables > > SELECT > a.* > ,b.* > FROM > TableA a > FULL OUTER JOIN TableB b > ON a.id = b.id > WHERE > a.id IS NULL > OR > b.id IS NULL > > (Untested) > > M!ke ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298771 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 Help - find unique rows in two similar tables
You know... This probably won't work. I don't have access to a database server right now to test this. I'm trying to picture the results, but it's late and my brain is foggy. M!ke -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, February 11, 2008 8:45 PM To: CF-Talk Subject: RE: Query Help - find unique rows in two similar tables SELECT a.* ,b.* FROM TableA a FULL OUTER JOIN TableB b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL (Untested) M!ke ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298768 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 Help - find unique rows in two similar tables
SELECT a.* ,b.* FROM TableA a FULL OUTER JOIN TableB b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL (Untested) M!ke -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Monday, February 11, 2008 8:38 PM To: CF-Talk Subject: Query Help - find unique rows in two similar tables I have two tables with identical structures. The data in the two tables is _nearly_ identical, with one table having a few additional rows. I need to find those additional rows. I want to query the two tables and find the rows in one or the other that are not present in both. It would be OK to run two queries - show the rows in table A that aren't present in table B, then a second query to do the opposite. The primary keys in the two tables are _not_ the same, so rows must be deamed the same by examing a combination of three other columns. I've found some examples of queries to finding duplicate rows in two similar tables, but not the inverse. And most of those examples use the primary key. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298766 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 help
change one thing it errors on another lol now it errors on the "," >I'll take a look at those > >thanks guys ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294437 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 help
I'll take a look at those thanks guys > Looks like CEIL() or CEILING() is the function you're looking for: > http://db.apache.org/derby/docs/10.2/ref/refderby.pdf > > HTH, > Jon > On Dec 8, 2007, at 11:18 PM, Dave l wrote: > > > if i change the select statement to: > > SELECT center_id, address1, city, state, postalcode, state, > > ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + > > > > it changes the error to: > > 'ROUND' is not recognized as a function or procedure. > > > > > > so i might be sol on this tut > > > > > > ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294431 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 help
Looks like CEIL() or CEILING() is the function you're looking for: http://db.apache.org/derby/docs/10.2/ref/refderby.pdf HTH, Jon On Dec 8, 2007, at 11:18 PM, Dave l wrote: > if i change the select statement to: > SELECTcenter_id, address1, city, state, postalcode, state, > ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + > > it changes the error to: > 'ROUND' is not recognized as a function or procedure. > > > so i might be sol on this tut > > > ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294414 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 help
http://www.ibm.com/developerworks/opensource/library/os-ad-trifecta6/ looks like you can use cast to round values On Dec 9, 2007 8:25 AM, Adrian Lynch <[EMAIL PROTECTED]> wrote: > If ROUND isn't there, look for another rounding function like CEILING, FLOOR > etc. > > Adrian > > -Original Message- > From: Dave l > Sent: 09 December 2007 04:18 > To: CF-Talk > Subject: Re: query help > > > if i change the select statement to: > SELECT center_id, address1, city, state, postalcode, state, >ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + > > it changes the error to: > 'ROUND' is not recognized as a function or procedure. > > > so i might be sol on this tut > > > ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294413 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 help
If ROUND isn't there, look for another rounding function like CEILING, FLOOR etc. Adrian -Original Message- From: Dave l Sent: 09 December 2007 04:18 To: CF-Talk Subject: Re: query help if i change the select statement to: SELECT center_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294412 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 help
if i change the select statement to: SELECT center_id, address1, city, state, postalcode, state, ROUND((ACOS((SIN(#lat_A#/57.2958) * SIN(#lat_B#/57.2958)) + it changes the error to: 'ROUND' is not recognized as a function or procedure. so i might be sol on this tut ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294410 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 Help
What's the difference between QUERY_RESULTS and GIFTREPORTS? Do things change if you select over query_results and join giftab1? -- Andrew -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 02, 2007 3:32 PM To: CF-Talk Subject: OT: Query Help Man, I'm having all kinds of brain farts today. I'm trying to optimize the following query: SELECT DISTINCT (fieldlist) FROM GIFTAB1 B INNER JOIN QUERY_RESULTS C ON B.ROW_ID = C.ROWID WHERE C.QUERYID = #param.QUERYID# AND C.ENTITYID = '#param.ENTITY_ID#' ORDER BY B.ENTITY_ID, B.CREDTDAT DESC GIFTAB1 contains almost million rows of data, and query_results contains a varying amount of rows, depending on activity, time of day, etc.. specifically in this case, QUERY_RESULTS contains 341 rows that match the aboe criteria, and currently contains about 8000 rows total. This query takes 7 seconds to return 7 rows of data given a specific queryid and entity id. I have indexes on all relevant columns, and row_id on giftab1 is actually a unique clustered index. I show the query execution plan, and my biggest hit (92%) is a "Clustered Index Scan" on giftab1 ... the row id join. A similar query in our legacy application performs MUCH better: SELECT DISTINCT (fieldlist) FROM GIFTAB1 B INNER JOIN GIFTREPORTS C ON B.ROW_ID = C.ROW_ID WHERE C.USERNAME='GROLWR' AND C.ENTITY_ID = '88' ORDER BY B.ENTITY_ID, B.CREDTDAT DESC The problem appears to be in my first query, that the clustered indes scan lists "actual number of rows" as 3.9 million - the FASTER query doesn't do this... the faster query uses an index scan on giftreports and an index seek on giftab1. Both contain 7 rows. -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289992 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 help
Martin > Thanks for the reply. Yeah I just came up with the same myself as a Q of Qs. I tried it in the original union query, which would be the ideal solution for me having it all in the same query, but Oracle throws me an inconsistent data types error?? Can SUM be used in a union on oracle? I don't see why there should be a problem with the SUM. Maybe the data type inconsistency is between "cl.COU_LIB" and "newgide.country_iso.cis_lib"? Nick ~| 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/cf_lists/message.cfm/forumid:4/messageid:246508 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query help
Hello Nick. Thanks for the reply. Yeah I just came up with the same myself as a Q of Qs. I tried it in the original union query, which would be the ideal solution for me having it all in the same query, but Oracle throws me an inconsistent data types error?? Can SUM be used in a union on oracle? Anyhoo it works with the Q of Q and no real difference in the performance. Again thanks for your reply. cheers Martin >> The results are great but in some instances a country will appear twice, >> with two different count values, I want the country to appear once with >> the total count of both results. > >select COUNTRY, sum(NB_PROFILES) from >(your entire select statement here) >group by COUNTRY > >should do it? > >Nick ~| 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/cf_lists/message.cfm/forumid:4/messageid:246504 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query help
> The results are great but in some instances a country will appear twice, > with two different count values, I want the country to appear once with > the total count of both results. select COUNTRY, sum(NB_PROFILES) from (your entire select statement here) group by COUNTRY should do it? Nick ~| 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/cf_lists/message.cfm/forumid:4/messageid:246503 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Help
Perhaps more elegant SELECT p.adtype, p.name, p.longitude, p.latitude, p.address1, p.address2, p.city, p.state, p.zip, ( SELECT TOP 1 Areacode FROMpropertyareas a WHERE a.propertyid = p.propertyid ) AS areacode FROMproperties ORDER BYp.name -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Tuesday, 6 September 2005 11:46 a.m. To: CF-Talk Subject: Re: Query Help Thanks, Mathew. That works. It looks familiar... I remember doing this before, where it required the use of 'fake' aggregate functions on all other columns I needed returned by the query, as well as in the ORDER BY clause. The real query is a little more complicated. SELECT p.propertyid, MIN(p.adtype) AS adtype, MIN(p.name) AS name, MIN(p.longitude) AS longitude, MIN(p.latitude) AS latitude, MIN(p.address1) AS address1, MIN(p.address2) AS address2, MIN(p.city) AS city, MIN(p.state) AS state, MIN(p.zip) AS zip, MIN(a.areacode) AS areacode FROM properties p INNER JOIN propertyareas a ON a.propertyid = p.propertyid WHERE a.areacode = '#form.areacode#' GROUP BY p.propertyid ORDER BY MIN(a.areacode), MIN(p.name) Seems to me that there's enough 'kludge factor' in this query that I can't help thinking either it should be designed differently or that there's something I'm missing in the design of the database that would allow me to do this more easily. - Original Message - From: "Matthew Walker" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, September 05, 2005 4:50 PM Subject: RE: Query Help > SELECT p.propertyid, p.name, min(c.name) AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > GROUP BY p.propertyid, p.name > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 6 September 2005 10:31 a.m. > To: CF-Talk > Subject: Query Help > > I have a number of real estate properties, each associated with one or > more city. If I select by specifying a city then I get a list of unique > > properties. But if I do a broader select, then I get duplicates of the > property record when a record is assocuated with two or more cities. > How > do I limit the select results returned so that I get no more than one of > > each property record? > > SELECT p.propertyid, p.name, c.name AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > > > property > -- > propertyid > name > > city > -- > cityid > name > > property_city > -- > propertyid > cityid > > > > > ~| 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:217401 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 Help
Thanks, Mathew. That works. It looks familiar... I remember doing this before, where it required the use of 'fake' aggregate functions on all other columns I needed returned by the query, as well as in the ORDER BY clause. The real query is a little more complicated. SELECT p.propertyid, MIN(p.adtype) AS adtype, MIN(p.name) AS name, MIN(p.longitude) AS longitude, MIN(p.latitude) AS latitude, MIN(p.address1) AS address1, MIN(p.address2) AS address2, MIN(p.city) AS city, MIN(p.state) AS state, MIN(p.zip) AS zip, MIN(a.areacode) AS areacode FROM properties p INNER JOIN propertyareas a ON a.propertyid = p.propertyid WHERE a.areacode = '#form.areacode#' GROUP BY p.propertyid ORDER BY MIN(a.areacode), MIN(p.name) Seems to me that there's enough 'kludge factor' in this query that I can't help thinking either it should be designed differently or that there's something I'm missing in the design of the database that would allow me to do this more easily. - Original Message - From: "Matthew Walker" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, September 05, 2005 4:50 PM Subject: RE: Query Help > SELECT p.propertyid, p.name, min(c.name) AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > GROUP BY p.propertyid, p.name > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 6 September 2005 10:31 a.m. > To: CF-Talk > Subject: Query Help > > I have a number of real estate properties, each associated with one or > more city. If I select by specifying a city then I get a list of unique > > properties. But if I do a broader select, then I get duplicates of the > property record when a record is assocuated with two or more cities. > How > do I limit the select results returned so that I get no more than one of > > each property record? > > SELECT p.propertyid, p.name, c.name AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > > > property > -- > propertyid > name > > city > -- > cityid > name > > property_city > -- > propertyid > cityid > > > > > ~| 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:217396 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 Help
oh, I read this: >How >do I limit the select results returned so that I get no more than one of >each property record? as that he wanted one of each property record. Jim, what is it exactly that you are after? On 9/5/05, Matthew Walker <[EMAIL PROTECTED]> wrote: > I think Jim wants to output just one row even if there is more than one > city. So the city that is output is an arbitrary selection from the > associated cities. Your solution still outputs one row for each city. > > -Original Message- > From: Ryan Guill [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 6 September 2005 10:45 a.m. > To: CF-Talk > Subject: Re: Query Help > > SELECT DISTINCT p.propertyid, p.name, c.name AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > > DISTINCT is all you need. > > On 9/5/05, Jim McAtee <[EMAIL PROTECTED]> wrote: > > I have a number of real estate properties, each associated with one or > > more city. If I select by specifying a city then I get a list of > unique > > properties. But if I do a broader select, then I get duplicates of > the > > property record when a record is assocuated with two or more cities. > How > > do I limit the select results returned so that I get no more than one > of > > each property record? > > > > SELECT p.propertyid, p.name, c.name AS city > > FROM property p > > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > > > > > > property > > -- > > propertyid > > name > > > > city > > -- > > cityid > > name > > > > property_city > > -- > > propertyid > > cityid > > > > > > > > > > ~| 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:217394 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 Help
I think Jim wants to output just one row even if there is more than one city. So the city that is output is an arbitrary selection from the associated cities. Your solution still outputs one row for each city. -Original Message- From: Ryan Guill [mailto:[EMAIL PROTECTED] Sent: Tuesday, 6 September 2005 10:45 a.m. To: CF-Talk Subject: Re: Query Help SELECT DISTINCT p.propertyid, p.name, c.name AS city FROM property p INNER JOIN property_city pc ON pc.propertyid = p.propertyid DISTINCT is all you need. On 9/5/05, Jim McAtee <[EMAIL PROTECTED]> wrote: > I have a number of real estate properties, each associated with one or > more city. If I select by specifying a city then I get a list of unique > properties. But if I do a broader select, then I get duplicates of the > property record when a record is assocuated with two or more cities. How > do I limit the select results returned so that I get no more than one of > each property record? > > SELECT p.propertyid, p.name, c.name AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > > > property > -- > propertyid > name > > city > -- > cityid > name > > property_city > -- > propertyid > cityid > > > ~| 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:217393 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 Help
SELECT p.propertyid, p.name, min(c.name) AS city FROM property p INNER JOIN property_city pc ON pc.propertyid = p.propertyid GROUP BY p.propertyid, p.name -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Tuesday, 6 September 2005 10:31 a.m. To: CF-Talk Subject: Query Help I have a number of real estate properties, each associated with one or more city. If I select by specifying a city then I get a list of unique properties. But if I do a broader select, then I get duplicates of the property record when a record is assocuated with two or more cities. How do I limit the select results returned so that I get no more than one of each property record? SELECT p.propertyid, p.name, c.name AS city FROM property p INNER JOIN property_city pc ON pc.propertyid = p.propertyid property -- propertyid name city -- cityid name property_city -- propertyid cityid ~| 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:217392 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 Help
SELECT DISTINCT p.propertyid, p.name, c.name AS city FROM property p INNER JOIN property_city pc ON pc.propertyid = p.propertyid DISTINCT is all you need. On 9/5/05, Jim McAtee <[EMAIL PROTECTED]> wrote: > I have a number of real estate properties, each associated with one or > more city. If I select by specifying a city then I get a list of unique > properties. But if I do a broader select, then I get duplicates of the > property record when a record is assocuated with two or more cities. How > do I limit the select results returned so that I get no more than one of > each property record? > > SELECT p.propertyid, p.name, c.name AS city > FROM property p > INNER JOIN property_city pc ON pc.propertyid = p.propertyid > > > property > -- > propertyid > name > > city > -- > cityid > name > > property_city > -- > propertyid > cityid > > > ~| 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:217390 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 help
If you don't want to do cfqueryparam, use the SQL version. The SQL version works well if you want to put the query in a folded string that can be passed to a function that does the cfquery. Data binding is done either way. In all cases, I think doing the data binding declaration is a best practice technique. Data binding without cfqueryparam: DECLARE @ID int SET @ID = 324 -- or use CF variable to as SET @ID = #Variables.ID# select emp.name, emp.salary from emp where emp.id = @ID or folded string technique: #PreserveSingleQuotes(Arguments[1])# DECLARE @ID int SET @ID = #Variables.ID# select emp.name, emp.salary from emp where emp.id = @ID In these approaches, the data binding occurs when the query is submitted. ( In the testing I did to determine the best approach for the code that CFSQLTool generates, I found that using provides better debug and support options rather than doing the folded string. ) Joseph At 05:33 PM 7/5/2005, you wrote: >When I posed this question to the group some time ago, the consensus was >basically your point, but the one counter point the struck me was, "well >it's not a variable now, but might it become one in the future?" So, >depends on how malleable you feel the code may be over its lifetime. > http://www.switch-box.org/CFSQLTool/Download/ Switch_box MediaFirm, Inc. www.Switch-box.org Loveland, CO USA ~| 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:211305 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 help
daniel kessler wrote: >> On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: >>> >>>AND cs_price <> >> value=""> >> >> AND cs_price IS NOT NULL > > this worked great! thanks! > > but why? IOW, why does this work, but these do not: > AND cs_price <> NULL > AND cs_price != NULL Because NULL is special. NULL means unknown. The outcome of every operation on unknown is unknown. The only exception is the special 'IS (NOT) NULL' case which will return TRUE or FALSE. A predicate like 'cs_price <> 3' will filter the returned data and will only pass rows where the expression returns TRUE. Since 'cs_price <> 3' will always return NULL when cs_price is NULL, records with a NULL are excluded from the result. 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:211245 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 help
>Cause NULL is not a value per say it would work if it were <> 'NULL' >assuming NULL is in the column. well THAT'S gonna be hard to remember. Well maybe this was painful enough to remember it next time. thanks for the explanation. ~| 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:211243 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 help
Cause NULL is not a value per say it would work if it were <> 'NULL' assuming NULL is in the column. -Original Message- From: daniel kessler [mailto:[EMAIL PROTECTED] Sent: 06 July 2005 12:30 To: CF-Talk Subject: Re: Query help >On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: >> an empty string. So I've adjusted the query so that if the store = >> 'convenience' then the price must not equal empt string. >> >> AND cs_price <> > >AND cs_price IS NOT NULL this worked great! thanks! but why? IOW, why does this work, but these do not: AND cs_price <> NULL AND cs_price != NULL And thanks for the discussion about when to use cfqueryparam. I suppose I knew that at some point but in the haste of implementation, I threw it in. Good to remember the proper time to use something. ~| 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:211242 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 help
>On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: >> an empty string. So I've adjusted the query so that if the store = >> 'convenience' then the price must not equal empt string. >> >> AND cs_price <> > value=""> > >AND cs_price IS NOT NULL this worked great! thanks! but why? IOW, why does this work, but these do not: AND cs_price <> NULL AND cs_price != NULL And thanks for the discussion about when to use cfqueryparam. I suppose I knew that at some point but in the haste of implementation, I threw it in. Good to remember the proper time to use something. ~| 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:211240 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 help
> Personally, I'm not very versed in execution plans, but how > would the query shown result in 2 execution plans? There were two queries in my example. Both were identical save for the literal value of emp.id. I suspect that the methods used by various databases to determine whether execution plans can be reused vary somewhat, as well. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| 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:211226 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 help
Personally, I'm not very versed in execution plans, but how would the query shown result in 2 execution plans? Where can I read up more about this? Russ -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 05, 2005 7:53 PM To: CF-Talk Subject: RE: Query help > Right, but my point was that if you did not pass variables into your > cfquery, why would you need a cfqueryparam? for example, consider this > select statement: > > select emp.name, emp.salary > from emp > where emp.id = 324 > > would you use a cfqueryparam in the above query? like this: > > select emp.name, emp.salary > from emp > where emp.id = > > I would have to say you could, but it is not necessary. Using it won't > give you any advantage since cfqueryparam is used for variable > binding, and since there is no variable to bind, why use it? In this particular case, viewed alone, there's no reason to do this. However, you might have another query somewhere else like this: select emp.name, emp.salary from emp where emp.id = 325 I think this would generally result in two execution plans, which would essentially be the same. In that case, why have the database do the work of recalculating the second execution plan? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| 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:211223 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 help
> You're right. Maybe the example SQL I gave was not a good one. The > bottom line is, whenever you have a "static SQL" that does not change > across your application, using bind variables in that SQL (through > cfqueryparam) is not necessary. That's my understanding. I think your understanding is correct; I was just providing an example where consistent use of bind parameters may still be useful even with static values. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| 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:211218 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 help
On 7/5/05, Dave Watts <[EMAIL PROTECTED]> wrote: > In this particular case, viewed alone, there's no reason to do this. > However, you might have another query somewhere else like this: > > select emp.name, emp.salary > from emp > where emp.id = 325 > > I think this would generally result in two execution plans, which would > essentially be the same. In that case, why have the database do the work of > recalculating the second execution plan? You're right. Maybe the example SQL I gave was not a good one. The bottom line is, whenever you have a "static SQL" that does not change across your application, using bind variables in that SQL (through cfqueryparam) is not necessary. That's my understanding. -- Eddie. http://awads.net/ ~| 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:211216 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 help
> Right, but my point was that if you did not pass variables into your > cfquery, why would you need a cfqueryparam? for example, consider this > select statement: > > select emp.name, emp.salary > from emp > where emp.id = 324 > > would you use a cfqueryparam in the above query? like this: > > select emp.name, emp.salary > from emp > where emp.id = > > I would have to say you could, but it is not necessary. Using it won't > give you any advantage since cfqueryparam is used for variable > binding, and since there is no variable to bind, why use it? In this particular case, viewed alone, there's no reason to do this. However, you might have another query somewhere else like this: select emp.name, emp.salary from emp where emp.id = 325 I think this would generally result in two execution plans, which would essentially be the same. In that case, why have the database do the work of recalculating the second execution plan? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| 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:211213 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 help
When I posed this question to the group some time ago, the consensus was basically your point, but the one counter point the struck me was, "well it's not a variable now, but might it become one in the future?" So, depends on how malleable you feel the code may be over its lifetime. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning -Original Message- From: Eddie [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 05, 2005 4:29 PM To: CF-Talk ....Subject: Re: Query help On 7/5/05, Russ <[EMAIL PROTECTED]> wrote: > I believe SQL (and probably oracle) will cache the execution path if you > use a cfqueryparam.. Right, but my point was that if you did not pass variables into your cfquery, why would you need a cfqueryparam? for example, consider this select statement: select emp.name, emp.salary from emp where emp.id = 324 would you use a cfqueryparam in the above query? like this: select emp.name, emp.salary from emp where emp.id = I would have to say you could, but it is not necessary. Using it won't give you any advantage since cfqueryparam is used for variable binding, and since there is no variable to bind, why use it? -- Eddie. http://awads.net/ ~| 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:211211 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 help
On 7/5/05, Russ <[EMAIL PROTECTED]> wrote: > I believe SQL (and probably oracle) will cache the execution path if you > use a cfqueryparam.. Right, but my point was that if you did not pass variables into your cfquery, why would you need a cfqueryparam? for example, consider this select statement: select emp.name, emp.salary from emp where emp.id = 324 would you use a cfqueryparam in the above query? like this: select emp.name, emp.salary from emp where emp.id = I would have to say you could, but it is not necessary. Using it won't give you any advantage since cfqueryparam is used for variable binding, and since there is no variable to bind, why use it? -- Eddie. http://awads.net/ ~| 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:211210 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 help
I believe SQL (and probably oracle) will cache the execution path if you
use a cfqueryparam..
-Original Message-
From: Eddie [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 05, 2005 5:51 PM
To: CF-Talk
Subject: Re: Query help
On 7/5/05, Jann E. VanOver <[EMAIL PROTECTED]> wrote:
> AND isNull(cs_price,'') <> value="">
Why would you want to use in this case? since there is
no "query parameter" here, just a literal value ("" or NULL).
--
Eddie.
http://awads.net/
~|
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:211204
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 help
On 7/5/05, Jann E. VanOver <[EMAIL PROTECTED]> wrote:
> AND isNull(cs_price,'') <> value="">
Why would you want to use in this case? since there is
no "query parameter" here, just a literal value ("" or NULL).
--
Eddie.
http://awads.net/
~|
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:211198
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 help
Eddie wrote: >On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: > > >>an empty string. So I've adjusted the query so that if the store = >>'convenience' then the price must not equal empt string. >> >>AND cs_price <> >> >> > >AND cs_price IS NOT NULL > > > or as I often code it for SQL Server (don't know if it works the same in Oracle) AND isNull(cs_price,'') <> This says, if "cs_price" is null, use an empty string in its place. ~| 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:211197 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 help
On 7/5/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: > an empty string. So I've adjusted the query so that if the store = > 'convenience' then the price must not equal empt string. > > AND cs_price <> AND cs_price IS NOT NULL -- Eddie. http://awads.net/ ~| 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:211194 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 help
>Have you tried > >AND cs_price != '' I did and then went to be sure it was ok. I found a note saying that it was ok in some implementations of sql and they recommended the < > so I adjusted to that with no change. I just tried it again with no change, dangit. ~| 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:211192 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 help
Have you tried AND cs_price != '' On 7/5/05 12:57 PM, "Daniel Kessler" <[EMAIL PROTECTED]> wrote: > I am on Oracle and I'm trying to do a fairly simple query and I just > don't see the problem with it. I've stared now for way to long. > > I am trying to differentiate between grocery store items and > convenience store items and this is done by the cs_price column being > an empty string. So I've adjusted the query so that if the store = > 'convenience' then the price must not equal empt string. > Unfortunately it always comes up with 0 records. If I remove the > added WHERE then it returns records, just more than it should. > > >SELECT detail,food_store_id,cs_price >FROM fsnep_food_store >WHERE department = value="#url.getFoodItems#"> > AND food_item = value="#url.getFoodDetail#"> > > AND cs_price <> value=""> > >ORDER BY detail asc > > > > Here's a dump of the database: > http://eatsmart.umd.edu/dbf/food_items/db_display.cfm > > thanks! ~| 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:211189 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 help
> It's the first time in some years though that I'm not > the one responsible for making the guidelines and I'm > finding it's a welcome relief. I'm sure It'll get old > at some point, but for now it's much less stressful > to be the worker bee instead of the manager and main > dev lead. I'm trying to move the other direction. :) Are you still managing Fitz and Floyd? > I was going to send you this reply off-list, but I > realized that this is a good illustration of the > reason we need to be flexible with our ability to > switch between different methodologies or coding styles. > It's an argument, or at least a topic, that comes up > pretty often on the list about the "best way" to do > things. Maybe sometimes the more important thing is > to be able to adapt to any given set of guidelines or > practices and use them efficiently. I'd be in a bad > way right now if I didn't have the ability to set > aside some of my preconceived notions and adhere to > the standards in place. Yeah, the idea that a bad standard is better than no standard... Sometimes it's difficult to find a good balance, though in general I think the reasoning is sound. Html/CSS box models for instance - MS interpretation was better, but it's still better to have one box model to deal with even if that box model isn't as good. And since nobody's advocating a doctype that will force other browsers to use the IE box model I use the doctype that forces IE to use the W3C model. And I still find the variations quite frustrating ... The biggest one for me is centering -- I can't seem for the life of me to get things to center properly in any of hte Mozilla browsers... IE's a dream for this, but I really wish they both behaved the same way, even if it made it more difficult to manage centering, the ability to identify a singular technique that works, even if difficult, would be fantastic... But then adaptation is often at the heart of my work, like in writing the sql abstraction layer for my framework, so that I can have a singular standard for database access. Which ties back into the comment about being able to adapt to a different environment and work with it efficiently, because without that ability to adapt you can't build those sorts of abstractions either. Though I don't get to use my own programming methods in my day job either -- we use other techniques that aren't as flexible but that are getting the job done, so I don't complain (even though could get a lot of this work done faster with a sql abstraction layer for instance) unless I'm being asked for advice on a new technique. :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| 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:210242 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 help
That is an interesting requirement although when I look back I think almost every system I ever worked with had a column named ID in each table. Here at work the inhouse framework they make use us, pretty much requires the PK of a table be just ID and numeric. There are ways around it but they are never used and probably not even known by most. On 6/22/05, Ken Ferguson <[EMAIL PROTECTED]> wrote: > > Yeah, I'm working with a guy who has, as part of his coding guidelines, > the requirement that every table in the database have a column named ID > that is set as an auto-increment. That's only one of the many fun little > guidelines to which we're strictly adhering. ~| 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:210238 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 help
Yeah, I'm working with a guy who has, as part of his coding guidelines, the requirement that every table in the database have a column named ID that is set as an auto-increment. That's only one of the many fun little guidelines to which we're strictly adhering. As a developer used to working alone or in very small groups most of the time, I'm finding it interesting to work with someone who's formulated some of his own guidelines like this. It's the first time in some years though that I'm not the one responsible for making the guidelines and I'm finding it's a welcome relief. I'm sure It'll get old at some point, but for now it's much less stressful to be the worker bee instead of the manager and main dev lead. I was going to send you this reply off-list, but I realized that this is a good illustration of the reason we need to be flexible with our ability to switch between different methodologies or coding styles. It's an argument, or at least a topic, that comes up pretty often on the list about the "best way" to do things. Maybe sometimes the more important thing is to be able to adapt to any given set of guidelines or practices and use them efficiently. I'd be in a bad way right now if I didn't have the ability to set aside some of my preconceived notions and adhere to the standards in place. --Ferg S. Isaac Dealey wrote: >>Isaac, thanks man, that works too and looks better than my >>case statements. Shame on you for thinking I'd even have >>Access installed on my machine though! It's MySQL. >> >> > >lol... Well it was a random guess based on the use of now() and "ID" >as a column name because that's real common with Access due to it >prepopulating the column name with "ID" if you get the "would you like >to create a primary key?" alert. I was guessing it was a client DB you >inherited, since I didn't think you would be creating "ID" columns. > >Glad to help. :) > > >s. isaac dealey 954.522.6080 >new epoch : isn't it time for a change? > >add features without fixtures with >the onTap open source framework > >http://www.fusiontap.com >http://coldfusion.sys-con.com/author/4806Dealey.htm > > > > > ~| 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:210235 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 help
> Isaac, thanks man, that works too and looks better than my > case statements. Shame on you for thinking I'd even have > Access installed on my machine though! It's MySQL. lol... Well it was a random guess based on the use of now() and "ID" as a column name because that's real common with Access due to it prepopulating the column name with "ID" if you get the "would you like to create a primary key?" alert. I was guessing it was a client DB you inherited, since I didn't think you would be creating "ID" columns. Glad to help. :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| 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:210229 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 help
Isaac, thanks man, that works too and looks better than my case statements. Shame on you for thinking I'd even have Access installed on my machine though! It's MySQL. --Ferg S. Isaac Dealey wrote: >Hey Ken, > >I've had those days... try this: > >SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS PARENT_NAME >FROM ENTITY A >INNER JOIN ENTITY_NAME B ON > (b.entity_id = a.id and now() > b.start_date and >(b.end_date is null or b.end_date > now())) >LEFT JOIN ENTITY_NAME C ON > (c.entity_id = a.parent_id and now() > c.start_date and >(c.end_date is null or c.end_date > now())) > >Though... this is Access isn't it? ... you may have some difficulty >getting access to accept the parenthesis in that order, since I think >the inner and left join statements need to be nested... With this one >I might start with the visual query builder in Access first to get the >left join syntax and then add the date filters. > > > >s. isaac dealey 954.522.6080 >new epoch : isn't it time for a change? > >add features without fixtures with >the onTap open source framework > >http://www.fusiontap.com >http://coldfusion.sys-con.com/author/4806Dealey.htm > > > > > ~| 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:210221 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 help
Hey Ken, I've had those days... try this: SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS PARENT_NAME FROM ENTITY A INNER JOIN ENTITY_NAME B ON (b.entity_id = a.id and now() > b.start_date and (b.end_date is null or b.end_date > now())) LEFT JOIN ENTITY_NAME C ON (c.entity_id = a.parent_id and now() > c.start_date and (c.end_date is null or c.end_date > now())) Though... this is Access isn't it? ... you may have some difficulty getting access to accept the parenthesis in that order, since I think the inner and left join statements need to be nested... With this one I might start with the visual query builder in Access first to get the left join syntax and then add the date filters. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| 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:210218 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 help
Below is what I came up with. It seems to work pretty well, but I'd still welcome any advice on improvement. --Ferg SELECTdistinct A.ID , B.ENTITY_NAME, case when A.parent_id is null then '' else C.ENTITY_NAME end AS PARENT_NAME FROM ENTITY A, ENTITY_NAME B, (SELECT entity_name, entity_id from entity_name where ((NOW() BETWEEN START_DATE AND END_DATE) OR (now() > START_DATE AND END_DATE is NULL))) as C WHERE A.ID = B.ENTITY_ID AND IF(A.parent_id is not null, A.PARENT_ID = C.ENTITY_ID,1=1) AND ((NOW() BETWEEN B.START_DATE AND B.END_DATE) OR (now() > B.START_DATE AND B.END_DATE is NULL)) Ken Ferguson wrote: >I've got one of those problems where I've looked at a query too long and now I >can't see anything clearly any more. I've put the table layout below of the >columns in question (there are more, but they're not important here). > >ENTITY > >+ID >+LOCATION >+PARENT_ID > >ENTITY_NAME > >+ID >+ENTITY_ID >+ENTITY_NAME >+START_DATE >+END_DATE > >So, an entity can change names over time, but can only ever have one currently >active name determined by the start and end dates. It's very easy to get the >entity's current name. I've no problem with that. The issue I'm having is >trying to get the PARENT ENTITY'S name. It's easy to get the currently active >parent's name as well, but where I'm messing up is that I'm leaving out those >records which DO NOT HAVE a parent entity assigned to them. > >My query: > >SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS PARENT_NAME >FROM ENTITY A, ENTITY_NAME B, > (SELECT entity_name, entity_id from entity_name where ((NOW() BETWEEN > START_DATE AND END_DATE) OR (now() > START_DATE AND END_DATE is NULL))) as C >WHERE A.ID = B.ENTITY_ID AND A.PARENT_ID = C.ENTITY_ID AND > ( > ((NOW() BETWEEN B.START_DATE AND B.END_DATE) OR (now() > > B.START_DATE AND B.END_DATE is NULL)) > ) > >DATA RETURNED >--- >ID ENTITY_NAME PARENT_NAME >--- >5 Shipper B Shipper A-2 >4 Shipper A-2 Shipper B >8 TestVector US Pipeline >--- > >DATA THAT SHOULD BE RETURNED >--- >ID ENTITY_NAME PARENT_NAME >--- >3 Vector US Pipeline >5 Shipper B Shipper A-2 >6 Agent 1 >4 Shipper A-2 Shipper B >8 TestVector US Pipeline >--- > > >I started out using a join, but then switched to the subquery... Like I said, >it shouldn't be all that difficult, but I just keep messing with it and not >getting it right. > >Thanks, >Ferg > > > > ~| 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:210208 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 help (ms sql)
if ( select count(*) from newsletter where template_name = 'Basic') > 0 update newsletter set template = (param 1) where id = there is no value here for ID in the where clause. else insert into newsletter ( template_name, template ) values ( 'Basic', (param 2) ) -Original Message- From: Protoculture [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 16, 2005 6:15 AM To: CF-Talk Subject: query help (ms sql) heres the query. Seems to be chocking on the else statement... I've used the cfqueryparam in the update before and its worked well enough. The only thing I've noticed that looks odd is the cfdebugging output see second section... I am trying to insert a long string of an html template. if ( select count(*) from newsletter where template_name = '#form.template_name#') > 0 update newsletter set template = where id = #form.templateId# else insert into newsletter ( template_name, template ) values ( '#form.template_name#', ) Notice the param 1 and param 2... I'm not sure what this is about. if ( select count(*) from newsletter where template_name = 'Basic') > 0 update newsletter set template = (param 1) where id = else insert into newsletter ( template_name, template ) values ( 'Basic', (param 2) ) ~| 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:198954 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 Help
Eric, You can find total payments made against orders in a specific date range that contain a specific part number by doing something like this: select sum(p.pmt) as order_pmts, i.part_number, o.order_number, o.order_date, o.order_total from orders o inner join order_items i on o.order_id = i.order_id inner join payments on o.order_id = p.order_id where o.order_date between and and i.part_number = group by o.order_date, i.part_number, o.order_total, o.order_number order by o.order_date If you need to find payments for orders with multiple part numbers, you can rewrite the part number filter to: and i.part_number in () This is not tested and is not database-specific since you didn't mention what DBMS you are using, but it should get you started in the right direction. Dina - Original Message - From: "Eric Hoffman" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, January 24, 2005 10:43 AM Subject: RE: Query Help > I know its goofybut lets say the 500 lb gorilla wants to eat...and > he likes crappy reports. > > Is there a logic pattern I should follow...or do I run the query to find > total $ in order items that contain the inventory itemand then check > the payments table for any payments, and then just say, well, if there > was a payment...count it? I would love to do in a single SQL query... > > Know what I mean? > > -Original Message- > From: Dina Hess [mailto:[EMAIL PROTECTED] > Sent: Monday, January 24, 2005 10:12 AM > To: CF-Talk > Subject: Re: Query Help > > Eric, > > I don't think the problem has anything to do with your knowledge of SQL. > It's not typical for a company to allocate order payments to specific > order line items; rather, payments are typically applied to the entire > order. Therefore, how could you possibly determine which line item a > payment should be applied to...unless I'm missing something. > > Dina > > > - Original Message - > From: "Eric Hoffman" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Monday, January 24, 2005 7:27 AM > Subject: Query Help > > > > I have to make a report based upon this scenario...seems simple, yet I > > can't get the numbers rightany pointers? (I have ordered a newer > > Advanced SQL book in the meantime!!) > > > > Need to get orders within date range containing a part number and get > > total dollars received in payments for this. > > > > So I query the orders tablewhich is related to the order_items > > table, where each line item of the order is stored, but does include a > > line_total field which did qty * rate charged. On the other hand, the > > payments table is related to the orders table by order id, and shows > > amount paid...but obviously not breaking out what of the payment > applies > > to what. > > > > So how do I query to get the applicable payment amount correct by that > > item id? Right now my numbers are inflated because it gets the whole > > order payment amount. Recievables higher than sales is great in > > fantasyland. Mucho frustrating no doubt. > > > > Thanks guys. > > > > Eric > > > > > > > > ~| 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:191691 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 Help
I know its goofybut lets say the 500 lb gorilla wants to eat...and he likes crappy reports. Is there a logic pattern I should follow...or do I run the query to find total $ in order items that contain the inventory itemand then check the payments table for any payments, and then just say, well, if there was a payment...count it? I would love to do in a single SQL query... Know what I mean? -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: Monday, January 24, 2005 10:12 AM To: CF-Talk Subject: Re: Query Help Eric, I don't think the problem has anything to do with your knowledge of SQL. It's not typical for a company to allocate order payments to specific order line items; rather, payments are typically applied to the entire order. Therefore, how could you possibly determine which line item a payment should be applied to...unless I'm missing something. Dina - Original Message - From: "Eric Hoffman" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, January 24, 2005 7:27 AM Subject: Query Help > I have to make a report based upon this scenario...seems simple, yet I > can't get the numbers rightany pointers? (I have ordered a newer > Advanced SQL book in the meantime!!) > > Need to get orders within date range containing a part number and get > total dollars received in payments for this. > > So I query the orders tablewhich is related to the order_items > table, where each line item of the order is stored, but does include a > line_total field which did qty * rate charged. On the other hand, the > payments table is related to the orders table by order id, and shows > amount paid...but obviously not breaking out what of the payment applies > to what. > > So how do I query to get the applicable payment amount correct by that > item id? Right now my numbers are inflated because it gets the whole > order payment amount. Recievables higher than sales is great in > fantasyland. Mucho frustrating no doubt. > > Thanks guys. > > Eric > > ~| Logware: 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:191558 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 Help
Eric, I don't think the problem has anything to do with your knowledge of SQL. It's not typical for a company to allocate order payments to specific order line items; rather, payments are typically applied to the entire order. Therefore, how could you possibly determine which line item a payment should be applied to...unless I'm missing something. Dina - Original Message - From: "Eric Hoffman" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, January 24, 2005 7:27 AM Subject: Query Help > I have to make a report based upon this scenario...seems simple, yet I > can't get the numbers rightany pointers? (I have ordered a newer > Advanced SQL book in the meantime!!) > > Need to get orders within date range containing a part number and get > total dollars received in payments for this. > > So I query the orders tablewhich is related to the order_items > table, where each line item of the order is stored, but does include a > line_total field which did qty * rate charged. On the other hand, the > payments table is related to the orders table by order id, and shows > amount paid...but obviously not breaking out what of the payment applies > to what. > > So how do I query to get the applicable payment amount correct by that > item id? Right now my numbers are inflated because it gets the whole > order payment amount. Recievables higher than sales is great in > fantasyland. Mucho frustrating no doubt. > > Thanks guys. > > Eric > > ~| Logware: 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:191537 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 Help Needed (Solved)
Got it working with this code: SELECT COUNT(*) as ClientCount FROM Clients WHERE Client_IntakeDate >= AND Client_IntakeDate <= AND #Trim(Form.Param1Select)# = AND #Trim(Form.Param2Select)# = AND #Trim(Form.Param3Select)# = ; On Wed, 3 Nov 2004 11:25:16 -0600, Donna French <[EMAIL PROTECTED]> wrote: > Okay, I see what you mean but I'm not sure how to define since they > are passed from selects instead of a db field? > > Thank you for your help, > ~ Donna > > > > > On Wed, 03 Nov 2004 01:59:39 +0100, Jochem van Dieten > <[EMAIL PROTECTED]> wrote: > > Donna French wrote: > > > Should this code work if I only pass the StartDate, EndDate and Param1Val? > > > > No. > > > > > > > > > > > > > > > > > > > > > > > > > username=#MM_wdv_USERNAME# password=#MM_wdv_PASSWORD#> > > > SELECT COUNT(Clients.Client_ID) as ClientCount > > > FROM Clients > > > WHERE > > > Client_IntakeDate >= > > cfsqltype="cf_sql_date"> > > > AND > > > Client_IntakeDate <= > > cfsqltype="cf_sql_date"> > > > AND > > > #Form.Param1# = > > > > This will throw an error because form.param1 is undefined. > > > > > AND > > > #Form.Param2# = > > > > Here both form.param2 and form.param2val are undefined. Remember > > that your cfparam is for param2val, not form.param2val. (CF might > > skip past the param2val issue though since it is essentially a > > constant and the compiler might recognize it as such.) > > > > > AND > > > #Form.Param3# = ; > > > > Same issue. > > > > > > > > > Jochem > > > > ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=35 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183278 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 Help Needed
Okay, I see what you mean but I'm not sure how to define since they are passed from selects instead of a db field? Thank you for your help, ~ Donna On Wed, 03 Nov 2004 01:59:39 +0100, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > Donna French wrote: > > Should this code work if I only pass the StartDate, EndDate and Param1Val? > > No. > > > > > > > > > > > > > > > username=#MM_wdv_USERNAME# password=#MM_wdv_PASSWORD#> > > SELECT COUNT(Clients.Client_ID) as ClientCount > > FROM Clients > > WHERE > > Client_IntakeDate >= > cfsqltype="cf_sql_date"> > > AND > > Client_IntakeDate <= > cfsqltype="cf_sql_date"> > > AND > > #Form.Param1# = > > This will throw an error because form.param1 is undefined. > > > AND > > #Form.Param2# = > > Here both form.param2 and form.param2val are undefined. Remember > that your cfparam is for param2val, not form.param2val. (CF might > skip past the param2val issue though since it is essentially a > constant and the compiler might recognize it as such.) > > > AND > > #Form.Param3# = ; > > Same issue. > > > > > Jochem > > ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=35 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183274 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 Help Needed
Donna French wrote: > Should this code work if I only pass the StartDate, EndDate and Param1Val? No. > > > > > username=#MM_wdv_USERNAME# password=#MM_wdv_PASSWORD#> > SELECT COUNT(Clients.Client_ID) as ClientCount > FROM Clients > WHERE > Client_IntakeDate >= cfsqltype="cf_sql_date"> > AND > Client_IntakeDate <= cfsqltype="cf_sql_date"> > AND > #Form.Param1# = This will throw an error because form.param1 is undefined. > AND > #Form.Param2# = Here both form.param2 and form.param2val are undefined. Remember that your cfparam is for param2val, not form.param2val. (CF might skip past the param2val issue though since it is essentially a constant and the compiler might recognize it as such.) > AND > #Form.Param3# = ; Same issue. > Jochem ~| This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.houseoffusion.com/banners/view.cfm?bannerid=11 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183225 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 help...[ SOLVED ]
yes, i can GUARANTEE that. how about $100 pay via paypal? Talk is cheap. Once again, I am using oracle 8i. Max() like count() is a group function. Determines the largest value in a column. Nick Han >>> [EMAIL PROTECTED] 02/17/04 04:21PM >>> > huh? this statement, select max(date_modified) from estates, > will never return more than one record. > > Therefore, using '=' is correct. Can you GUARANTEE that? If 2 records are modified at exactly the same time, it'll return more than one record, which will break the "=" [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...[ SOLVED ]
> huh? this statement, select max(date_modified) from estates, > will never return more than one record. > > Therefore, using '=' is correct. Can you GUARANTEE that? If 2 records are modified at exactly the same time, it'll return more than one record, which will break the "=" [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
And people say that Oracle is better than SQL Server? I do TOP quite a lot, so it'd be annoying to have to bury my query in a sub-query that is "unnecessary" > Intuitively, you would think so, but it doesn't work that > way. The sql engine would do the rownum first and then apply > the order-by clause after. > > So if you do this, select * from users where rownum >=10 and > last_name='SMITH' order by last_name, first_name, you might > not get exactly what you expect. > > If 'smith, andy' is in the 11 th row of the table, he would > not show up. On the other hand, 'smith, zack' in the 5 th > row, he would show up. > > Nick Han [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
Right, I understand that. I didn't say it was resourceful and I wouldn't personally do it, but not knowing oracle, that was the best suggestion I could come up with. John Burns -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 3:12 PM To: CF-Talk Subject: RE: query help... > Well, if you do top 1 you would not want dates desc or the first one > would be the newest (right?). Oops, I mis-read, the desc was in there as I thought he wanted the newest > Other than that, you > could just return the whole query (if there's no sort of top syntax > for your DB it'll work, even though it's not very > resourceful) and just do > maxrows="1"> and that would just output the first record. One word - YUK! Do you know what a "maxrows" does? What you're suggesting is that he returns EVERY record from the set to CF - this could be millions of records if it's some sort of tracking data It's much faster to get the SQL engine to get the single record than to risk the entire recordset being passed Let the SQL engine to the "heavy lifting" and let CF do what it's there for - to do dynamic output and logic! [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
Intuitively, you would think so, but it doesn't work that way. The sql engine would do the rownum first and then apply the order-by clause after. So if you do this, select * from users where rownum >=10 and last_name='SMITH' order by last_name, first_name, you might not get exactly what you expect. If 'smith, andy' is in the 11 th row of the table, he would not show up. On the other hand, 'smith, zack' in the 5 th row, he would show up. Nick Han >>> [EMAIL PROTECTED] 02/17/04 12:12PM >>> > Jeremy, I didn't use the rownum example because in the given > scenario, using rownum may not give you the latest mod date > record from the all records in the table. Won't it give you the first record if you specify an ORDER BY? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
On Oracle: SELECT * FROM ( SELECT myfield FROM mytable ORDER BY date_modified ) WHERE ROWNUM = 1 Steve, What db do you use? > -Original Message- > From: Nick Han [mailto:[EMAIL PROTECTED] > Sent: dinsdag 17 februari 2004 20:58 > To: CF-Talk > Subject: Re:query help... > > Jeremy, I didn't use the rownum example because in the given > scenario, using rownum may not give you the latest mod date > record from the all records in the table. > > Nick Han > > >>> [EMAIL PROTECTED] 02/17/04 10:28AM >>> > Nick, > > FYI in oracle the "top" select is handled by the ROWNUM > pseudocolumn... > > i.e. SELECT myfield FROM mytable WHERE ROWNUM<=10; > > > Jeremy Brodie > Edgewater Technology > > web: http://www.edgewater.com > phone:(703) 815-2500 > email: [EMAIL PROTECTED] > > > > In oracle, there is no 'top' select. In the scenario given, a sub > > select is the most efficient. > > > > My apology for not having asked Dan what DB he was using. > > > > Nick Han > > > > >>> [EMAIL PROTECTED] 02/17/04 10:53AM >>> > > Why not select them ordered by date_modified and then just use the > > first > > record? Or if you're worried about passing all of that > data back and > > forth you can use TOP (or whatever corresponds to your DB server) > > > > John Burns > > > > -Original Message- > > From: Steve Milburn [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, February 17, 2004 1:51 PM > > To: CF-Talk > > Subject: Re: query help... > > > > Daniel Farmer wrote: > > > > > This is my query... > > > > > > select * from estates where featured = 1 and > > > > > > [ I want to select the oldest date_modified field here ] > > > > > > > > > > > > ~~ > > > Daniel Farmer > > > Coldfusion Developer / Sales / Producer > > > 613.284.1684 > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
> Jeremy, I didn't use the rownum example because in the given > scenario, using rownum may not give you the latest mod date > record from the all records in the table. Won't it give you the first record if you specify an ORDER BY? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
> Well, if you do top 1 you would not want dates desc or the > first one would be the newest (right?). Oops, I mis-read, the desc was in there as I thought he wanted the newest > Other than that, you > could just return the whole query (if there's no sort of top > syntax for your DB it'll work, even though it's not very > resourceful) and just do > maxrows="1"> and that would just output the first record. One word - YUK! Do you know what a "maxrows" does? What you're suggesting is that he returns EVERY record from the set to CF - this could be millions of records if it's some sort of tracking data It's much faster to get the SQL engine to get the single record than to risk the entire recordset being passed Let the SQL engine to the "heavy lifting" and let CF do what it's there for - to do dynamic output and logic! [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...[ SOLVED ]
Thanks John, that did it. =) - Original Message - From: Burns, John To: CF-Talk Sent: Tuesday, February 17, 2004 2:30 PM Subject: RE: query help... Well, if you do top 1 you would not want dates desc or the first one would be the newest (right?). Other than that, you could just return the whole query (if there's no sort of top syntax for your DB it'll work, even though it's not very resourceful) and just do query="blah" startrow="1" maxrows="1"> and that would just output the first record. John Burns -Original Message- From: Daniel Farmer [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 2:17 PM To: CF-Talk Subject: Re: query help... I think this has been the closest yet... but still no work. =( - Original Message - From: Philip Arnold To: CF-Talk Sent: Tuesday, February 17, 2004 2:04 PM Subject: RE: query help... select top 1 * from estates where featured = 1 Order by date_modified desc > From: Daniel Farmer [mailto:[EMAIL PROTECTED] > > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
Well, if you do top 1 you would not want dates desc or the first one would be the newest (right?). Other than that, you could just return the whole query (if there's no sort of top syntax for your DB it'll work, even though it's not very resourceful) and just do query="blah" startrow="1" maxrows="1"> and that would just output the first record. John Burns -Original Message- From: Daniel Farmer [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 2:17 PM To: CF-Talk Subject: Re: query help... I think this has been the closest yet... but still no work. =( - Original Message - From: Philip Arnold To: CF-Talk Sent: Tuesday, February 17, 2004 2:04 PM Subject: RE: query help... select top 1 * from estates where featured = 1 Order by date_modified desc > From: Daniel Farmer [mailto:[EMAIL PROTECTED] > > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...
I think this has been the closest yet... but still no work. =( - Original Message - From: Philip Arnold To: CF-Talk Sent: Tuesday, February 17, 2004 2:04 PM Subject: RE: query help... select top 1 * from estates where featured = 1 Order by date_modified desc > From: Daniel Farmer [mailto:[EMAIL PROTECTED] > > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...
OK that sounds like a good solution but how do I select the first record of a query ? - Original Message - From: Burns, John To: CF-Talk Sent: Tuesday, February 17, 2004 1:53 PM Subject: RE: query help... Why not select them ordered by date_modified and then just use the first record? Or if you're worried about passing all of that data back and forth you can use TOP (or whatever corresponds to your DB server) John Burns -Original Message- From: Steve Milburn [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 1:51 PM To: CF-Talk Subject: Re: query help... Daniel Farmer wrote: > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] > > > > ~~ > Daniel Farmer > Coldfusion Developer / Sales / Producer > 613.284.1684 > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
In oracle, there is no 'top' select. In the scenario given, a sub select is the most efficient. My apology for not having asked Dan what DB he was using. Nick Han >>> [EMAIL PROTECTED] 02/17/04 10:53AM >>> Why not select them ordered by date_modified and then just use the first record? Or if you're worried about passing all of that data back and forth you can use TOP (or whatever corresponds to your DB server) John Burns -Original Message- From: Steve Milburn [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 1:51 PM To: CF-Talk Subject: Re: query help... Daniel Farmer wrote: > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] > > > > ~~ > Daniel Farmer > Coldfusion Developer / Sales / Producer > 613.284.1684 > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
select top 1 * from estates where featured = 1 Order by date_modified desc > From: Daniel Farmer [mailto:[EMAIL PROTECTED] > > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
Why not select them ordered by date_modified and then just use the first record? Or if you're worried about passing all of that data back and forth you can use TOP (or whatever corresponds to your DB server) John Burns -Original Message- From: Steve Milburn [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 1:51 PM To: CF-Talk Subject: Re: query help... Daniel Farmer wrote: > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] > > > > ~~ > Daniel Farmer > Coldfusion Developer / Sales / Producer > 613.284.1684 > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...
Daniel Farmer wrote: > This is my query... > > select * from estates where featured = 1 and > > [ I want to select the oldest date_modified field here ] > > > > ~~ > Daniel Farmer > Coldfusion Developer / Sales / Producer > 613.284.1684 > ~~ > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...
This didn't work either... TCX][MyODBC]You have an error in your SQL syntax near - Original Message - From: Tony Weeg To: CF-Talk Sent: Tuesday, February 17, 2004 1:24 PM Subject: RE: query help... select * from estates where featured = 1 and date <= (select max(date_modified) from estates where featured = 1) maybe? -Original Message- From: Daniel Farmer [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 1:15 PM To: CF-Talk Subject: query help... This is my query... select * from estates where featured = 1 and [ I want to select the oldest date_modified field here ] ~~ Daniel Farmer Coldfusion Developer / Sales / Producer 613.284.1684 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...
this didn't work got error... using mySQL btw TCX][MyODBC]You have an error in your SQL syntax near - Original MTCX][MyODBC]You have an error in your SQL syntax near essage - From: Nick Han To: CF-Talk Sent: Tuesday, February 17, 2004 1:21 PM Subject: Re: query help... select * from estates where featured = 1 and date_modified=(select max(date_modified) from estates) Nick Han >>> [EMAIL PROTECTED] 02/17/04 10:14AM >>> This is my query... select * from estates where featured = 1 and [ I want to select the oldest date_modified field here ] ~~ Daniel Farmer Coldfusion Developer / Sales / Producer 613.284.1684 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: query help...
select * from estates where featured = 1 and date <= (select max(date_modified) from estates where featured = 1) maybe? -Original Message- From: Daniel Farmer [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 1:15 PM To: CF-Talk Subject: query help... This is my query... select * from estates where featured = 1 and [ I want to select the oldest date_modified field here ] ~~ Daniel Farmer Coldfusion Developer / Sales / Producer 613.284.1684 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query help...
select * from estates where featured = 1 and date_modified=(select max(date_modified) from estates) Nick Han >>> [EMAIL PROTECTED] 02/17/04 10:14AM >>> This is my query... select * from estates where featured = 1 and [ I want to select the oldest date_modified field here ] ~~ Daniel Farmer Coldfusion Developer / Sales / Producer 613.284.1684 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Query help - Selecting record based on date
> > You may need to use cf_sql_datetime for oracle -- I know > > mssql server doesn't understand date or datetime, only > > timestamp... It doesn't? I always use cf_sql_datetime for dates in SQL Server (I think) without problems. In fact it always seemed to me that the cfqueryparam datatypes were based on the SQL Server list -- and I theorized that that's what the _sql_ refers to . . . maybe not. ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137818 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 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 help - Selecting record based on date
Glad I could help. :) > Great! That works. > Thank you, > James > -Original Message- > From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] > Sent: Sunday, September 21, 2003 2:06 PM > To: CF-Talk > Subject: Re: Query help - Selecting record based on date > Assuming SQL Server or Oracle: > SELECT * FROM MyTable WHERE mydatetimecolumn BETWEEN > value="#date1#" cfsqltype="cf_sql_timestamp"> and > value="#date2#" cfsqltype="cf_sql_timestamp"> > You may need to use cf_sql_datetime for oracle -- I know > mssql server doesn't understand date or datetime, only > timestamp... blech... > but in any event, that should get you on your way. :) > s. isaac dealey972-490-6624 > team macromedia volunteer > http://www.macromedia.com/go/team > chief architect, tapestry cms http://products.turnkey.to > onTap is open source http://www.turnkey.to/ontap >> Hi, >> I'm building an app that needs to retrieve records from a >> SQL Server table >> based on a record's date. >> The date values in the table are in the format of m/d/y >> h:m:s AM/PM >> (8/4/2003 1:02:16 PM). >> The tool I'm building allows the user to input a date >> range in the format of >> 08/04/2003 to 08/30/2003. >> So, for instance a record has the date value of 8/4/2003 >> 1:02:16 PM. I >> want to pass in 08/01/2003 (date1) and 08/15/2003 (date2) >> and >> find that record. >> I've tried converting the date with ParseDateTime(), >> which didn't work >> and trying LIKE '%#date1#%' which didn't work either. >> Suggestions? The data in the table can't be changed, as >> it was >> generated by a tool already in production. >> Thanks, >> James >> ~ >> ~ >> ~~~| >> Message: >> http://www.houseoffusion.com/lists.cfm?link=i:4:137806 >> Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 >> Subscription: >> http://www.houseoffusion.com/lists.cfm?link=s:4 >> Unsubscribe: >> http://www.houseoffusion.com/cf_lists/unsubsc >> ribe.cfm?user=633.558.4 >> 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 > ~~ > ~~~| > Message: > http://www.houseoffusion.com/lists.cfm?link=i:4:137812 > Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 > Subscription: > http://www.houseoffusion.com/lists.cfm?link=s:4 > Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubsc > ribe.cfm?user=633.558.4 > This list and all House of Fusion resources hosted by > CFHosting.com. The place for dependable ColdFusion > Hosting. > http://www.cfhosting.com s. isaac dealey972-490-6624 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137813 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 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 help - Selecting record based on date
Great! That works. Thank you, James -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Sunday, September 21, 2003 2:06 PM To: CF-Talk Subject: Re: Query help - Selecting record based on date Assuming SQL Server or Oracle: SELECT * FROM MyTable WHERE mydatetimecolumn BETWEEN and You may need to use cf_sql_datetime for oracle -- I know mssql server doesn't understand date or datetime, only timestamp... blech... but in any event, that should get you on your way. :) s. isaac dealey972-490-6624 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap > Hi, > I'm building an app that needs to retrieve records from a > SQL Server table > based on a record's date. > The date values in the table are in the format of m/d/y > h:m:s AM/PM > (8/4/2003 1:02:16 PM). > The tool I'm building allows the user to input a date > range in the format of > 08/04/2003 to 08/30/2003. > So, for instance a record has the date value of 8/4/2003 1:02:16 PM. I > want to pass in 08/01/2003 (date1) and 08/15/2003 (date2) and > find that record. > I've tried converting the date with ParseDateTime(), which didn't work > and trying LIKE '%#date1#%' which didn't work either. > Suggestions? The data in the table can't be changed, as it was > generated by a tool already in production. > Thanks, > James > ~~ > ~~~| > Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137806 > Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 > Subscription: > http://www.houseoffusion.com/lists.cfm?link=s:4 > Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubsc > ribe.cfm?user=633.558.4 > 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 ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137812 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
Re: Query help - Selecting record based on date
Assuming SQL Server or Oracle: SELECT * FROM MyTable WHERE mydatetimecolumn BETWEEN and You may need to use cf_sql_datetime for oracle -- I know mssql server doesn't understand date or datetime, only timestamp... blech... but in any event, that should get you on your way. :) s. isaac dealey972-490-6624 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap > Hi, > I'm building an app that needs to retrieve records from a > SQL Server table > based on a record's date. > The date values in the table are in the format of m/d/y > h:m:s AM/PM > (8/4/2003 1:02:16 PM). > The tool I'm building allows the user to input a date > range in the format of > 08/04/2003 to 08/30/2003. > So, for instance a record has the date value of 8/4/2003 > 1:02:16 PM. I want > to pass in 08/01/2003 (date1) and 08/15/2003 (date2) and > find that record. > I've tried converting the date with ParseDateTime(), which > didn't work and > trying LIKE '%#date1#%' which didn't work either. > Suggestions? The data in the table can't be changed, as it > was generated by > a tool already in production. > Thanks, > James > ~~ > ~~~| > Message: > http://www.houseoffusion.com/lists.cfm?link=i:4:137806 > Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 > Subscription: > http://www.houseoffusion.com/lists.cfm?link=s:4 > Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubsc > ribe.cfm?user=633.558.4 > 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 ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137811 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 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
