Re: SQL Question, incrementing values
DOH!! end of the day, yeah... cc.cc_type_id isn't part of a group by or aggregate function.. and I need those end values as part of the returned record set On 12/13/2012 4:49 PM, John M Bliss wrote: > This gives you the error...? > > CASE > WHEN cc.cc_type_ID = 1 THEN @careCB + 1 > WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 > WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 > WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 > WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 > ELSE 0 > END AS myvar > > On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart > wrote: > >> CASE >> >> WHEN cc.cc_type_ID = 1 THEN @careCB + 1 >> >> WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 >> >> WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 >> >> WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 >> >> WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 >> >> ELSE 0 >> >> END >> > > -- Scott Stewart Adobe Certified Expert / Instructor ColdFusion 8, 9 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353455 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
Except I need those values individually as part of the return.. On 12/13/2012 4:49 PM, John M Bliss wrote: > This gives you the error...? > > CASE > WHEN cc.cc_type_ID = 1 THEN @careCB + 1 > WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 > WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 > WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 > WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 > ELSE 0 > END AS myvar > > On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart > wrote: > >> CASE >> >> WHEN cc.cc_type_ID = 1 THEN @careCB + 1 >> >> WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 >> >> WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 >> >> WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 >> >> WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 >> >> ELSE 0 >> >> END >> > > -- Scott Stewart Adobe Certified Expert / Instructor ColdFusion 8, 9 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353454 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
This gives you the error...? CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart wrote: > CASE > > WHEN cc.cc_type_ID = 1 THEN @careCB + 1 > > WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 > > WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 > > WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 > > WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 > > ELSE 0 > > END > -- John Bliss - http://about.me/jbliss ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353453 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL question
Mike Kear wrote: > The first two examples are selecting the literal value 'mike' and '1' > In the first example, you are telling SQL to give the column > containing 'mike' a name of 'name'. aha.. literal was the word I was looking for. I did a search for "sql select literal" and it led me to the following: http://www.firstsql.com/tutor3.htm#literal which describes that functionality. Thanks for the help!! Mike ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319460 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL question
I've always known it as selecting a literal value. So "SELECT 1" is "select the literal value 1". On Mon, Feb 16, 2009 at 7:41 PM, Mike Soultanian wrote: > > I was curious if anyone knows how you describe the following SQL > functionality: > > SELECT 'mike' as name > > returns a single column named "name" with a single row containing "mike" > > I also know you can do stuff like: > > SELECT 1 > > Which returns a column named "1" with a single row containing "1", or: > > SELECT 4/2 > > which returns a column named "4/2" with a single row containing "2.000" > > The last one is obvious as it's SQL arithmetic, but what are the first > two examples? Are those also examples of "SQL arithmetic" as well? I > can't find this kind of SQL functionality described or documented > anywhere on the net. > > Thanks! > Mike > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319398 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL question
> I was curious if anyone knows how you describe the following SQL > functionality: > > ... > > The last one is obvious as it's SQL arithmetic, but what are the first > two examples? Are those also examples of "SQL arithmetic" as well? I > can't find this kind of SQL functionality described or documented > anywhere on the net. I don't think there's any special name for that. They're just SQL commands. There's nothing special about "SQL arithmetic" either - SQL is a programming language, and pretty much all programming languages allow you to perform arithmetic, assign literal values, etc. 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;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319390 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL question
The first two examples are selecting the literal value 'mike' and '1' In the first example, you are telling SQL to give the column containing 'mike' a name of 'name'. A practical example of where you might use this behaviour might be : SELECT 'Invoice' as doctype, invoiceno, invoicedate, amount FROM dbo.invoices WHERE amount > '0' or SELECT 'Credit' as doctype, invoiceno, invoicedate, amount FROM dbo.invoices WHERE amount < '0' Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Feb 17, 2009 at 11:41 AM, Mike Soultanian wrote: > > I was curious if anyone knows how you describe the following SQL > functionality: > > SELECT 'mike' as name > > returns a single column named "name" with a single row containing "mike" > > I also know you can do stuff like: > > SELECT 1 > > Which returns a column named "1" with a single row containing "1", or: > > SELECT 4/2 > > which returns a column named "4/2" with a single row containing "2.000" > > The last one is obvious as it's SQL arithmetic, but what are the first > two examples? Are those also examples of "SQL arithmetic" as well? I > can't find this kind of SQL functionality described or documented > anywhere on the net. > > Thanks! > Mike > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319389 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question -- Order by a column's value?
Yes, you can do this with a CASE statement. The syntax may depend on you db, but on SQL Server ORDER BY CASE Colleges WHEN 'Harvard' THEN 1 WHEN 'Princeton' THEN 2 WHEN 'Dartmouth' THEN 3 ELSE 100 END This would put those three colleges in that order, and all others would go after them. To be smart you should add another ORDER BY field to sort all of the ones that hit the default case in some way (so, sort by the CASE statement, then by the Colleges field). Thanks Mark -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, January 25, 2008 1:41 PM To: CF-Talk Subject: OT: SQL Question -- Order by a column's value? Suppose I have a small set of data with a column named "Colleges". Is there a way to write an ORDER BY statement to say something like... ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? Just wondering... Che ~| 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:297457 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question -- Order by a column's value?
Cool. That did the trick. Thanks to all! -Original Message- From: Gaulin, Mark [mailto:[EMAIL PROTECTED] Sent: Friday, January 25, 2008 2:08 PM To: CF-Talk Subject: RE: SQL Question -- Order by a column's value? Yes, you can do this with a CASE statement. The syntax may depend on you db, but on SQL Server ORDER BY CASE Colleges WHEN 'Harvard' THEN 1 WHEN 'Princeton' THEN 2 WHEN 'Dartmouth' THEN 3 ELSE 100 END This would put those three colleges in that order, and all others would go after them. To be smart you should add another ORDER BY field to sort all of the ones that hit the default case in some way (so, sort by the CASE statement, then by the Colleges field). Thanks Mark -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Friday, January 25, 2008 1:41 PM To: CF-Talk Subject: OT: SQL Question -- Order by a column's value? Suppose I have a small set of data with a column named "Colleges". Is there a way to write an ORDER BY statement to say something like... ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? Just wondering... Che ~| 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:297463 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question, get previous record
You are correct. Thanks! -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:28 PM To: CF-Talk Subject: RE: SQL Question, get previous record I think you'll want an order by on those: to get the previous: SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# ORDER BY idNumber DESC or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# ORDER BY idNumber ASC ~| 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:296629 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question, get previous record
If you already have the result set, and it is ordered by the id, then you could just use this myQueryResult.id[currentrow+/-1] to fetch the previous/next id number very pseudo code here, but hopefully you get the drift. but not quite sure if this is what you're asking? On Jan 15, 2008 1:20 PM, Scott Stewart <[EMAIL PROTECTED]> wrote: > I have a basic table > > It has a numeric primary key and a field that determines display order. > > > > The records are displayed based on the record order. However the record > order may not be continuous > > (IE: 1, 2, 5, 7, 10) > > > > The application calls for an up/down order change feature. What I need to > be > able to do is select record order 7 and move it up or down > > So I'd need to be able to get the order number prior to or the next one. > > > > Any Ideas on how to retrieve this? > > > > sas > > -- > > Scott Stewart > > ColdFusion Developer > > > > SSTWebworks > > 4405 Oakshyre Way > > Raleigh, NC. 27616 > > (919) 874-6229 (home) > > (703) 220-2835 (cell) > > > > > > ~| 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:296628 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question, get previous record
Thanks guys, I knew it had to be something simple -- Scott Stewart ColdFusion Developer SSTWebworks 4405 Oakshyre Way Raleigh, NC. 27616 (919) 874-6229 (home) (703) 220-2835 (cell) -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 2:23 PM To: CF-Talk Subject: RE: SQL Question, get previous record If you are using SQL Server, you can use something like this to get the previous: SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# M!ke -Original Message- From: Scott Stewart [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:21 PM To: CF-Talk Subject: SQL Question, get previous record I have a basic table It has a numeric primary key and a field that determines display order. The records are displayed based on the record order. However the record order may not be continuous (IE: 1, 2, 5, 7, 10) The application calls for an up/down order change feature. What I need to be able to do is select record order 7 and move it up or down So I'd need to be able to get the order number prior to or the next one. Any Ideas on how to retrieve this? sas -- Scott Stewart ColdFusion Developer ~| 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:296627 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question, get previous record
I think you'll want an order by on those: to get the previous: SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# ORDER BY idNumber DESC or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# ORDER BY idNumber ASC ~Brad -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:23 PM To: CF-Talk Subject: RE: SQL Question, get previous record If you are using SQL Server, you can use something like this to get the previous: SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# M!ke -Original Message- From: Scott Stewart [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:21 PM To: CF-Talk Subject: SQL Question, get previous record I have a basic table It has a numeric primary key and a field that determines display order. The records are displayed based on the record order. However the record order may not be continuous (IE: 1, 2, 5, 7, 10) The application calls for an up/down order change feature. What I need to be able to do is select record order 7 and move it up or down So I'd need to be able to get the order number prior to or the next one. Any Ideas on how to retrieve this? sas -- Scott Stewart ColdFusion Developer ~| 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:296626 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question, get previous record
If you are using SQL Server, you can use something like this to get the previous: SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# M!ke -Original Message- From: Scott Stewart [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:21 PM To: CF-Talk Subject: SQL Question, get previous record I have a basic table It has a numeric primary key and a field that determines display order. The records are displayed based on the record order. However the record order may not be continuous (IE: 1, 2, 5, 7, 10) The application calls for an up/down order change feature. What I need to be able to do is select record order 7 and move it up or down So I'd need to be able to get the order number prior to or the next one. Any Ideas on how to retrieve this? sas -- Scott Stewart ColdFusion Developer ~| 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:296625 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question: contains space' '
Mark i am trying to find where only the lastname was added to the field Contact. some 2000+ records. then do a match on a known field like email or phone & then update the Contact field with the combined 'fname lname' from a xls spread sheet. then after all the names are combined i will just add 2 new fields & do it the way it should have been done a few years ago! ~| 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:295756 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question: contains space' '
ok.. thank you! SELECT id, Contact, Address, City, State, Zip FROM Leads WHERE (Contact LIKE '') OR (Contact LIKE ' ') OR (Contact NOT LIKE '% % ') AND (LTRIM(RTRIM(Contact)) NOT LIKE '% %') ORDER BY id now to fix 20,000+ records! wonder if it breaks! ~| 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:295752 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: sql question: contains space' '
If what you are trying to do is eliminate trailing spaces why not just do: Update contacts set contact = rtrim(ltrim(contact)) -Original Message- From: morchella [mailto:[EMAIL PROTECTED] Sent: Thursday, January 03, 2008 9:03 AM To: CF-Talk Subject: sql question: contains space' ' hey guys. this is a 2 parter. i have a table i need to fix. i have no real idea how to do this. the problem is the Contact filed. the first several thousand entries combine fnme & lname into this one field from a xls file. then maybe 20,000 entries only have a name with a space in the Contact filed. then some one caught the error in the xls file & made a tweak to it so the remainding3k entries are fine again. so... Part 1 i need to find out how to do a where statement that looks for a single name that could be any thing, a null or a single name with a space or any combinations of space like 'Joe ', 'Dan ' but NOT 'James Dean' right now my select to find the problem isn't really working.. SELECT rep_assigned, id, industry, Contact, Address, City, State, Zip FROMContacts WHERE (Contact LIKE '') OR (Contact LIKE ' ') Part 2 then i will need to do a find & update from the xls where Contact is a partial match & Address is a full match. this is the part that scares the crap out of me! ~| 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:295753 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question: contains space' '
On 1/3/08, Paul Ihrig <[EMAIL PROTECTED]> wrote: > just fond out why > man this db is so messed up... > > 'Joe Garth ' > > > so i would i look for NOT LIKE '% % ' > OR... ltrim(rtrim(contact)) NOT LIKE '% %' ~| 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:295751 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql question: contains space' '
just fond out why man this db is so messed up... 'Joe Garth ' so i would i look for NOT LIKE '% % ' ~| 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:295750 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql question: contains space' '
You may need to get into database character functions. I believe they all have them, but they all implement them slightly differently. You will need to consult appropriate documentation for you database management system. But you should be able to do something like this concept. SELECT fields FROM aTable WHERE right(aField, 1) = ' ' I.E. Select records where the last character of a field is a space. The purpose of the LIKE operator is to search the entire field for a match, and that is not really what you want. You want to match a specific character. ~| 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:295749 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question: contains space' '
'% ' shouldnt return ' money'
it should only return enteries with a trailing space..
for example
create table testtbl ( name varchar2(10));
insert into testtbl values ('Greg ');
insert into testtbl values ('Greg M');
insert into testtbl values ('Greg Mo');
insert into testtbl values ('Gary ');
insert into testtbl values ('Ghetto Fab');
commit;
select * from testtbl where name like '% '
returns only
NAME
'Greg '
'Gary '
(I added quotes so you can see the trailing space)
On Jan 3, 2008 9:21 AM, morchella <[EMAIL PROTECTED]> wrote:
> but what if i want
> like '% ';
> and
> not like '% money'
>
> where money could be any last name or character.
>
>
> On Jan 3, 2008 10:17 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:
>
> > the SQL statement like requires a %..
> > for example..
> > select * from froo where name like 'G%'
> > will return all names that starts with G..
> > So try something like
> > select * from tbl where name like '% ';
> > That will catch anything with a trailing space.
> > Just a heads up..
> >
> >
> > On Jan 3, 2008 9:03 AM, morchella <[EMAIL PROTECTED]> wrote:
> > > hey guys.
> > > this is a 2 parter.
> > >
> > > i have a table i need to fix. i have no real idea how to do this. the
> > > problem is the Contact filed.
> > >
> > > the first several thousand entries combine fnme & lname into this one
> > field
> > > from a xls file.
> > > then maybe 20,000 entries only have a name with a space in the Contact
> > > filed.
> > > then some one caught the error in the xls file & made a tweak to it so
> > the
> > > remainding3k entries are fine again.
> > >
> > > so...
> > > Part 1
> > > i need to find out how to do a where statement that looks for a single
> > name
> > > that could be any thing, a null or a single name with a space or any
> > > combinations of space
> > >
> > > like 'Joe ', 'Dan ' but NOT 'James Dean'
> > > right now my select to find the problem isn't really working..
> > >
> > > SELECT rep_assigned, id, industry, Contact, Address, City, State,
> > Zip
> > > FROMContacts
> > > WHERE (Contact LIKE '') OR (Contact LIKE ' ')
> > >
> > > Part 2
> > > then i will need to do a find & update from the xls where Contact is a
> > > partial match & Address is a full match.
> > > this is the part that scares the crap out of me!
> > >
> > >
> > >
> >
> >
>
>
~|
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:295746
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question: contains space' '
but what if i want like '% '; and not like '% money' where money could be any last name or character. On Jan 3, 2008 10:17 AM, Greg Morphis <[EMAIL PROTECTED]> wrote: > the SQL statement like requires a %.. > for example.. > select * from froo where name like 'G%' > will return all names that starts with G.. > So try something like > select * from tbl where name like '% '; > That will catch anything with a trailing space. > Just a heads up.. > > > On Jan 3, 2008 9:03 AM, morchella <[EMAIL PROTECTED]> wrote: > > hey guys. > > this is a 2 parter. > > > > i have a table i need to fix. i have no real idea how to do this. the > > problem is the Contact filed. > > > > the first several thousand entries combine fnme & lname into this one > field > > from a xls file. > > then maybe 20,000 entries only have a name with a space in the Contact > > filed. > > then some one caught the error in the xls file & made a tweak to it so > the > > remainding3k entries are fine again. > > > > so... > > Part 1 > > i need to find out how to do a where statement that looks for a single > name > > that could be any thing, a null or a single name with a space or any > > combinations of space > > > > like 'Joe ', 'Dan ' but NOT 'James Dean' > > right now my select to find the problem isn't really working.. > > > > SELECT rep_assigned, id, industry, Contact, Address, City, State, > Zip > > FROMContacts > > WHERE (Contact LIKE '') OR (Contact LIKE ' ') > > > > Part 2 > > then i will need to do a find & update from the xls where Contact is a > > partial match & Address is a full match. > > this is the part that scares the crap out of me! > > > > > > > > ~| 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:295744 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question: contains space' '
the SQL statement like requires a %.. for example.. select * from froo where name like 'G%' will return all names that starts with G.. So try something like select * from tbl where name like '% '; That will catch anything with a trailing space. Just a heads up.. On Jan 3, 2008 9:03 AM, morchella <[EMAIL PROTECTED]> wrote: > hey guys. > this is a 2 parter. > > i have a table i need to fix. i have no real idea how to do this. the > problem is the Contact filed. > > the first several thousand entries combine fnme & lname into this one field > from a xls file. > then maybe 20,000 entries only have a name with a space in the Contact > filed. > then some one caught the error in the xls file & made a tweak to it so the > remainding3k entries are fine again. > > so... > Part 1 > i need to find out how to do a where statement that looks for a single name > that could be any thing, a null or a single name with a space or any > combinations of space > > like 'Joe ', 'Dan ' but NOT 'James Dean' > right now my select to find the problem isn't really working.. > > SELECT rep_assigned, id, industry, Contact, Address, City, State, Zip > FROMContacts > WHERE (Contact LIKE '') OR (Contact LIKE ' ') > > Part 2 > then i will need to do a find & update from the xls where Contact is a > partial match & Address is a full match. > this is the part that scares the crap out of me! > > > ~| 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:295743 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question
> Didn't they mention the table has something like 11 million rows. Oops. That should have been "... would be better than a subquery" Janet ~| 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:289765 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question
>This should be pretty simple actually =) > > >SELECT DISTINCT > Order_num, > datetime_created, > (SELECT product_name FROM tableName WHERE order_num = a.ordernum >AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num = >a.order_num)) as LastProduct >FROM > tableName A Didn't they mention the table has something like 11 million rows. With so many rows I would think a derived table would be than a subquery. Though 2005 might be smart enough to optimize it. I don't know. ~| 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:289763 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question
This should be pretty simple actually =) SELECT DISTINCT Order_num, datetime_created, (SELECT product_name FROM tableName WHERE order_num = a.ordernum AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num = a.order_num)) as LastProduct FROM tableName A This assumes you have some type of auto-incrementing primary key, but you should get the idea. Let me know if this works for you, I tested it in SQL 2005 no sweat. Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 12:11 PM To: CF-Talk Subject: SOT: SQL Question This one's been bugging me for a day now. I don't think this is possible, but before I gave up I thought I would ask. (Yes Rick, I googled it first) As usual, the real scenario is much more complicated, but this is a simple example that shows the concept. Let's say I had a table with multiple products per order and the date they were ordered: order_num product_namedatetime_created 1 apples 9/1/2007 1 oranges 9/10/2007 1 bananas 9/20/2007 2 apples 9/5/2007 2 pears 9/15/2007 2 kiwi9/25/2007 Is it possible with a SINGLE select statement to simply get a distinct list of orders represented with the LAST product ordered like so: order_num product_namedatetime_created 1 bananas 9/20/2007 2 kiwi9/25/2007 The knee jerk reaction is to group by order_num, and then use the max aggregate on datetime_created. That's fine, but then you can't get the corresponding product_name. To get the product name in the select list you have to add it in the group by which then itemizes all the products and you no longer have a distinct list of orders. Using a derived table doesn't even help. I can't do "top 1" with an "order by datetime_ordered desc" because I am reporting across multiple orders. I am on MS SQL Server 2005. The only ways I can find to do this are: 1) Cursor or while loop over orders getting last product row by agonizing row 2) Create function to return last product on the order, and "cross apply" it. The function still has to run RBAR for every order though. 3) Initial select into a temp table with max(datetime_created). Update temp table in a second pass with product_name joining on datetime_created. I don't like joining to a date because it guaranteed unique. Ideas? Thanks. ~Brad ~| 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:289762 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question
>This solution is similar to Greg's in that I will get dupes if more than >one product is added at the same time. Sql 2005 may have a better method for doing this, but you could use 2 derived tables. One to grab the max date by order number, and the other to grab the max record id per order number. Then do an INNER JOIN to get the detailed records. Totally untested, but something like this SELECT t.* FROM t1 AS t INNER JOIN ( SELECT mi.order_number, MAX(mi.YourRecordID) AS YourRecordID FROMt1 AS mi INNER JOIN ( SELECT order_num, max(datetime_created) as latestdate FROM t1 GROUP BY order_num ) AS md ON mi.order_num = md.order_num AND mi.datetime_created = md.latestdate GROUP BY mi.order_number ) AS mx ON t.YourRecordID = mx.YourRecordID ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289760 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question
This solution is similar to Greg's in that I will get dupes if more than one product is added at the same time. Essentially, it is the same logic as my original 3rd option, except for it attempts to do it in one statement. I think the best way may in fact be to get a distinct list of orders in a temp table with their max product's datetime_created, and then perform a second update statement on order_num and datetime_created to get the product name. That would eliminate my dupes. I'm still hitting everything twice-- once with a grouped select, and once with an update, but maybe that's the best way. Joining on a date kind of scared me. It seemed like there should have been a way to get the corresponding primary_key (which has a clustered index) and use that for the join. ~Brad -Original Message- From: Andrew Clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:58 AM To: CF-Talk Subject: RE: SQL Question Just move the aggregate up to the from and do a join: select * from t myT, (select order_num, max(datetime_created) as max_dt_created from t group by order_num) where myT.order_num = .order_num AND myT.datetime_created = .max_dt_created Regardless of how you do this, you first have to determine the max datetime_create, so you're going to have to use a subselect I think. -- Andrew ~| 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:289759 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Question
>Have you tried using a derived table? I think that should work. I think it should work too, assuming there would _not_ be duplicate datetime_created values per order_num. If there were, the query could return multiple rows per order_num. Janet ~| 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:289757 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question
You might want to post this at [EMAIL PROTECTED] There are some sharp SQL experts there. Bruce Brad Wood wrote: > Yes, I did several attempts at a derived table, but I still ran into the > same problem... I couldn't do a top 1 with order by datetime_created > desc because I am reporting across multiple orders, not just one. I > need to group by something, but if it is just the order_num I can't get > the product_name, and if I group by the product_name as well, it > itemizes all the products. > > ~Brad > > > ~| 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:289758 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question
Yes, I did several attempts at a derived table, but I still ran into the same problem... I couldn't do a top 1 with order by datetime_created desc because I am reporting across multiple orders, not just one. I need to group by something, but if it is just the order_num I can't get the product_name, and if I group by the product_name as well, it itemizes all the products. ~Brad -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:54 AM To: CF-Talk Subject: RE: SQL Question Have you tried using a derived table? I think that should work. SELECT * FROM t1 INNER JOIN (SELECT order_num, max(datetime_created) as latestdate FROM t1) AS t2 ON t1.order_num = t2.order_num AND t1.datetime_created = t2.datetime_created M!ke ~| 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:289756 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question
Just move the aggregate up to the from and do a join: select * from t myT, (select order_num, max(datetime_created) as max_dt_created from t group by order_num) where myT.order_num = .order_num AND myT.datetime_created = .max_dt_created Regardless of how you do this, you first have to determine the max datetime_create, so you're going to have to use a subselect I think. -- Andrew -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 12:46 PM To: CF-Talk Subject: RE: SQL Question Doesn't look like MS SQL Server 2005 will let me compare more than one column in a where clause. Even if it did, this requires two selects. My understanding is the sub-select would get ran once for every record in table t. In my case that's 11 Millions times! ~Brad -Original Message- From: Andrew Clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:33 AM To: CF-Talk Subject: RE: SQL Question Off the top of my head I get: select * from t myT where (order_num, datetime_created) = (select order_num, max(datetime_created) from t where order_num = myT.order_num group by order_num) Note: I tested this in postgres, not sql server... -- Andrew ~| 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:289754 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question
Have you tried using a derived table? I think that should work. SELECT * FROM t1 INNER JOIN (SELECT order_num, max(datetime_created) as latestdate FROM t1) AS t2 ON t1.order_num = t2.order_num AND t1.datetime_created = t2.datetime_created M!ke -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:46 AM To: CF-Talk Subject: RE: SQL Question Doesn't look like MS SQL Server 2005 will let me compare more than one column in a where clause. Even if it did, this requires two selects. My understanding is the sub-select would get ran once for every record in table t. In my case that's 11 Millions times! ~Brad -Original Message- From: Andrew Clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:33 AM To: CF-Talk Subject: RE: SQL Question Off the top of my head I get: select * from t myT where (order_num, datetime_created) = (select order_num, max(datetime_created) from t where order_num = myT.order_num group by order_num) Note: I tested this in postgres, not sql server... -- Andrew ~| 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:289752 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question
Clarification: I should have worded that as "Doesn't look like MS SQL Server 2005 will let me return more than one column from a sub-select in a where clause for comparison." I really wish there was an aggregate which would let me select the value of a column from the same row which the max() function picked if that makes any sense. Of course the max value may have existed on more than one row, but I'd settle for a random selection. In that case, multiple products could have been added to the same order at the exact same time so just picking one of those records that had the max value would work. ~Brad -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:46 AM To: CF-Talk Subject: RE: SQL Question Doesn't look like MS SQL Server 2005 will let me compare more than one column in a where clause. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289753 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question
Doesn't look like MS SQL Server 2005 will let me compare more than one column in a where clause. Even if it did, this requires two selects. My understanding is the sub-select would get ran once for every record in table t. In my case that's 11 Millions times! ~Brad -Original Message- From: Andrew Clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:33 AM To: CF-Talk Subject: RE: SQL Question Off the top of my head I get: select * from t myT where (order_num, datetime_created) = (select order_num, max(datetime_created) from t where order_num = myT.order_num group by order_num) Note: I tested this in postgres, not sql server... -- Andrew ~| 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:289750 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question
Off the top of my head I get: select * from t myT where (order_num, datetime_created) = (select order_num, max(datetime_created) from t where order_num = myT.order_num group by order_num) Note: I tested this in postgres, not sql server... -- Andrew -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 12:11 PM To: CF-Talk Subject: SOT: SQL Question This one's been bugging me for a day now. I don't think this is possible, but before I gave up I thought I would ask. (Yes Rick, I googled it first) As usual, the real scenario is much more complicated, but this is a simple example that shows the concept. Let's say I had a table with multiple products per order and the date they were ordered: order_num product_namedatetime_created 1 apples 9/1/2007 1 oranges 9/10/2007 1 bananas 9/20/2007 2 apples 9/5/2007 2 pears 9/15/2007 2 kiwi9/25/2007 Is it possible with a SINGLE select statement to simply get a distinct list of orders represented with the LAST product ordered like so: order_num product_namedatetime_created 1 bananas 9/20/2007 2 kiwi9/25/2007 The knee jerk reaction is to group by order_num, and then use the max aggregate on datetime_created. That's fine, but then you can't get the corresponding product_name. To get the product name in the select list you have to add it in the group by which then itemizes all the products and you no longer have a distinct list of orders. Using a derived table doesn't even help. I can't do "top 1" with an "order by datetime_ordered desc" because I am reporting across multiple orders. I am on MS SQL Server 2005. The only ways I can find to do this are: 1) Cursor or while loop over orders getting last product row by agonizing row 2) Create function to return last product on the order, and "cross apply" it. The function still has to run RBAR for every order though. 3) Initial select into a temp table with max(datetime_created). Update temp table in a second pass with product_name joining on datetime_created. I don't like joining to a date because it guaranteed unique. Ideas? Thanks. ~Brad ~| 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:289749 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Question - seleting row sets
There are a number of ways to do this. The typical way that I get this done is
(im going to use your example of rows 31-50 for a total of 20 rows)
SELECT TOP 20 *
FROM [tableName]
WHERE [primaryKeyField] not in (
SELECT top 30 [primaryKeyField]
FROM [tableName]
WHERE [criteriaField] = 'value'
ORDER BY [sortField]
)
AND ([criteriaField] = 'value')
ORDER BY [sortField]
>After all this time I should know this one but, well... I don't.
>
>How does one go about selecting a specific set of rows from an SQL 2000
>database Query? So as not to confuse the issue let me explain further.
>
>The client has a database table of over 500,000 records and wants to specify
>the query and sort criteria then return a specific set or rows; say rows 800
>thru 850 or rows 30-50. Depending on the selection criteria there could be
>any number of matches up to 500,000. There are no sequentially number
>fields. I really don't want to pull all 500,00 records into memory.
>
>Example:
>
>Select {stuff}
>From {table}
>Where (something = selections)
>Order By Name
>
>However I only want to retrieve rows 30-50. I assume I will first need to
>perform a count using the same criteria to see if those rows exist but then
>how would I go about retrieving just them if they did?
>
>
>
>Best Regards,
>
>Dennis Powers
>UXB Internet - A website design and Hosting Company
>690 Wolcott Road
>P.O. Box 6029
>Wolcott, CT 06716
>Tel: (203)879-2844
>http://www.uxbinternet.com/
>http://www.uxb.net/
~|
Create Web Applications With ColdFusion MX7 & Flex 2.
Build powerful, scalable RIAs. Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284162
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
Hmm, MS SQL must do the cast behind the scenes.
..
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 11:00 AM
To: CF-Talk
Subject: RE: SQL question
Thanks Ben, and Joe here is what finally worked for me.
dateCreated >= '04/03/2007' AND dateCreated < DATEADD(DAY, 1,
'04/03/2007')
With
dateCreated >= '04/03/2007' AND dateCreated < ('04/03/2007' + 1)
I get this error:
Conversion failed when converting the varchar value '04/03/2007' to data
type int.
So I probably have to cast as a date in order to get this to work.
~|
ColdFusion MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274447
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
Thanks Ben, and Joe here is what finally worked for me.
dateCreated >= '04/03/2007' AND dateCreated < DATEADD(DAY, 1, '04/03/2007')
With
dateCreated >= '04/03/2007' AND dateCreated < ('04/03/2007' + 1)
I get this error:
Conversion failed when converting the varchar value '04/03/2007' to data type
int.
So I probably have to cast as a date in order to get this to work.
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:48 AM
To: CF-Talk
Subject: Re: SQL question
Chad,
Try
WHERE
dateCreated >= @someDate AND dateCreated < DATEADD(DAY, 1, @someDate)
-Joe
On Apr 3, 2007, at 10:45 AM, Chad Gray wrote:
> I just tried this and I get no records.
>
> dateCreated >= 04/02/2007 AND dateCreated < (04/02/2007 + 1)
>
> Is this method compatible with MS SQL?
>
>
>
>
> -Original Message-
> From: Chad Gray [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 03, 2007 10:41 AM
> To: CF-Talk
> Subject: RE: SQL question
>
> On the (@date + 1) how do you know it is adding one day?
>
> Out of curiosity how do you add one year?
>
> Thanks for the clean elegant solution. I will try it out.
>
>
> -----Original Message-
> From: Ben Nadel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 03, 2007 10:26 AM
> To: CF-Talk
> Subject: RE: SQL question
>
> People people people :)
>
> I have seen casting, converting, date-diffing, LIKE'ing,
> MONTH()/Day()/Year()'ing Please do not run functions on your
> date/time fields. Running a function on a column in general is
> extremely
> slow. Date/time stamps can be used quite nicely with out them:
>
> DECLARE @date DATETIME;
> SET @date = '04/14/2006';
>
> SELECT *
> FROM [TABLE]
> WHERE
> date_created >= @date
> AND
> date_created < (@date + 1)
>
>
> Notice that I comparing the "date_created" to the set date and also
> that
> it is LESS than the set date PLUS one (the next day). This is going to
> perform a 100 times better than any function you call on the date
> column.
>
>
> Ben Nadel
> Certified Advanced ColdFusion MX7 Developer
> www.bennadel.com
>
> Need ColdFusion Help?
> www.bennadel.com/ask-ben/
>
>
>
>
>
>
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274439
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
Try putting single quotes around the dates (so it know you are not doing
division).
dateCreated >= '04/02/2007' AND dateCreated < ('04/02/2007' + 1)
..
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 03, 2007 10:46 AM
To: CF-Talk
Subject: RE: SQL question
I just tried this and I get no records.
dateCreated >= 04/02/2007 AND dateCreated < (04/02/2007 + 1)
Is this method compatible with MS SQL?
~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274438
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL question
Chad, Try WHERE dateCreated >= @someDate AND dateCreated < DATEADD(DAY, 1, @someDate) -Joe On Apr 3, 2007, at 10:45 AM, Chad Gray wrote: > I just tried this and I get no records. > > dateCreated >= 04/02/2007 AND dateCreated < (04/02/2007 + 1) > > Is this method compatible with MS SQL? > > > > > -Original Message- > From: Chad Gray [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 10:41 AM > To: CF-Talk > Subject: RE: SQL question > > On the (@date + 1) how do you know it is adding one day? > > Out of curiosity how do you add one year? > > Thanks for the clean elegant solution. I will try it out. > > > -Original Message- > From: Ben Nadel [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 10:26 AM > To: CF-Talk > Subject: RE: SQL question > > People people people :) > > I have seen casting, converting, date-diffing, LIKE'ing, > MONTH()/Day()/Year()'ing Please do not run functions on your > date/time fields. Running a function on a column in general is > extremely > slow. Date/time stamps can be used quite nicely with out them: > > DECLARE @date DATETIME; > SET @date = '04/14/2006'; > > SELECT * > FROM [TABLE] > WHERE > date_created >= @date > AND > date_created < (@date + 1) > > > Notice that I comparing the "date_created" to the set date and also > that > it is LESS than the set date PLUS one (the next day). This is going to > perform a 100 times better than any function you call on the date > column. > > > Ben Nadel > Certified Advanced ColdFusion MX7 Developer > www.bennadel.com > > Need ColdFusion Help? > www.bennadel.com/ask-ben/ > > > > > > ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274437 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
Behind the scenes, date/times are really numbers. They are decimal numbers that represent the number of days that have passed since a given starting date. This starting date, the zero date, is different in SQL than it is in Coldfusion, but the theory still holds. One day = 1 Two days = 2 One and a half days = 1.5 One and a quarter days = 1.25 If you play around with CreateTimeSpan() in ColdFusion, you will see this. CreateTimeSpan( 1, 0, 0, 0 ) === 1. So, by adding one to a date, you are adding a single day (since you are adding one to the underlying decimal value). Here are some related links if you are interested: http://www.bennadel.com/blog/226-ColdFusion-Date-Math-Faster-Than-Date-M ethods-And-Other-Date-Math-Ramblings.htm http://www.bennadel.com/blog/311-Ask-Ben-Looping-Through-The-Days-In-A-M onth.htm http://www.bennadel.com/blog/285-Caution-ColdFusion-Zero-Date-vs-SQL-Zer o-Date.htm Cheers! .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:41 AM To: CF-Talk Subject: RE: SQL question On the (@date + 1) how do you know it is adding one day? Out of curiosity how do you add one year? Thanks for the clean elegant solution. I will try it out. ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274436 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL question
DATEADD(YEAR, 1, @someDate) http://www.databasejournal.com/features/mssql/article.php/2216011 has a good primer on date manip for SQL server. -Joe On Apr 3, 2007, at 10:41 AM, Chad Gray wrote: > On the (@date + 1) how do you know it is adding one day? > > Out of curiosity how do you add one year? > > Thanks for the clean elegant solution. I will try it out. > > > -Original Message- > From: Ben Nadel [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 10:26 AM > To: CF-Talk > Subject: RE: SQL question > > People people people :) > > I have seen casting, converting, date-diffing, LIKE'ing, > MONTH()/Day()/Year()'ing Please do not run functions on your > date/time fields. Running a function on a column in general is > extremely > slow. Date/time stamps can be used quite nicely with out them: > > DECLARE @date DATETIME; > SET @date = '04/14/2006'; > > SELECT * > FROM [TABLE] > WHERE > date_created >= @date > AND > date_created < (@date + 1) > > > Notice that I comparing the "date_created" to the set date and also > that > it is LESS than the set date PLUS one (the next day). This is going to > perform a 100 times better than any function you call on the date > column. > > ... > Ben Nadel > Certified Advanced ColdFusion MX7 Developer > www.bennadel.com > > Need ColdFusion Help? > www.bennadel.com/ask-ben/ > > > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274435 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
I just tried this and I get no records. dateCreated >= 04/02/2007 AND dateCreated < (04/02/2007 + 1) Is this method compatible with MS SQL? -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:41 AM To: CF-Talk Subject: RE: SQL question On the (@date + 1) how do you know it is adding one day? Out of curiosity how do you add one year? Thanks for the clean elegant solution. I will try it out. -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:26 AM To: CF-Talk Subject: RE: SQL question People people people :) I have seen casting, converting, date-diffing, LIKE'ing, MONTH()/Day()/Year()'ing Please do not run functions on your date/time fields. Running a function on a column in general is extremely slow. Date/time stamps can be used quite nicely with out them: DECLARE @date DATETIME; SET @date = '04/14/2006'; SELECT * FROM [TABLE] WHERE date_created >= @date AND date_created < (@date + 1) Notice that I comparing the "date_created" to the set date and also that it is LESS than the set date PLUS one (the next day). This is going to perform a 100 times better than any function you call on the date column. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274434 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
On the (@date + 1) how do you know it is adding one day? Out of curiosity how do you add one year? Thanks for the clean elegant solution. I will try it out. -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:26 AM To: CF-Talk Subject: RE: SQL question People people people :) I have seen casting, converting, date-diffing, LIKE'ing, MONTH()/Day()/Year()'ing Please do not run functions on your date/time fields. Running a function on a column in general is extremely slow. Date/time stamps can be used quite nicely with out them: DECLARE @date DATETIME; SET @date = '04/14/2006'; SELECT * FROM [TABLE] WHERE date_created >= @date AND date_created < (@date + 1) Notice that I comparing the "date_created" to the set date and also that it is LESS than the set date PLUS one (the next day). This is going to perform a 100 times better than any function you call on the date column. ... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274432 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
Ah Gotcha. -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:35 AM To: CF-Talk Subject: RE: SQL question BETWEEN is good, but it is doubly-inclusive meaning that it is like doing both >= and <=. In this case, it might turn up records where the created date is exactly 12:00 AM the next day. What we need is the >= and the < (not using the equals sign in the second comparison). ... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:30 AM To: CF-Talk Subject: RE: SQL question Ben, Ok... Nicely done. What about "BETWEEN" ... Any benefits there? WHERE date_created BETWEEN @date AND @date + 1 -Mark ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274431 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
BETWEEN is good, but it is doubly-inclusive meaning that it is like doing both >= and <=. In this case, it might turn up records where the created date is exactly 12:00 AM the next day. What we need is the >= and the < (not using the equals sign in the second comparison). .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:30 AM To: CF-Talk Subject: RE: SQL question Ben, Ok... Nicely done. What about "BETWEEN" ... Any benefits there? WHERE date_created BETWEEN @date AND @date + 1 -Mark ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274430 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
Ben, Ok... Nicely done. What about "BETWEEN" ... Any benefits there? WHERE date_created BETWEEN @date AND @date + 1 -Mark -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:26 AM To: CF-Talk Subject: RE: SQL question People people people :) I have seen casting, converting, date-diffing, LIKE'ing, MONTH()/Day()/Year()'ing Please do not run functions on your date/time fields. Running a function on a column in general is extremely slow. Date/time stamps can be used quite nicely with out them: DECLARE @date DATETIME; SET @date = '04/14/2006'; SELECT * FROM [TABLE] WHERE date_created >= @date AND date_created < (@date + 1) Notice that I comparing the "date_created" to the set date and also that it is LESS than the set date PLUS one (the next day). This is going to perform a 100 times better than any function you call on the date column. ... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274427 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
People people people :) I have seen casting, converting, date-diffing, LIKE'ing, MONTH()/Day()/Year()'ing Please do not run functions on your date/time fields. Running a function on a column in general is extremely slow. Date/time stamps can be used quite nicely with out them: DECLARE @date DATETIME; SET @date = '04/14/2006'; SELECT * FROM [TABLE] WHERE date_created >= @date AND date_created < (@date + 1) Notice that I comparing the "date_created" to the set date and also that it is LESS than the set date PLUS one (the next day). This is going to perform a 100 times better than any function you call on the date column. .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274426 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
The trick with date and MSSQL is using > and <. The string '04/02/2007' is seen by MSSQL as '04/02/2007 00:00:00', so your condition will return only those records with that exact timestamp. You have to use: WHERE dateCreated >= '04/02/2007' AND dateCreateted < '04/03/2007' Steve Brownlee http://www.fusioncube.net/ > -Original Message- > From: Chad Gray [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 9:59 AM > To: CF-Talk > Subject: SQL question > > I am using MS SQL and have a field with data type DateTime. > > I want to find all records with the day 4/2/2007? > > If I do > WHERE dateCreated = '04/02/2007' > > I get no records and I have lots of records like this in the database: > > 4/2/2007 9:57:57 AM > 4/2/2007 10:57:57 AM > 4/2/2007 5:57:57 AM > 4/2/2007 2:57:57 AM > > I want to keep the time in my data so I don't want to modify > the data type. > > How do I write my query to find all records on the day 4/2/2007? ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274425 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
Option 1. Use the CONVERT() function to get strip the time from the date. Then, you have a date string to which you can compare. Option 2. Use a combination of DAY() MONTH() YEAR() SQL functions. (This isn't the best solution, but it would work.) Ex: WHERE DAY(dateCol)=#dayVar# AND MONTH(dateCol)=#monthVar# AND YEAR(dateCol)=#yearVal# -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:12 AM To: CF-Talk Subject: RE: SQL question I tried that too and no records are returned. -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:09 AM To: CF-Talk Subject: RE: SQL question How about using... WHERE dateCreated LIKE '%4/2/2007%' -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:05 AM To: CF-Talk Subject: RE: SQL question I tried that also and no records are returned. -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:01 AM To: CF-Talk Subject: RE: SQL question How about you simply use WHERE dateCreated = '4/2/2007' Basically, lose the padding zeros. ~Che -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:59 AM To: CF-Talk Subject: SQL question I am using MS SQL and have a field with data type DateTime. I want to find all records with the day 4/2/2007? If I do WHERE dateCreated = '04/02/2007' I get no records and I have lots of records like this in the database: 4/2/2007 9:57:57 AM 4/2/2007 10:57:57 AM 4/2/2007 5:57:57 AM 4/2/2007 2:57:57 AM I want to keep the time in my data so I don't want to modify the data type. How do I write my query to find all records on the day 4/2/2007? ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274423 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
AH! This works! Thanks! -Original Message- From: Paul Hastings [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:10 AM To: CF-Talk Subject: Re: SQL question Chad Gray wrote: > I want to find all records with the day 4/2/2007? WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0 ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274421 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
Select * >From table Where convert(varchar, datecreated, 101)='04/02/07' Jim Gurfein managing member mediaSPA 56 locust avenue | rye | ny 10580 | usa t 914.921.3200 x 101 | m 914.588.9392 | f 914.921.9190 uk | 0207-993-2601 x101 www.mediaspa.com -Original Message- From: Paul Hastings [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:10 AM To: CF-Talk Subject: Re: SQL question Chad Gray wrote: > I want to find all records with the day 4/2/2007? WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0 ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274420 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
Ooh.. I like that one -Original Message- From: Paul Hastings [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:10 AM To: CF-Talk Subject: Re: SQL question Chad Gray wrote: > I want to find all records with the day 4/2/2007? WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0 ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274419 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL question
Give the CONVERT function a try: WHERE CONVERT(varchar(10), dateCreated, 101) = '04/02/2007' This will change the style of the dateCreated value into mm/dd/ format, which is what the 101 means, when running the comparison. On 4/3/07, Chad Gray <[EMAIL PROTECTED]> wrote: > I tried that also and no records are returned. > > > -Original Message- > From: Che Vilnonis [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 10:01 AM > To: CF-Talk > Subject: RE: SQL question > > How about you simply use WHERE dateCreated = '4/2/2007' > Basically, lose the padding zeros. > > ~Che > > -Original Message- > From: Chad Gray [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 9:59 AM > To: CF-Talk > Subject: SQL question > > > I am using MS SQL and have a field with data type DateTime. > > I want to find all records with the day 4/2/2007? > > If I do > WHERE dateCreated = '04/02/2007' > > I get no records and I have lots of records like this in the database: > > 4/2/2007 9:57:57 AM > 4/2/2007 10:57:57 AM > 4/2/2007 5:57:57 AM > 4/2/2007 2:57:57 AM > > I want to keep the time in my data so I don't want to modify the data type. > > > How do I write my query to find all records on the day 4/2/2007? > > > > > > > ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274418 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
I tried that too and no records are returned. -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:09 AM To: CF-Talk Subject: RE: SQL question How about using... WHERE dateCreated LIKE '%4/2/2007%' -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:05 AM To: CF-Talk Subject: RE: SQL question I tried that also and no records are returned. -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:01 AM To: CF-Talk Subject: RE: SQL question How about you simply use WHERE dateCreated = '4/2/2007' Basically, lose the padding zeros. ~Che -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:59 AM To: CF-Talk Subject: SQL question I am using MS SQL and have a field with data type DateTime. I want to find all records with the day 4/2/2007? If I do WHERE dateCreated = '04/02/2007' I get no records and I have lots of records like this in the database: 4/2/2007 9:57:57 AM 4/2/2007 10:57:57 AM 4/2/2007 5:57:57 AM 4/2/2007 2:57:57 AM I want to keep the time in my data so I don't want to modify the data type. How do I write my query to find all records on the day 4/2/2007? ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274416 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL question
Chad Gray wrote: > I want to find all records with the day 4/2/2007? WHERE DATEDIFF(day, dateCreated,'4/3/2007')=0 ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274415 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
How about using... WHERE dateCreated LIKE '%4/2/2007%' -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:05 AM To: CF-Talk Subject: RE: SQL question I tried that also and no records are returned. -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:01 AM To: CF-Talk Subject: RE: SQL question How about you simply use WHERE dateCreated = '4/2/2007' Basically, lose the padding zeros. ~Che -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:59 AM To: CF-Talk Subject: SQL question I am using MS SQL and have a field with data type DateTime. I want to find all records with the day 4/2/2007? If I do WHERE dateCreated = '04/02/2007' I get no records and I have lots of records like this in the database: 4/2/2007 9:57:57 AM 4/2/2007 10:57:57 AM 4/2/2007 5:57:57 AM 4/2/2007 2:57:57 AM I want to keep the time in my data so I don't want to modify the data type. How do I write my query to find all records on the day 4/2/2007? ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274414 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL question
I tried that also and no records are returned. -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 10:01 AM To: CF-Talk Subject: RE: SQL question How about you simply use WHERE dateCreated = '4/2/2007' Basically, lose the padding zeros. ~Che -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:59 AM To: CF-Talk Subject: SQL question I am using MS SQL and have a field with data type DateTime. I want to find all records with the day 4/2/2007? If I do WHERE dateCreated = '04/02/2007' I get no records and I have lots of records like this in the database: 4/2/2007 9:57:57 AM 4/2/2007 10:57:57 AM 4/2/2007 5:57:57 AM 4/2/2007 2:57:57 AM I want to keep the time in my data so I don't want to modify the data type. How do I write my query to find all records on the day 4/2/2007? ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274412 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL question
I wrote a blog post concerning this a while back: http://www.stillnetstudios.com/2007/01/20/comparing-dates-without-times-in-sql-server/ Hope that helps. -Ryan Chad Gray wrote: > I am using MS SQL and have a field with data type DateTime. > > I want to find all records with the day 4/2/2007? > > If I do > WHERE dateCreated = '04/02/2007' > > I get no records and I have lots of records like this in the database: > > 4/2/2007 9:57:57 AM > 4/2/2007 10:57:57 AM > 4/2/2007 5:57:57 AM > 4/2/2007 2:57:57 AM > > I want to keep the time in my data so I don't want to modify the data type. > > How do I write my query to find all records on the day 4/2/2007? > > > ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274411 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
How about you simply use WHERE dateCreated = '4/2/2007' Basically, lose the padding zeros. ~Che -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:59 AM To: CF-Talk Subject: SQL question I am using MS SQL and have a field with data type DateTime. I want to find all records with the day 4/2/2007? If I do WHERE dateCreated = '04/02/2007' I get no records and I have lots of records like this in the database: 4/2/2007 9:57:57 AM 4/2/2007 10:57:57 AM 4/2/2007 5:57:57 AM 4/2/2007 2:57:57 AM I want to keep the time in my data so I don't want to modify the data type. How do I write my query to find all records on the day 4/2/2007? ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question
On what engine? If this is MSSQL, try running the query tuning advisor. There maybe some updates to indexes or statistics that will speed it up. -Original Message- From: Jerry Barnes [mailto:[EMAIL PROTECTED] Sent: Monday, April 02, 2007 12:22 PM To: CF-Talk Subject: OT: SQL Question The following query is slow. I'd like to speed it up a bit. Any suggestions would be appreciated. SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F LEFT OUTER JOIN v_sae_jna_mr M ON F.recordthread = M.i_recordThread WHERE (M.i_recid IS NULL) Basically, the query is looking for items in one table that have not been marked in another table. I have done this before but don't ever remember it being this slow. I can probably figure this out on my own at some point, but the mental block I have now may take several days to work out. Thanks. J ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274330 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Question
Wold moving the M.i_recid IS NULL to the JOIN help? SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F LEFT OUTER JOIN v_sae_jna_mr M ON F.recordthread = M.i_recordThread AND M.i_recid IS NULL Adrian -Original Message- From: Jerry Barnes [mailto:[EMAIL PROTECTED] Sent: 02 April 2007 17:22 To: CF-Talk Subject: OT: SQL Question The following query is slow. I'd like to speed it up a bit. Any suggestions would be appreciated. SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F LEFT OUTER JOIN v_sae_jna_mr M ON F.recordthread = M.i_recordThread WHERE (M.i_recid IS NULL) Basically, the query is looking for items in one table that have not been marked in another table. I have done this before but don't ever remember it being this slow. I can probably figure this out on my own at some point, but the mental block I have now may take several days to work out. Thanks. J ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274326 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question?
Doug Brown wrote: > Ok, so if my data will only be supporting the English language I should just > use varhcar or char since n uses 2 bytes for one character. Correct? never say "never". unless you're going to be dealing in TB of data, better safe than sorry. ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274155 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Question?
Doug Brown wrote: > I understand several things about SQL when it comes to getting information > out of it, but never really have understood which data types to use for what > specific data. I know what ones suppose to hold what kind of data as far as > integer data, character data, monetary data, data and time data, binary > strings, and so on. I am mostly confused with n(varchar) or (n)char. I know > that varchar is for using Non-Unicode data and nvarchar is for Unicode that > is of varying length, but when would I use each? Are you asking about the language SQL or about a specific implementation? Jochem ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274142 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question?
Disk space is cheap. If there is ever a chance that you'll need other characters embedded in text sometime in the future, then it pays to sacrifice the extra space and use the N data types now. --- Ben -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 4:22 PM To: CF-Talk Subject: RE: SQL Question? I believe so... -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 4:03 PM To: CF-Talk Subject: RE: SQL Question? Ok, so if my data will only be supporting the English language I should just use varhcar or char since n uses 2 bytes for one character. Correct? Doug -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 1:58 PM To: CF-Talk Subject: RE: SQL Question? I've always read that you use nvarchar for multilingual data. Keep in mind, nvarchar takes up twice as much space in the db since it makes an alotment for languages that have extended characters. ~C -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 3:48 PM To: CF-Talk Subject: SQL Question? I understand several things about SQL when it comes to getting information out of it, but never really have understood which data types to use for what specific data. I know what ones suppose to hold what kind of data as far as integer data, character data, monetary data, data and time data, binary strings, and so on. I am mostly confused with n(varchar) or (n)char. I know that varchar is for using Non-Unicode data and nvarchar is for Unicode that is of varying length, but when would I use each? Hope I make a little sense. Doug ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274132 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question?
I believe so... -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 4:03 PM To: CF-Talk Subject: RE: SQL Question? Ok, so if my data will only be supporting the English language I should just use varhcar or char since n uses 2 bytes for one character. Correct? Doug -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 1:58 PM To: CF-Talk Subject: RE: SQL Question? I've always read that you use nvarchar for multilingual data. Keep in mind, nvarchar takes up twice as much space in the db since it makes an alotment for languages that have extended characters. ~C -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 3:48 PM To: CF-Talk Subject: SQL Question? I understand several things about SQL when it comes to getting information out of it, but never really have understood which data types to use for what specific data. I know what ones suppose to hold what kind of data as far as integer data, character data, monetary data, data and time data, binary strings, and so on. I am mostly confused with n(varchar) or (n)char. I know that varchar is for using Non-Unicode data and nvarchar is for Unicode that is of varying length, but when would I use each? Hope I make a little sense. Doug ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274131 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question?
Ok, so if my data will only be supporting the English language I should just use varhcar or char since n uses 2 bytes for one character. Correct? Doug -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 1:58 PM To: CF-Talk Subject: RE: SQL Question? I've always read that you use nvarchar for multilingual data. Keep in mind, nvarchar takes up twice as much space in the db since it makes an alotment for languages that have extended characters. ~C -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 3:48 PM To: CF-Talk Subject: SQL Question? I understand several things about SQL when it comes to getting information out of it, but never really have understood which data types to use for what specific data. I know what ones suppose to hold what kind of data as far as integer data, character data, monetary data, data and time data, binary strings, and so on. I am mostly confused with n(varchar) or (n)char. I know that varchar is for using Non-Unicode data and nvarchar is for Unicode that is of varying length, but when would I use each? Hope I make a little sense. Doug ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274129 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question?
I've always read that you use nvarchar for multilingual data. Keep in mind, nvarchar takes up twice as much space in the db since it makes an alotment for languages that have extended characters. ~C -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 3:48 PM To: CF-Talk Subject: SQL Question? I understand several things about SQL when it comes to getting information out of it, but never really have understood which data types to use for what specific data. I know what ones suppose to hold what kind of data as far as integer data, character data, monetary data, data and time data, binary strings, and so on. I am mostly confused with n(varchar) or (n)char. I know that varchar is for using Non-Unicode data and nvarchar is for Unicode that is of varying length, but when would I use each? Hope I make a little sense. Doug ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274128 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL QUestion
Damn! Always the simple shit that gets me. Thanks. On 2/1/07, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > > Bruce Sorge wrote: > > OK, I figured it out pretty much. So now I have this: > > > > CREATE TABLE #tempduplicatedata > > ( > > Code NVARCHAR(20) > > ) > > > > > > --Identify and save dup data into temp table > > INSERT INTO #tempduplicatedata > > INSERT INTO #tempduplicatedata (code) > > > SELECT Code FROM Codes > > GROUP BY Code > > HAVING COUNT(Code) > 1 > > > Jochem > "I'm a mawg: half man, half dog. I'm my own best friend!" ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268379 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL QUestion
Bruce Sorge wrote: > OK, I figured it out pretty much. So now I have this: > > CREATE TABLE #tempduplicatedata > ( > Code NVARCHAR(20) > ) > > > --Identify and save dup data into temp table > INSERT INTO #tempduplicatedata INSERT INTO #tempduplicatedata (code) > SELECT Code FROM Codes > GROUP BY Code > HAVING COUNT(Code) > 1 Jochem ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268377 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: sql question
You may want to recompile the sp... It may have been compiled before the "statistics" for the tables involved were updated. You could check the query plan of the sp vs. the query to see how they differ. (Not sure what db you are using...) Mark -Original Message- From: Tim Do [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 1:45 PM To: CF-Talk Subject: OT: sql question I have a stored procedure that is taking 15-20 seconds to run. However, if I take the query inside the stored procedure and run it... it only takes 2-3 seconds. I've read something about parameter sniffing but not sure if it applies to my stored procedure. Here is my sp: CREATE PROC dbo.get_vendorActivityPeriod ( @periodMonth integer , @periodYear integer , @propertyID varchar(6) = NULL ) as Begin select count(*) countVoucherHeader , month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) periodMonth , year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) periodYear , A.vendor_code , (select top 1 address_name from apmaster where vendor_code = A.vendor_code order by address_type, pay_to_code) address_name , sum(amt_gross) as amountGross , case when @propertyID IS NULL then '' else @propertyID end propertyID , case when @propertyID IS NULL then 'All Communities' else (select distinct display_name from property.dbo.complex where display_num = @propertyID) end propertyDescription from apvohdr A where month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) = @periodMonth and year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) = @periodYear and A.posting_code = IsNull(@propertyID, A.posting_code) group by month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , vendor_code order by year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , address_name end thanks! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268345 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: sql question
How many records are coming back? How are you calling it? I have ran tests before for where running exec sp_name in side of a cfquery was faster than cfstoredproc. Just a thought. Also are you sure the stored proc is really taking 20 seconds to run OR is the CF page just taking 17 seconds to produce its output. This could be determined by running a trace while you hit the page if you are using SQL Server. ~Brad -Original Message- From: Tim Do [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 12:45 PM To: CF-Talk Subject: OT: sql question I have a stored procedure that is taking 15-20 seconds to run. However, if I take the query inside the stored procedure and run it... it only takes 2-3 seconds. I've read something about parameter sniffing but not sure if it applies to my stored procedure. Here is my sp: CREATE PROC dbo.get_vendorActivityPeriod ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268344 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL QUestion
Bruce Sorge wrote: > I have a lot of duplicate information in a table. I know how to query to > find the dupes, but I am having problems with deleting them (there are > thousands). I tried this: create a clone of your table but make your "key" duplicated column as a unique key setting the index to "ignore duplicate keys". then do a SELECT/INSERT INSERT clonedTable... SELECT ... FROM tableWithDuplicates the db server will toss out all the duplicated rows leaving the first inserted one intact. btw this is for sql server. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268341 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL QUestion
I actually bloged about this earlier this month. See here: http://www.ruslansivak.com/index.cfm/2007/1/10/Deleting-duplicate-rows-from- SQL-Server Russ > -Original Message- > From: Bruce Sorge [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 01, 2007 12:26 PM > To: CF-Talk > Subject: SQL QUestion > > I have a lot of duplicate information in a table. I know how to query to > find the dupes, but I am having problems with deleting them (there are > thousands). I tried this: > > DELETE > FROM CODES > WHERE Code = > (SELECT Code, > COUNT(Code) AS NumOccurrences > FROM Codes > GROUP BY Code > HAVING ( COUNT(Code) > 1 )) > > Now of of course even if this one did work, it just occured to me that it > will not do what I want it to. What I want is to leave one instance of the > code in the database and delete the duplicates only. Is this possible > without me going line by line? > > Thanks, > > -- > Bruce Sorge > > "I'm a mawg: half man, half dog. I'm my own best friend!" > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268340 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL QUestion
OK, I figured it out pretty much. So now I have this: CREATE TABLE #tempduplicatedata ( Code NVARCHAR(20) ) --Identify and save dup data into temp table INSERT INTO #tempduplicatedata SELECT Code FROM Codes GROUP BY Code HAVING COUNT(Code) > 1 --Confirm number of dup rows SELECT @@ROWCOUNT AS 'Number of Duplicate Rows' --Delete dup from original table DELETE FROM Codes FROM Codes INNER JOIN #tempduplicatedata ON Codes.Code= #tempduplicatedata.Code --Insert the delete data back INSERT INTO Codes SELECT Code FROM #tempduplicatedata --Check for dup data. SELECT Code FROM Codes GROUP BY Code HAVING COUNT(Code) > 1 --Check table SELECT Code FROM Codes --Drop temp table DROP TABLE #tempduplicatedata But I am getting the error Insert Error: Column name or number of supplied values does not match table definition. So this tells me that there is a problem with an insert, but not sure which one. Any SQL Guru's out there that can spot the problem? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268338 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL QUestion
Bruce Sorge wrote: > I have a lot of duplicate information in a table. I know how to query to > find the dupes, but I am having problems with deleting them (there are > thousands). I tried this: > > DELETE > FROM CODES > WHERE Code = > (SELECT Code, > COUNT(Code) AS NumOccurrences > FROM Codes > GROUP BY Code > HAVING ( COUNT(Code) > 1 )) > > Now of of course even if this one did work, it just occured to me that it > will not do what I want it to. What I want is to leave one instance of the > code in the database and delete the duplicates only. Is this possible > without me going line by line? > Is Code the only field? One way would be something like this... SELECT DISTINCT Code INTO #tempcodetable FROM Codes DELETE FROM Codes INSERT INTO Codes SELECT Code FROM #tempcodetable DROP #tempcodetable And of course, make a backup before attempting any operation like this. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268337 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL QUestion
Without looking too hard, would using a TOP 1 in the sub select work? -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED] Sent: 01 February 2007 17:26 To: CF-Talk Subject: SQL QUestion I have a lot of duplicate information in a table. I know how to query to find the dupes, but I am having problems with deleting them (there are thousands). I tried this: DELETE FROM CODES WHERE Code = (SELECT Code, COUNT(Code) AS NumOccurrences FROM Codes GROUP BY Code HAVING ( COUNT(Code) > 1 )) Now of of course even if this one did work, it just occured to me that it will not do what I want it to. What I want is to leave one instance of the code in the database and delete the duplicates only. Is this possible without me going line by line? Thanks, -- Bruce Sorge "I'm a mawg: half man, half dog. I'm my own best friend!" ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268336 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL QUestion
Bruce, One way to accomplish this is to query your (unique) records and populate another table with the same structure with that data. Once it is done, then you can re-populate that table from the table you created. Hope that makes sense. Doug B. - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Thursday, February 01, 2007 10:25 AM Subject: SQL QUestion > I have a lot of duplicate information in a table. I know how to query to > find the dupes, but I am having problems with deleting them (there are > thousands). I tried this: > > DELETE > FROM CODES > WHERE Code = > (SELECT Code, > COUNT(Code) AS NumOccurrences > FROM Codes > GROUP BY Code > HAVING ( COUNT(Code) > 1 )) > > Now of of course even if this one did work, it just occured to me that it > will not do what I want it to. What I want is to leave one instance of the > code in the database and delete the duplicates only. Is this possible > without me going line by line? > > Thanks, > > -- > Bruce Sorge > > "I'm a mawg: half man, half dog. I'm my own best friend!" > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268335 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL question
That looks like the right/only way to do it as far as I know. Mark -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Thursday, November 30, 2006 11:02 AM To: CF-Talk Subject: OT: SQL question I am not a MS SQL guru and this bit of SQL is about as advanced as I get. I am basically doing a query on a table of catalogs and elements inside of the catalog. Each element has a history of status changes (new, in progress, done etc). I want to get the most current status for the element from the Statushistory table so I use MAX() on the UID of the history table (see the second left join). Then I take that max UID and use it to look up the information on the Status (see the third left join). Is there a better way of writing this? If there are a lot of elements (100 or more) then the query can take a couple of seconds to perform. SELECT c.CatalogID, c.CatalogName, e.*, es.ElementStatus, es.ElementStatusDate, es.UserName FROM Catalogs as c LEFT JOIN Elements as e ON c.CatalogID = e.CatalogID LEFT JOIN ( SELECT MAX(ElementStatusHistoryID) as MaxESHID, ElementID FROM ElementStatusHistory GROUP BY ElementID ) as maxESH ON (e.ElementID = maxESH.ElementID) LEFT JOIN ( SELECT ElementStatusHistoryID, ElementStatus, ElementStatusDate, UserName FROM ElementStatusHistory ) as es ON (es.ElementStatusHistoryID = maxESH.MaxESHID) WHERE c.CatalogID = 10 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262233 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question
You could also do: SELECT MAX(thedate) FROM yourtable -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Sunday, October 22, 2006 9:05 AM To: CF-Talk Subject: SQL Question Im a little burnt out need some help. If I query a table that tracks the number of times a book was checked out and returned. I want to get the most recent item in the table to find the current status of the book. So I can find out if it is checked in or out. How do I write the SQL to find just the highest date/time stamp for that book? Thansk! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257739 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question
Thanks Kris! That makes sense. > -Original Message- > From: Kris Jones [mailto:[EMAIL PROTECTED] > Sent: Sunday, October 22, 2006 10:12 AM > To: CF-Talk > Subject: Re: SQL Question > > select top 1 * > from tablename > order by datefield desc > > > On 10/22/06, Chad Gray <[EMAIL PROTECTED]> wrote: > > Im a little burnt out need some help. > > > > If I query a table that tracks the number of times a book was checked > out and returned. I want to get the most recent item in the table to find > the current status of the book. So I can find out if it is checked in or > out. > > > > How do I write the SQL to find just the highest date/time stamp for that > book? > > > > Thansk! > > > > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257703 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Question
select top 1 * from tablename order by datefield desc On 10/22/06, Chad Gray <[EMAIL PROTECTED]> wrote: > Im a little burnt out need some help. > > If I query a table that tracks the number of times a book was checked out and > returned. I want to get the most recent item in the table to find the > current status of the book. So I can find out if it is checked in or out. > > How do I write the SQL to find just the highest date/time stamp for that book? > > Thansk! > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257702 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql question.
Thank you all for your suggestions, after half a day of hair pulling, I figured
out what's going on.. actually the database table given to me wasn't
consistent. There really were no listings for some folders. That was the
reason...
Otherwise to achieve what I wanted, this will work:
select file_id,path,[desc],unit, displayName as name,pic_quality from
#request.filetablename# where lower(path) like '#lcase(relPath)#%' and
charindex('#path_separator#',path,len('#relPath#')+1)=0
Once again, thanks alot for your time...
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248729
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question.
Thank you all for your suggestions, after half a day of hair pulling, I figured
out what's going on.. actually the database table given to me wasn't
consistent. There really were no listings for sme folders. that was the
reason...
Otherwise to achieve what I wanted, this will work:
select file_id,path,[desc],unit, displayName as name,pic_quality from
#request.filetablename# where lower(path) like '#lcase(relPath)#%' and
charindex('#path_separator#',path,len('#relPath#')+1)=0
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248728
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: sql question.
Agreeing with everyone who has already posted, this might not be the best place to be doing this... But one more option to play with: WHERE [field] LIKE '_%\_%' AND [field] NOT LIKE '%.__' AND [field] NOT LIKE '%.___' AND [field] NOT LIKE '%.' ... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com "Some people call me the space cowboy. Some people call me the gangster of love." -Original Message- From: Brian Dumbledore [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 4:31 PM To: CF-Talk Subject: sql question. I am using MS-SQL, I couldn't get this to work.. I tried, patindex,charindex, like combinations, none worked. I have directory paths in a table, given a starting path of a directory, I want to get its first level elements. eg: table has warranty\a\1.jpg warranty\a warranty\a\2.jpg warranty\a22 warranty\a-23 I want to get warranty\a,warranty\a22,warranty\a-23 I tried all combinations, it doesn't work (correctly). I actually get only warranty\a, but don't get the other two (which makes me thing it is because of the numbers in the string.. but doesn't make any sense). Please help Thanks for your time. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248687 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question.
Quite rightly sucks - it is not the place for a regex. "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Mingo Hagen To: CF-Talk Sent: Wed Aug 02 21:56:24 2006 Subject: Re: sql question. SQL Server sucks at regexps, you have some rudimentary tools at your disposal with the LIKE statement, but none that I could get to work, I have in the past found a regexp stored procedure but that's just dog slow... This is the like statement I tried: SELECT * FROM listing WHERE dir LIKE It doesn't work because the % sign already matches to the end of the string... so sorry :( Mingo. Brian Dumbledore wrote: > I am using MS-SQL, I couldn't get this to work.. > > I tried, patindex,charindex, like combinations, none worked. > > I have directory paths in a table, given a starting path of a directory, I want to get its first level elements. > > eg: > > table has > warranty\a\1.jpg > warranty\a > warranty\a\2.jpg > warranty\a22 > warranty\a-23 > > I want to get warranty\a,warranty\a22,warranty\a-23 > > I tried all combinations, it doesn't work (correctly). I actually get only warranty\a, but don't get the other two (which makes me thing it is because of the numbers in the string.. but doesn't make any sense). Please help > > Thanks for your time. > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248686 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: sql question.
select *
>From tablename
where patindex('%\%\%',dir) = 0
And patindex('%\%',dir) > 0
-Original Message-
From: Brian Dumbledore [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 02, 2006 4:31 PM
To: CF-Talk
Subject: sql question.
I am using MS-SQL, I couldn't get this to work..
I tried, patindex,charindex, like combinations, none worked.
I have directory paths in a table, given a starting path of a directory, I want
to get its first level elements.
eg:
table has
warranty\a\1.jpg
warranty\a
warranty\a\2.jpg
warranty\a22
warranty\a-23
I want to get warranty\a,warranty\a22,warranty\a-23
I tried all combinations, it doesn't work (correctly). I actually get only
warranty\a, but don't get the other two (which makes me thing it is because of
the numbers in the string.. but doesn't make any sense). Please help
Thanks for your time.
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248685
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql question.
On 8/2/06, Brian Dumbledore <[EMAIL PROTECTED]> wrote:
> I am using MS-SQL, I couldn't get this to work..
>
> I tried, patindex,charindex, like combinations, none worked.
>
> I have directory paths in a table, given a starting path of a directory, I
> want to get its first level elements.
>
> eg:
>
> table has
> warranty\a\1.jpg
> warranty\a
> warranty\a\2.jpg
> warranty\a22
> warranty\a-23
>
> I want to get warranty\a,warranty\a22,warranty\a-23
>
> I tried all combinations, it doesn't work (correctly). I actually get only
> warranty\a, but don't get the other two (which makes me thing it is because
> of the numbers in the string.. but doesn't make any sense). Please help
>
SELECT * FROM table WHERE CHARINDEX('\',thecolumn,CHARINDEX ('\',foo)+1) = 0
--
Jim Wright
Wright Business Solutions
[EMAIL PROTECTED]
919-417-2257
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248684
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql question.
SQL Server sucks at regexps, you have some rudimentary tools at your disposal with the LIKE statement, but none that I could get to work, I have in the past found a regexp stored procedure but that's just dog slow... This is the like statement I tried: SELECT * FROM listing WHERE dir LIKE It doesn't work because the % sign already matches to the end of the string... so sorry :( Mingo. Brian Dumbledore wrote: > I am using MS-SQL, I couldn't get this to work.. > > I tried, patindex,charindex, like combinations, none worked. > > I have directory paths in a table, given a starting path of a directory, I > want to get its first level elements. > > eg: > > table has > warranty\a\1.jpg > warranty\a > warranty\a\2.jpg > warranty\a22 > warranty\a-23 > > I want to get warranty\a,warranty\a22,warranty\a-23 > > I tried all combinations, it doesn't work (correctly). I actually get only > warranty\a, but don't get the other two (which makes me thing it is because > of the numbers in the string.. but doesn't make any sense). Please help > > Thanks for your time. > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248683 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL question
On Monday 12 June 2006 15:54, Ben Nadel wrote: > But it might be better to make something with more feedback: Or use two queries and a transaction SELECT MAX(StatusWhen) as maxDate FROM table UPDATE table SET STATUS = 'approved', WHERE SKU = http://www.houseoffusion.com/lists.cfm/link=i:4:243211 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: SQL question -- Thanks!
Thanks for the answers... they all appear to do what I need. Thanks again, Chad ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243204 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: SQL question
Chad, You can do a sub query: UPDATE table SET STATUS = 'approved', WHERE SKU = #URL.SKU# AND StatusWhen = ( SELECT MAX(StatusWhen) FROM table ) But it might be better to make something with more feedback: // Declare the ID to be updated DECLARE @id INT; // Get the ID to be updated SET @id = ISNULL( ( SELECT [id] FROM table WHERE SKU = #URL.SKU# AND StatusWhen = ( SELECT MAX(StatusWhen) FROM table ) ), 0 ) // Update the table UPDATE table SET STATUS = 'approved', WHERE [id] = @id // Return the updated record id SELECT @id This of course assumes you have some sort of ID column. ... Ben Nadel www.bennadel.com -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED] Sent: Monday, June 12, 2006 10:45 AM To: CF-Talk Subject: SQL question How would I write the SQL to update a record that has the most recent date? Say I want to change the status field to "Approved" where the date is the most recent and SKU = 12345 Will the Max function work in the Where section of the SQL or is there another function to use to find the most recent date? UPDATE table SET STATUS = 'approved', WHERE SKU = #URL.SKU# AND MAX(StatusWhen) Thanks, Chad ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243200 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: SQL question
Chad Gray wrote: > How would I write the SQL to update a record that has the most recent date? > > Say I want to change the status field to "Approved" where the date is the > most recent and SKU = 12345 > > Will the Max function work in the Where section of the SQL or is there > another function to use to find the most recent date? > > UPDATE table > SET STATUS = 'approved', > WHERE SKU = #URL.SKU# AND MAX(StatusWhen) > > > Thanks, > Chad Something like this usually works UPDATE table SET STATUS = 'approved' WHERE SKU = #URL.SKU# AND ( StatusWhen = ( SELECT TOP 1 StatusWhen FROM table WHERE SKU = #URL.SKU# ORDER BY StatusWhen DESC ) ) Didn't check any of it, but it'll show the idea. You might wanna use by the way. If you like your night's rest. Rens ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243199 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: SQL question
UPDATE tablename SET status = 'approved' WHERE SKU = #URL.SKU# AND statusWhen = (SELECT MAX(statusWhen) FROM tablename) i think that should do it... (to answer the actual question, I'm not sure if the MAX() function will work as you originally asked) On 6/12/06, Chad Gray <[EMAIL PROTECTED]> wrote: > How would I write the SQL to update a record that has the most recent date? > > Say I want to change the status field to "Approved" where the date is the > most recent and SKU = 12345 > > Will the Max function work in the Where section of the SQL or is there > another function to use to find the most recent date? > > UPDATE table > SET STATUS = 'approved', > WHERE SKU = #URL.SKU# AND MAX(StatusWhen) > > > Thanks, > Chad > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243198 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: SQL question
use a subquery update table set status = 'approved' where sku = and statuswhen = ( select max(statuswhen) from status where ... --include where clauses ) On 6/12/06, Chad Gray <[EMAIL PROTECTED]> wrote: > How would I write the SQL to update a record that has the most recent date? > > Say I want to change the status field to "Approved" where the date is the > most recent and SKU = 12345 > > Will the Max function work in the Where section of the SQL or is there > another function to use to find the most recent date? > > UPDATE table > SET STATUS = 'approved', > WHERE SKU = #URL.SKU# AND MAX(StatusWhen) > > > Thanks, > Chad > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243197 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: sql question.....
a cleaner way of writing this might be: INSERT INTO hourly (employee_id ,ticket_no ,ticket_date ,labor_start ,labor_stop ,labor_lunch ,truck_id ,equip_start ,equip_stop ,equip_down ,equip_idle) VALUES (#employee_id# ,'#ticket_no#' ,'#ticket_date#' , , ad infinitum :) On 4/19/06, David Elliott <[EMAIL PROTECTED]> wrote: > > Below is a part of my code > > > INSERT INTO hourly > (employee_id > ,ticket_no > ,ticket_date > ,labor_start > ,labor_stop > ,labor_lunch > ,truck_id > ,equip_start > ,equip_stop > ,equip_down > ,equip_idle) > VALUES > (#employee_id# > ,'#ticket_no#' > ,'#ticket_date#' > ,'#labor_start#' > ,'#labor_stop#' > ,#labor_lunch# > ,#truck_id# > ,'#equip_start#' > ,'#equip_stop#' > ,#equip_down# > ,#equip_idle#) > > > And my question might be a simple onethe line that says equip_idle IS NOT "">,equip_idle...does this mean I'll only have any > entry if their is a value in it? And isn't the code for the value portion > of the statement also show up (as far as the field goes) if there is a > value? > > Dave > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238185 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: sql question.....
No problem. On 4/19/06, David Elliott <[EMAIL PROTECTED]> wrote: > Thanks Ryan. > > Ryan Guill <[EMAIL PROTECTED]> wrote: On 4/19/06, David Elliott wrote: > > Below is a part of my code > > > > > And my question might be a simple onethe line that says > > ,equip_idle...does this mean I'll only have any entry if their is a value > > in it? > > Yes, but a better way I think is to say > NEQ 0> but yeah, this should work too. > > >And isn't the code for the value portion of the statement also show > up (as far as the field >goes) if there is a value? > > > Yep, it should show up too. > > -- > Ryan Guill > A Deep Blue > [EMAIL PROTECTED] > www.ryanguill.com > (270) 217.2399 > got google talk? Chat me at [EMAIL PROTECTED] > > The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com > > Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ > > www.ryanguill.com/ > The Roman Empire: www.ryanguill.com/blog/ > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238181 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: sql question.....
Dave, I am not sure I follow 100%, but yes, the CFIF statements will stop the values from being added. However, the column will still be in the database for that new record and will have whatever default value you have assigned to that column (or NULL if no default value has been set and the column allows NULL values). ... Ben Nadel www.bennadel.com -Original Message- From: David Elliott [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 19, 2006 2:26 PM To: CF-Talk Subject: sql question. Below is a part of my code INSERT INTO hourly (employee_id ,ticket_no ,ticket_date ,labor_start ,labor_stop ,labor_lunch ,truck_id ,equip_start ,equip_stop ,equip_down ,equip_idle) VALUES (#employee_id# ,'#ticket_no#' ,'#ticket_date#' ,'#labor_start#' ,'#labor_stop#' ,#labor_lunch# ,#truck_id# ,'#equip_start#' ,'#equip_stop#' ,#equip_down# ,#equip_idle#) And my question might be a simple onethe line that says ,equip_idle...does this mean I'll only have any entry if their is a value in it? And isn't the code for the value portion of the statement also show up (as far as the field goes) if there is a value? Dave ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238180 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: sql question.....
Thanks Ryan. Ryan Guill <[EMAIL PROTECTED]> wrote: On 4/19/06, David Elliott wrote: > Below is a part of my code > > And my question might be a simple onethe line that says > ,equip_idle...does this mean I'll only have any entry if their is a value in > it? Yes, but a better way I think is to say NEQ 0> but yeah, this should work too. >And isn't the code for the value portion of the statement also show up (as far as the field >goes) if there is a value? Yep, it should show up too. -- Ryan Guill A Deep Blue [EMAIL PROTECTED] www.ryanguill.com (270) 217.2399 got google talk? Chat me at [EMAIL PROTECTED] The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com Use CF and SQL? Try qBrowser - http://www.ryanguill.com/docs/ www.ryanguill.com/ The Roman Empire: www.ryanguill.com/blog/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238179 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
