Re: (ot) Transact-SQL Help
Brad, I'll have to look at your response in much greater detail, but I can tell you this. Currently, I'm running a CF script that populates a prospect_export table once a day. The initial query returns 25,785 rows, which gets flattened into 20,265 rows for reporting purposes. Ultimately I'd rather this were a view, but barring that, I'd rather repopulate the table more often than once a day, and I'm fairly certain that doing it with SQL instead of CF would be more efficient. -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
(ot) Transact-SQL Help
I'm hoping someone here can point me in the right direction. I'm doing something in CF that I really need to be doing in SQL: I need to flatten this data: select A.entityid, A.MEMBERID, A.RELTYPE, A.leaderFlag from PTRACK.dbo.ptTeam A WHERE A.status=1 into this format: entityid, EXEC1, (1st row reltype='ETL') EXEC2, (2nd row reltype='ETL') TEAMLEAD, (1st row leaderFlag=1) MEMBER1,(1st row reltype='TMM' and leaderflag=0) MEMBER2,(2nd row reltype='TMM' and leaderflag=0) MEMBER3,(3rd row reltype='TMM' and leaderflag=0) MEMBER4,(4th row reltype='TMM' and leaderflag=0) MEMBER5,(5th row reltype='TMM' and leaderflag=0) AFSTAFF1, (1st row reltype='AFS') AFSTAFF2, (2nd row reltype='AFS') AFVOL,, (1st row reltype='AFV') DOCTOR1,, (1st row reltype='AMD') DOCTOR2,, (1st row reltype='AMD') The query returns multiple rows per entity. The output will be one row per entity. In CF, I'm looping over the first query, and creating a resultset that looks like the second query. If an entity has more than 1 executive team leader or more than 5 team members or more than 2 doctors associated, we ignore the extras. But I don't have the slightest friggin' clue how to do this in SQL. My db is MS SQL Server 2005 Thanks -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
Hi Rick, This is where the limiting nature of SQL comes in--SQL is a set language and has no (or very limited) looping/reshaping capability. Unless MS has extended their SQL in ways I'm not aware of, this would be nearly impossible. That's where the power of CF comes in. --Ben Rick Root wrote: I'm hoping someone here can point me in the right direction. I'm doing something in CF that I really need to be doing in SQL: I need to flatten this data: select A.entityid, A.MEMBERID, A.RELTYPE, A.leaderFlag from PTRACK.dbo.ptTeam A WHERE A.status=1 into this format: entityid, EXEC1, (1st row reltype='ETL') EXEC2, (2nd row reltype='ETL') TEAMLEAD, (1st row leaderFlag=1) MEMBER1,(1st row reltype='TMM' and leaderflag=0) MEMBER2,(2nd row reltype='TMM' and leaderflag=0) MEMBER3,(3rd row reltype='TMM' and leaderflag=0) MEMBER4,(4th row reltype='TMM' and leaderflag=0) MEMBER5,(5th row reltype='TMM' and leaderflag=0) AFSTAFF1, (1st row reltype='AFS') AFSTAFF2, (2nd row reltype='AFS') AFVOL,, (1st row reltype='AFV') DOCTOR1,, (1st row reltype='AMD') DOCTOR2,, (1st row reltype='AMD') The query returns multiple rows per entity. The output will be one row per entity. In CF, I'm looping over the first query, and creating a resultset that looks like the second query. If an entity has more than 1 executive team leader or more than 5 team members or more than 2 doctors associated, we ignore the extras. But I don't have the slightest friggin' clue how to do this in SQL. My db is MS SQL Server 2005 Thanks -- Ben Connerb...@webworldinc.com Web World, Inc. 888-206-6486 PO Box 1122 480-704-2000 Queen Creek, AZ 85242 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
This is where the limiting nature of SQL comes in--SQL is a set language and has no (or very limited) looping/reshaping capability. Unless MS has extended their SQL in ways I'm not aware of, this would be nearly impossible. That's where the power of CF comes in. That's not true. You can use cursors to iterate through a recordset, for example. 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 informatio ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326210 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
You might also take a look at ms sql 2005's row_number() function. You may be able to partition the data and use row_number() to limit the returned records. http://msdn.microsoft.com/en-us/library/ms186734.aspx ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326213 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: (ot) Transact-SQL Help
Original Message Subject: (ot) Transact-SQL Help From: Rick Root rick.r...@webworksllc.com Date: Thu, September 10, 2009 12:25 pm To: cf-talk cf-talk@houseoffusion.com I'm hoping someone here can point me in the right direction. I'm doing something in CF that I really need to be doing in SQL: After reading your post several times I think I might understand just what it is you are trying to accomplish here. Questions: What makes you think you need to do this in SQL? Is the current code performing poorly? Are there really hundreds of columns and this is just a simplified example? Are there really only 13 records in the ptTeam table or are you actually doing dozens of times on one page? Is there any particular difference between EXEC1 and EXEC2 or is it just up to the randomness of the query as to who gets to be 1 and who gets to be 2? Do you know if you will have at least one of every type of person? Since you asked-- as for SQL solutions, it looks like you are basically wanting a column for each record matching a particular criteria. My first thought is a messy select statement that joins to the ptTeam table once for every column. This will actually be made harder if the order of the members of the same type is not explicit. Note: you would need to change each join that isn't guaranteed to return a match to an LEFT OUTER JOIN. I'm also making the wild assumption that your records all share the same entityid but have a unique MEMBERID. You also didn't say what column you wanted to return for each member so I assumed you wanted the MEMBERID column. SELECT TOP 1 EXEC1.MEMBERID AS EXEC1, EXEC1.MEMBERID AS EXEC2, TEAMLEAD.MEMBERID AS TEAMLEAD, etc... FROM ptTeam EXEC1 INNER JOIN ptTeam EXEC2 ON EXEC1.entityid = 'foo' AND EXEC1.status = 1 AND reltype='ETL' AND EXEC1.MEMBERID EXEC2.MEMBERID INNER JOIN ptTeam TEAMLEAD ON EXEC1.entityid = 'foo' AND TEAMLEAD.status = 1 AND TEAMLEAD.leaderFlag=1 INNER JOIN etc... WHERE EXEC1.status = 1 AND reltype='ETL' AND EXEC1.entityid = 'foo' For the record, I would expect this to perform like crap since it's going to make every possible combination and then pick one to return with TOP 1. Moving on, another option would be to declare a table variable to hold your final result set: DECLARE @finalResult TABLE (entityid int, EXEC1 int, EXEC2 int, TEAMLEAD int, MEMBER1 int, MEMBER2 int, MEMBER3 int, MEMBER4 int etc...) Then populate your columns one statement at a time: INSERT INTO @finalResult (entityid, EXEC1) (SELECT entityid, MEMEBRID FRKM PTRACK.dbo.ptTeam WHERE status=1 AND reltype='ETL' UPDATE fr SET fr.EXEC2 = team.MEMBERID FROM @finalResult fr INNER JOIN ptTeam team ON fr.entityid = team.entityid AND team.status=1 AND team.reltype='ETL' AND fr.EXEC1 team.MEMBERID UPDATE fr SET fr.TEAMLEAD = lead.MEMBERID FROM @finalResult fr INNER JOIN ptTeam lead ON fr.entityid = lead.entityid AND lead.status=1 AND lead.leaderFlag=1 etc... Ok, now that I gave those examples let me say I think they're both pretty crappy. Honestly I'd do this in CF if it were me based on what you showed us, but I would do it with query of queries. Return your 15 or so records in a single, simple hit to the database. Then perform 6 qofqs on it-- one for each type of member. In other words, get all the EXECs in one, and all team lead in another, and then just loop over them as you go and output them as EXEC1, EXEC2, TEAMLEAD, etc. As long as you are only dealing with a dozen or so records from the database it should perform fine and and I think it will be a heck of a lot simpler than trying to make your SQL server take a row-based list of people and pivot them out into columns. That never works easily because it really isn't the kind of thing SQL Server was designed to do. On the cursor thing-- that is another option I didn't present, but I really don't think it's a better one. Thanks. ~Brad ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326214 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) Transact-SQL Help
As long as you are only dealing with a dozen or so records from the database it should perform fine and and I think it will be a heck of a lot simpler than trying to make your SQL server take a row-based list of people and pivot them out into columns. Assuming the example is actually representative of the data, partitioning with row_number() may be a feasible alternative to pivoting or cursors. But it would probably still require a bit of fancy footwork in terms of sql. Having said that, Brad raises some valid questions about the data. Is it really that small and simple a recordset, and if so what is the perceived advantage of doing it in sql over using CF ? -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326216 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact SQL question has me stumped
Dennis... Learn something new everyday. I guess I did not know there was bit operator in CF :) -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 1:11 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped Mark, I knew I would most likely not explain it properly. The existing database has a column that contains values that are typed as a BigInt. I have a filter that gets constructed from Bit values for example: the filter = 129 which was constructed from bit 1 binary + bit 8 Binary 1 + 128. What I need to do is write a select statement where it returns all rows that where the BigInt column has bit 1 AND Bit 128. So it would select rows where the bigInt column contained: 1 3 5 128 129 384 385 SELECT mycolumns FROM mytable WHERE ? Thanks! 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/ ~| 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:298320 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Transact SQL question has me stumped
I meant... I didn't know there was a bit operator in MSSQL ... In my defense I'm not feeling well today and I've had a variety of pills of dubious quality and nature. -mk -Original Message- From: Mark Kruger [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 06, 2008 8:47 AM To: CF-Talk Subject: RE: Transact SQL question has me stumped Dennis... Learn something new everyday. I guess I did not know there was bit operator in CF :) -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 1:11 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped Mark, I knew I would most likely not explain it properly. The existing database has a column that contains values that are typed as a BigInt. I have a filter that gets constructed from Bit values for example: the filter = 129 which was constructed from bit 1 binary + bit 8 Binary 1 + 128. What I need to do is write a select statement where it returns all rows that where the BigInt column has bit 1 AND Bit 128. So it would select rows where the bigInt column contained: 1 3 5 128 129 384 385 SELECT mycolumns FROM mytable WHERE ? Thanks! 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/ ~| 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:298322 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Transact SQL question has me stumped
I am hoping an SQL guru can assist me with what I am sure is a stupid little
oversight or misunderstanding on my part.
I hope I can explain this. I need to do a bit evaluation against data in the
database where the data is stored in a BigInit column. Within my code I
construct a bit filter and need to select all records where the logical AND
of that value against the BigInt field is true.
For example If my bit filter = 129 (1*2) + (8*2) What I need to do is select
all records Where the BigInt Column has bit 1 AND bit 8. I am not sure how
to evaluate this function in a where clause.
SELECT mycolumns
FROM mytable
WHERE BigIntColumn CAST(#filter# as BigInt)) = {and this is where I fall
down}
Any help is appreciated.
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/
~|
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:298232
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact SQL question has me stumped
Are you sure that bigInt is what you think it is? I thought it was just a
signed integer (2billion+ to -2billion+).
Meanwhile, the code below will throw a syntax error on the ampersand...
BigIntCol CAST(...) ... The ampersand is not the concatenation operator
in T-SQL. You have to use a plus sign. If you are trying to concatenate you
would have to do something like
CAST( CAST(bigintCol AS varchar(8)) + '#filter#' AS bigInt)
In other words - cast your column to a character type, concatenate them and
then cast them back to a biginto type. Seems like a lot of work :)
-Mark
-Original Message-
From: Dennis Powers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 12:01 PM
To: CF-Talk
Subject: Transact SQL question has me stumped
I am hoping an SQL guru can assist me with what I am sure is a stupid little
oversight or misunderstanding on my part.
I hope I can explain this. I need to do a bit evaluation against data in the
database where the data is stored in a BigInit column. Within my code I
construct a bit filter and need to select all records where the logical AND
of that value against the BigInt field is true.
For example If my bit filter = 129 (1*2) + (8*2) What I need to do is select
all records Where the BigInt Column has bit 1 AND bit 8. I am not sure how
to evaluate this function in a where clause.
SELECT mycolumns
FROM mytable
WHERE BigIntColumn CAST(#filter# as BigInt)) = {and this is where I fall
down}
Any help is appreciated.
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/
~|
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:298238
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Transact SQL question has me stumped
Mark, I knew I would most likely not explain it properly. The existing database has a column that contains values that are typed as a BigInt. I have a filter that gets constructed from Bit values for example: the filter = 129 which was constructed from bit 1 binary + bit 8 Binary 1 + 128. What I need to do is write a select statement where it returns all rows that where the BigInt column has bit 1 AND Bit 128. So it would select rows where the bigInt column contained: 1 3 5 128 129 384 385 SELECT mycolumns FROM mytable WHERE ? Thanks! 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/ ~| 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:298240 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact SQL question has me stumped
You need a bitwise operator. Bit and is in MS SQL SELECT mycolumns FROM mytable WHERE bit_column 128 = 128 Should be along the lines of what you want I think. This example works for me on MS SQL 2005: declare @test as bigint set @test = 128 select @test 128 ~Brad -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 1:11 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped Mark, I knew I would most likely not explain it properly. The existing database has a column that contains values that are typed as a BigInt. I have a filter that gets constructed from Bit values for example: the filter = 129 which was constructed from bit 1 binary + bit 8 Binary 1 + 128. What I need to do is write a select statement where it returns all rows that where the BigInt column has bit 1 AND Bit 128. So it would select rows where the bigInt column contained: 1 3 5 128 129 384 385 SELECT mycolumns FROM mytable WHERE ? Thanks! 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/ ~| 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:298244 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact SQL question has me stumped
You need a bitwise operator. Bit and is in MS SQL SELECT mycolumns FROM mytable WHERE bit_column 128 = 128 This was essentially what I was doing but it does not work properly for matching multiple bits in the bit_column, Example: matching a row that has Bit 1 and Bit 8 (129) or matching a row that has Bit 1 and Bit 4 (9). Is my only choice looping over the filter and dynamically constructing the Where clause from the binary value of the filter? 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/ ~| 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:298245 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact SQL question has me stumped
I'm not Guru, but this should do it.
First you create a mask by turning on the bits you need.
myMask = 2^1 + 2 ^8
Then, bitwise AND () with your column. The result needs to be equal to your
mask to have all the specific bits turned on. NOTE: This assumes you don't care
about the value in the rest of the bits.
SELECT mycolumns
FROM mytable
WHERE BigIntColumn #myMask# = #myMask#
NOTE: I don't think you have to cast anything as long as you are dealing with
ints...but this is the part where I mention IANAG (I am not a guru).
-jeff
I am hoping an SQL guru can assist me with what I am sure is a stupid little
oversight or misunderstanding on my part.
I hope I can explain this. I need to do a bit evaluation against data in the
database where the data is stored in a BigInit column. Within my code I
construct a bit filter and need to select all records where the logical AND
of that value against the BigInt field is true.
For example If my bit filter = 129 (1*2) + (8*2) What I need to do is select
all records Where the BigInt Column has bit 1 AND bit 8. I am not sure how
to evaluate this function in a where clause.
SELECT mycolumns
FROM mytable
WHERE BigIntColumn CAST(#filter# as BigInt)) = {and this is where I fall
down}
Any help is appreciated.
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/
~|
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:298248
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact SQL question has me stumped
Ok, so the problem is that you want the where clause to be dynamic based on some user-entered search criteria? So you want to match 0 to n possible bits? I see two possible SQL solutions right off: 1) Populate temp table with candidate records in a cursor or while loop for each filter (yuck) 2) Dynamic SQL. I would do the latter. Dynamically create a SQL string which looks like this: SELECT mycolumns FROM mytable WHERE bit_column 1 = 1 AND bit_column 8 = 8 AND bit_column 32 = 32 AND bit_column 128 = 128 Ect ... ~Brad -Original Message- From: Dennis Powers [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 2:02 PM To: CF-Talk Subject: RE: Transact SQL question has me stumped You need a bitwise operator. Bit and is in MS SQL SELECT mycolumns FROM mytable WHERE bit_column 128 = 128 This was essentially what I was doing but it does not work properly for matching multiple bits in the bit_column, Example: matching a row that has Bit 1 and Bit 8 (129) or matching a row that has Bit 1 and Bit 4 (9). Is my only choice looping over the filter and dynamically constructing the Where clause from the binary value of the filter? ~| 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:298250 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact SQL question has me stumped
doh! That should be 2^0 + 2^7 (silly me) myMask = 2^1 + 2 ^8 ~| 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:298257 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Transact SQL question has me stumped
Jeff, myMask = 2^1 + 2 ^8 SELECT mycolumns FROM mytable WHERE BigIntColumn #myMask# = #myMask# This was exactly what I was doing - irrespective of the CAST to change data types - but it will only select records that have bit 1 AND Bit 8. What I need to do is to select records that contain Bit 1 OR Bit 8. (example values only) Example: Given these values BigIntColumn == 1 2 4 8 128 129 256 257 Select == 1 = (bit 1) 5 = (bit 1 + bit 3) 128 = (bit 8) 129 = (bit 1 + bit 8 257 = (bit 1 + bit 9) I am so confused :( 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/ ~| 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:298255 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact SQL question has me stumped
Ah, you changed the question :) You originally said AND, now it is OR! It doesn't change the problem much. Step 1: Create your mask Step 2: Zero out the bits we don't care about with Step 3: If we are left with anything, we have records that contain a flag. WHERE myColumn myMask 0 enjoy! -jeff This was exactly what I was doing - irrespective of the CAST to change data types - but it will only select records that have bit 1 AND Bit 8. What I need to do is to select records that contain Bit 1 OR Bit 8. (example values only) Example: Given these values BigIntColumn == 1 2 4 8 128 129 256 257 Select == 1 = (bit 1) 5 = (bit 1 + bit 3) 128 = (bit 8) 129 = (bit 1 + bit 8 257 = (bit 1 + bit 9) I am so confused :( 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/ ~| 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:298260 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact SQL question has me stumped
Jeff, Ah, you changed the question :) You originally said AND, now it is OR! I knew I was phrasing it incorrectly smile mixing Boolean and linguistic and WHERE myColumn myMask 0 This is much too easy and I can't believe I overlooked so simple a basic Boolean solution. That sound you heard is my head hitting the table. Thank you for taking the time with me. I appreciate it. 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/ ~| 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:298277 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: Transact-SQL number formatting
Phillip, that worked great, thanks!
On 6/19/07, Phillip Ciske [EMAIL PROTECTED] wrote:
T-SQL has a replicate() function that can help. You can pad the last
four digits with zeros using:
REPLICATE('0', 4 - LEN(h.phonenbr)) + CAST(h.phonenbr AS varchar)
So 789 becomes 0789 and 23 becomes 0023.
Phillip
On 6/19/07, Rick Root [EMAIL PROTECTED] wrote:
I'm accessing some mainframe data where phone number parts are stored
as integers. i'm trying to return it as a single formatted number so
I did this:
CAST(H.AREACODE AS VARCHAR)+'.'+CAST(H.PHONEXCH AS
VARCHAR)+'.'+CAST(H.PHONENBR AS VARCHAR) AS PHONE_NUMBER,
which returns 123.456.7890
This is fine as long as the numbers are = 100 or =1000 (for the four
digit part).
If not, I get results like this: 123.456.789 (where the last 4 digits
are actually 0789)
Easy enough to deal with in Coldfusion using numberformat... but I
can't seem to find an equivalent Transact-SQL function, which
surprises me a bit.
Is there a SQL solution for this?
Rick
~|
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:281597
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: Transact-SQL number formatting
I'm accessing some mainframe data where phone number parts are stored as integers. i'm trying to return it as a single formatted number so I did this: CAST(H.AREACODE AS VARCHAR)+'.'+CAST(H.PHONEXCH AS VARCHAR)+'.'+CAST(H.PHONENBR AS VARCHAR) AS PHONE_NUMBER, which returns 123.456.7890 This is fine as long as the numbers are = 100 or =1000 (for the four digit part). If not, I get results like this: 123.456.789 (where the last 4 digits are actually 0789) Easy enough to deal with in Coldfusion using numberformat... but I can't seem to find an equivalent Transact-SQL function, which surprises me a bit. Is there a SQL solution for this? Rick -- CFMBB - Coldfusion Message Boards, Version 1.21 Now Available! http://www.cfmbb.org ~| 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:281552 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Transact-SQL number formatting
Is there a SQL solution for this? http://www.databasejournal.com/features/mssql/article.php/111 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! This email has been processed by SmoothZap - www.smoothwall.net ~| 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:281553 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: Transact-SQL number formatting
T-SQL has a replicate() function that can help. You can pad the last
four digits with zeros using:
REPLICATE('0', 4 - LEN(h.phonenbr)) + CAST(h.phonenbr AS varchar)
So 789 becomes 0789 and 23 becomes 0023.
Phillip
On 6/19/07, Rick Root [EMAIL PROTECTED] wrote:
I'm accessing some mainframe data where phone number parts are stored
as integers. i'm trying to return it as a single formatted number so
I did this:
CAST(H.AREACODE AS VARCHAR)+'.'+CAST(H.PHONEXCH AS
VARCHAR)+'.'+CAST(H.PHONENBR AS VARCHAR) AS PHONE_NUMBER,
which returns 123.456.7890
This is fine as long as the numbers are = 100 or =1000 (for the four
digit part).
If not, I get results like this: 123.456.789 (where the last 4 digits
are actually 0789)
Easy enough to deal with in Coldfusion using numberformat... but I
can't seem to find an equivalent Transact-SQL function, which
surprises me a bit.
Is there a SQL solution for this?
Rick
--
CFMBB - Coldfusion Message Boards, Version 1.21 Now Available!
http://www.cfmbb.org
~|
ColdFusion 8 beta â Build next generation applications today.
Free beta download on Labs
http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281570
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
Can you post the query execution plan? It sounds like you forgot to add an index. I have anice getdistance function that calcs the distance between two zip codes. **snip** However, that is very slow. It's very fast if I pass in the lat and long. ~| 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:268984 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Can you post the query execution plan? It sounds like you forgot to add an index. That or the you're killing the optimizer with the SELECT *; bookmark lookups can kill a DB. I've got my zip code database proximity thing all figured out. I have a nice getdistance function that calcs the distance between two zip codes. It returns a result set containing all the zip codes within 25 miles of 27502. But I can't use that in an IN clause... so what do I do? Rick ~| 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:268987 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Query optimization didn't have anything to do with it. I was running getDistance(zip1,zip2) instead a WHERE IN clause. My getDistance() function, which was based on Russ' would actually do two selects based on the zip code arguments. For example select prospect, name, address, city, state, zip from prospects where zip in ( select zipcode from zipcodes where getDistance(prospects.zip,'27502') 25) Let's say there were 100,000 rows in the prospect table. My original solution would've caused the getDistance() function to be called 100,000 times, and each time it would run two select queries to get the latitude and longitude, and then calc the distance. Effectively running 200,000 little select queries. Indexes weren't going to help =) At any rate, I got everything working super fast and blogged all about it. Rick ~| 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:269029 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: Transact-SQL help
I've got my zip code database proximity thing all figured out. I have a
nice getdistance function that calcs the distance between two zip codes.
syntax of my function: getDistance(zip1,long1,lat1,zip2,long2,lat2)
taking either the zip code or the lat/long for each...
I can now do:
SELECT *
FROM prospects A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
)
However, that is very slow. It's very fast if I pass in the lat and long.
So I've written a stored procedure that when executed looks like this:
sp_zipcodes '27502' 25
It returns a result set containing all the zip codes within 25 miles of
27502.
But I can't use that in an IN clause... so what do I do?
Rick
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
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:268841
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Transact-SQL help
Rick,
Did you take a look at the query that I sent earlier? It takes only 3
seconds to run on my machine.
Russ
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 11:49 AM
To: CF-Talk
Subject: OT: Transact-SQL help
I've got my zip code database proximity thing all figured out. I have a
nice getdistance function that calcs the distance between two zip codes.
syntax of my function: getDistance(zip1,long1,lat1,zip2,long2,lat2)
taking either the zip code or the lat/long for each...
I can now do:
SELECT *
FROM prospects A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
)
However, that is very slow. It's very fast if I pass in the lat and long.
So I've written a stored procedure that when executed looks like this:
sp_zipcodes '27502' 25
It returns a result set containing all the zip codes within 25 miles of
27502.
But I can't use that in an IN clause... so what do I do?
Rick
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
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:268844
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: Transact-SQL help
Rick Root wrote:
syntax of my function: getDistance(zip1,long1,lat1,zip2,long2,lat2)
taking either the zip code or the lat/long for each...
SELECT *
FROM prospects A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
)
This query is not indexable so it needs to do the math on each and every
row. Prequalify the rows by drawing an imaginary box on the map from
b.lat + X to b.lat -X and b.lon + X to b.lon -X and finding only the
points in that box (the database can do that using an index). Then
perform your distance function only on the points in the box.
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:268848
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Russ, if you're referring to this one:
*
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742
*http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742
works fine if you want to pass in the lat/long directly, but I'm trying to
come up with a way to do it off zip code. your function got really slow
when I added functionality inside to do lat/long lookups, because the
function executes for each zip code you're comparing - up to 42000.
However, in attemping to explain all this, I've actually worked out a
solution:
The following query worked pretty well, given that TB907 (the address
table) contains nearly 900,000 records. (Don't blame me for the table name,
blame BSR, it's their product)
DECLARE @long1 decimal(5,2)
DECLARE @lat1 decimal(5,2);
SELECT @long1 = dbo.getlongitude('27502');
SELECT @lat1 = dbo.getLatitude('27502');
SELECT A.*
FROM WEBREPORTS.dbo.TB907 A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) 25
)
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
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:268853
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: Transact-SQL help
On 2/6/07, Jochem van Dieten [EMAIL PROTECTED] wrote:
This query is not indexable so it needs to do the math on each and every
row. Prequalify the rows by drawing an imaginary box on the map from
b.lat + X to b.lat -X and b.lon + X to b.lon -X and finding only the
points in that box (the database can do that using an index). Then
perform your distance function only on the points in the box.
I did this to speed it up originally:
SELECT B.zipcode
FROM zipcodes B
WHERE
B.zipcode between '27002' and '28002'
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
That caused it to only look at 1000 zip codes instead of 42000 zip codes.
the lat/long adjustment would probably be more effective.. or perhaps at
least, more accurate.
Rick
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
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:268854
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Transact-SQL help
No, I'm referring to:
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818
Russ
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 12:42 PM
To: CF-Talk
Subject: Re: Transact-SQL help
Russ, if you're referring to this one:
*
http://www.houseoffusion.com/groups/CF-
Talk/thread.cfm/threadid:50203#268742
*http://www.houseoffusion.com/groups/CF-
Talk/thread.cfm/threadid:50203#268742
works fine if you want to pass in the lat/long directly, but I'm trying to
come up with a way to do it off zip code. your function got really slow
when I added functionality inside to do lat/long lookups, because the
function executes for each zip code you're comparing - up to 42000.
However, in attemping to explain all this, I've actually worked out a
solution:
The following query worked pretty well, given that TB907 (the address
table) contains nearly 900,000 records. (Don't blame me for the table
name,
blame BSR, it's their product)
DECLARE @long1 decimal(5,2)
DECLARE @lat1 decimal(5,2);
SELECT @long1 = dbo.getlongitude('27502');
SELECT @lat1 = dbo.getLatitude('27502');
SELECT A.*
FROM WEBREPORTS.dbo.TB907 A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) 25
)
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
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:268855
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
Russ wrote: No, I'm referring to: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818 That one is pretty quick here... SELECT b.* FROM tblzipcodes a CROSS JOIN tmpzipcodes b WHERE dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 AND a.zip = '27614' Took 1412 ms. I limited the latitude side of things with... SELECT b.* FROM tmpzipcodes a CROSS JOIN tmpzipcodes b WHERE a.zip = '27614' AND b.latitude a.latitude + (10*.014457) AND b.latitude a.latitude - (10*.014457) AND dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 which only took 170ms I think that .014457 is right...basically 1 degree/69.172 miles. ~| 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:268863 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
On 2/6/07, Russ [EMAIL PROTECTED] wrote: No, I'm referring to: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818 Ah, no I hadn't seen that one. I've never used CROSS JOIN before.. interesting. However, my final solution actually does the same thing in about 100ms for a 10 mile radius of 27502. It actually does it in about 1500ms, but I added some code to only look at zip codes whose lat/long were within a certain range, at jochem's suggestion. If I look at latitudes and longitutdes of 2.0 +/-, it improved the query performance from 1542ms to 109ms. Here's my solution - using your original getDistance function. DECLARE @lat1 decimal(5,2); DECLARE @long1 decimal(5,2); DECLARE @zip1 char(5); SET @zip1 = '27502' SELECT @lat1 = LATITUDE from ADSPRD.dbo.zipcodes where zipcode = @zip1; SELECT @long1 = LONGITUDE from ADSPRD.dbo.zipcodes where zipcode = @zip1; SELECT B.CITY, B.STATE, B.zipcode FROM ADSPRD.dbo.zipcodes B WHERE B.LATITUDE BETWEEN @lat1-2.0 and @lat1+2.0 AND B.LONGITUDE BETWEEN @long1-2.0 and @long1+2.0 AND ADSPRD.dbo.getDistance(@lat1,@long1,B.latitude,B.longitude) 10 It's definately not as simple as your cross join solution, but it's the equivalent of lightning fast by comparison. Thanks for everyone's help on this! Rick ~| 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:268870 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
so many solutions, so little time! I like how you're limiting by latitude only and using the radius as well. Doing that actually lowered my execution time to 94ms ... =) On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: Russ wrote: No, I'm referring to: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818 That one is pretty quick here... SELECT b.* FROM tblzipcodes a CROSS JOIN tmpzipcodes b WHERE dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 AND a.zip = '27614' Took 1412 ms. I limited the latitude side of things with... SELECT b.* FROM tmpzipcodes a CROSS JOIN tmpzipcodes b WHERE a.zip = '27614' AND b.latitude a.latitude + (10*.014457) AND b.latitude a.latitude - (10*.014457) AND dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 which only took 170ms I think that .014457 is right...basically 1 degree/69.172 miles. ~| 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:268871 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Rick Root wrote: I like how you're limiting by latitude only and using the radius as well. Thinking about limiting it by longitude made my head hurt...I thought about using some larger constant (like the 2 degrees that you used), but in Alaska, 2 degrees longitude only equates to about 44 miles in some places. Limiting by latitude at least gets the bulk of the comparisons out of the way. ~| 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:268875 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Actually, I did a limitation by longitude as well, because at the equator, the fudge factor is the same (approximatley 69 miles per degree)... everything in the US is less than that but I figured what the heck. So I draw the box on both lat and long, knowing that the longitude will actually encompass MORE than the area I'm looking for... the getDistance() function actually does the work of exact distances anyway. As long as the box is bigger than necessary, it's all good. I was thinking it might actually be wise to use a range factor GREATER than 1/69 ... like 2/69...giving the latitude some wiggle room. But thinking about the math involved as to *WHY* I'd do that makes *MY* head hurt! On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: Rick Root wrote: I like how you're limiting by latitude only and using the radius as well. Thinking about limiting it by longitude made my head hurt...I thought about using some larger constant (like the 2 degrees that you used), but in Alaska, 2 degrees longitude only equates to about 44 miles in some places. Limiting by latitude at least gets the bulk of the comparisons out of the way. ~| 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:268878 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact-SQL help
You can also not think about the math, precompute the distance tables, and then only keep data in there where the distance is x miles. Russ -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 06, 2007 3:10 PM To: CF-Talk Subject: Re: Transact-SQL help Actually, I did a limitation by longitude as well, because at the equator, the fudge factor is the same (approximatley 69 miles per degree)... everything in the US is less than that but I figured what the heck. So I draw the box on both lat and long, knowing that the longitude will actually encompass MORE than the area I'm looking for... the getDistance() function actually does the work of exact distances anyway. As long as the box is bigger than necessary, it's all good. I was thinking it might actually be wise to use a range factor GREATER than 1/69 ... like 2/69...giving the latitude some wiggle room. But thinking about the math involved as to *WHY* I'd do that makes *MY* head hurt! On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: Rick Root wrote: I like how you're limiting by latitude only and using the radius as well. Thinking about limiting it by longitude made my head hurt...I thought about using some larger constant (like the 2 degrees that you used), but in Alaska, 2 degrees longitude only equates to about 44 miles in some places. Limiting by latitude at least gets the bulk of the comparisons out of the way. ~| 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:268882 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
I blogged all this here: http://www.opensourcecf.com/1/2007/02/Determining-Zip-Code-Proximity.cfm ~| 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:268883 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Rick Root wrote: Actually, I did a limitation by longitude as well, because at the equator, the fudge factor is the same (approximatley 69 miles per degree)... everything in the US is less than that but I figured what the heck. So I draw the box on both lat and long, knowing that the longitude will actually encompass MORE than the area I'm looking for... the getDistance() function actually does the work of exact distances anyway. As long as the box is bigger than necessary, it's all good. I was thinking it might actually be wise to use a range factor GREATER than 1/69 ... like 2/69...giving the latitude some wiggle room. But thinking about the math involved as to *WHY* I'd do that makes *MY* head hurt! What calculation did you use to limit the longitude?...if you are using the same one as latitude, I'm not understanding... Let's take a latitude around the center of our home state of NC...35 degrees... At 35 a degree of longitude is approx 57 miles long (per http://www.csgnetwork.com/degreelenllavcalc.html) 1 degree longitude/57 miles = .017544 1 degree latitude/69.172 miles = .014457 ..014457 .017544 so you would be limiting too much of the longitude if you are only using the .014457 number. Are you doing something different? Or do I have it all wrong? ~| 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:268884 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
We are quite lucky in that our Search Engine software has GeoSearch built in :-) it is damn impressive to have it however you get it though, for $5 it's a steal to get the data! This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, 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: Rick Root To: CF-Talk Sent: Tue Feb 06 20:34:34 2007 Subject: Re: Transact-SQL help I blogged all this here: http://www.opensourcecf.com/1/2007/02/Determining-Zip-Code-Proximity.cfm ~| 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:268885 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
Jim, you make an excellent point that I hadn'e considered. However, in my blog entry on the topic, I'm actually doubling the size of the rectangle for the longitude, so I'm doing @lat1-(@[EMAIL PROTECTED]) so even though my range factor technically should be larger, I actually double it anyway, and that compensates for pretty much any different (except maybe in Alaska, I'll have to check that) The factor I'm multiplying by is 0.028985507 Northern alaska would be 0.52ish so I suppose I should actually multiply by 4 (4/69) to compensate. Rick On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: What calculation did you use to limit the longitude?...if you are using the same one as latitude, I'm not understanding... Let's take a latitude around the center of our home state of NC...35 degrees... At 35 a degree of longitude is approx 57 miles long (per http://www.csgnetwork.com/degreelenllavcalc.html) 1 degree longitude/57 miles = .017544 1 degree latitude/69.172 miles = .014457 ..014457 .017544 so you would be limiting too much of the longitude if you are only using the .014457 number. Are you doing something different? Or do I have it all wrong? ~| 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:268933 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Help with Transact SQL
Perhaps I'm missing something here but shouldn't a simple update statement suffice e.g. Update sometable Set pathField = '/path1/path2/rest_of_path' Where pathField = 'http://an_absolute_path/new_path_1/path2/rest_of_path ' Or is there more to it? Kola -Original Message- From: Frank Mamone [mailto:[EMAIL PROTECTED] Sent: 24 September 2003 23:38 To: CF-Talk Subject: Help with Transact SQL Hi, I need help with an update query using transact SQL. I need to modify the data in record set in the following way. The field contains a URL like this: /path1/path2/rest_of_path I need to change it to: http://an_absolute_path/new_path_1/path2/rest_of_path So everything stays the same from path2 on. Thanks in advance for your help. Frank Mamone ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:138422 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
Re: Help with Transact SQL
you can use the following to update all rows at once: update tablename set uri_column = 'http://etc.com/etc' + uri_column - Original Message - From: Frank Mamone [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 1:38 AM Subject: Help with Transact SQL Hi, I need help with an update query using transact SQL. I need to modify the data in record set in the following way. The field contains a URL like this: /path1/path2/rest_of_path I need to change it to: http://an_absolute_path/new_path_1/path2/rest_of_path So everything stays the same from path2 on. Thanks in advance for your help. Frank Mamone ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:138423 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 ColdFusion MX 6.1, now 2.5 times faster. http://www.macromedia.com/software/coldfusion/productinfo/upgrade/jump/introducing.html?trackingid=ColdFusion_468x60g_HouseofFusion_carat_082803
Re: Help with Transact SQL
Thanks for your responses. What I was looking for was the Replace functionwhich I eventually found. I never used functions in SQL before so that's whyI wasn't sure.I apologize if I wasn't totally clear.-Frank- Original Message -From: Mahmut Basaran [EMAIL PROTECTED]To: CF-Talk [EMAIL PROTECTED]Sent: Thursday, September 25, 2003 9:41 AMSubject: Re: Help with Transact SQL you can use the following to update all rows at once: update tablename set uri_column = 'http://etc.com/etc' + uri_column - Original Message - From: Frank Mamone [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 1:38 AM Subject: Help with Transact SQL Hi, I need help with an update query using transact SQL. I need to modify the data in record set in the following way. The field contains a URL like this: /path1/path2/rest_of_path I need to change it to: http://an_absolute_path/new_path_1/path2/rest_of_path So everything stays the same from path2 on. Thanks in advance for your help. Frank Mamone [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Help with Transact SQL
Hi, I need help with an update query using transact SQL. I need to modify the data in record set in the following way. The field contains a URL like this: /path1/path2/rest_of_path I need to change it to: http://an_absolute_path/new_path_1/path2/rest_of_path So everything stays the same from path2 on. Thanks in advance for your help. Frank Mamone ~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:138371 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 ColdFusion MX 6.1, now 2.5 times faster. http://www.macromedia.com/software/coldfusion/productinfo/upgrade/jump/introducing.html?trackingid=ColdFusion_468x60g_HouseofFusion_carat_082803
transact(ms sql) VS PL SQL(Oracle)
Does anyone have a good article or experiences on the differences between ms sql and oracle databases... thanks, --- phumes1 [EMAIL PROTECTED] wrote: I don't know whats happening... The contents of my output.txt file still contains the following. The output to the screen (console) is totally different and this is what I want dumped into the output file. The executable is a compiled Perl script. Does anyone know how to dump the output of the perl script to a file? C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer At 11:19 AM 6/7/2002 +0100, you wrote: C:\temptest.bat output.txt I'd suggest putting a location before the output.txt, something like Test.bat c:\output.txt I'm not sure where CFEXECUTE calls the file from... Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -Original Message- From: phumes1 [mailto:[EMAIL PROTECTED]] Sent: 06 June 2002 18:27 To: CF-Talk Subject: cfexecute Hi, This is what I put in my batch file: test.bat setlocal c: cd C:\temp c:\tempc:\program\runme.exe c:\temp\filename printer Then from the command prompt I did: C:\temptest.bat output.txt The programs run just fine. The program displays the processing to the screen. When I open the file output.txt this is what it contains: C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer I don't know whats going on. The output.txt file should contain the output thats dumped to the screen but it doesn't. The executable file is a Perl script which was compiled to a .exe file. Could there be something in the Perl script thats stopping the output to a file? Its works to the screen so I can't figure out whats happening. Any ideas? The bat file seem to be working exactly as you tell them to. In the instance below, when you are calling the exe directly, you are rerouting the screen output to the p.log file. It is routing the ouput of the runme command to the log file. In the CFExecute example you posted earlier, you are routing the output from the BAT file to the log file. Not quite the same thing. Try changing the call in the BAT file to match your example below, including the redirected output stream. (and what are the command switches -e /q /v doing for you in the CFExecute example? Is /q quiet mode, which supresses all bat file output?) Now you've got me curious Jerry Johnson [EMAIL PROTECTED] 06/05/02 02:27PM Why is my output file empty? The filename is passed to the executable for processing but shen I specify the output to be redirected to a file its empty. Why? Run from command prompt: c:\tempc:\program\runme.exe c:\temp\filename printer p.log +- --+ Philip Humeniuk [EMAIL PROTECTED] [EMAIL PROTECTED] +- ---+ __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact(ms sql) VS PL SQL(Oracle)
What do you want to know? +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: Amanda Stern [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 11, 2002 1:52 PM To: CF-Talk Subject: transact(ms sql) VS PL SQL(Oracle) Does anyone have a good article or experiences on the differences between ms sql and oracle databases... thanks, --- phumes1 [EMAIL PROTECTED] wrote: I don't know whats happening... The contents of my output.txt file still contains the following. The output to the screen (console) is totally different and this is what I want dumped into the output file. The executable is a compiled Perl script. Does anyone know how to dump the output of the perl script to a file? C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer At 11:19 AM 6/7/2002 +0100, you wrote: C:\temptest.bat output.txt I'd suggest putting a location before the output.txt, something like Test.bat c:\output.txt I'm not sure where CFEXECUTE calls the file from... Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -Original Message- From: phumes1 [mailto:[EMAIL PROTECTED]] Sent: 06 June 2002 18:27 To: CF-Talk Subject: cfexecute Hi, This is what I put in my batch file: test.bat setlocal c: cd C:\temp c:\tempc:\program\runme.exe c:\temp\filename printer Then from the command prompt I did: C:\temptest.bat output.txt The programs run just fine. The program displays the processing to the screen. When I open the file output.txt this is what it contains: C:\tempsetlocal C:\tempc: C:\tempcd c:\temp c:\tempc:\program\runme.exe c:\temp\filename printer I don't know whats going on. The output.txt file should contain the output thats dumped to the screen but it doesn't. The executable file is a Perl script which was compiled to a .exe file. Could there be something in the Perl script thats stopping the output to a file? Its works to the screen so I can't figure out whats happening. Any ideas? The bat file seem to be working exactly as you tell them to. In the instance below, when you are calling the exe directly, you are rerouting the screen output to the p.log file. It is routing the ouput of the runme command to the log file. In the CFExecute example you posted earlier, you are routing the output from the BAT file to the log file. Not quite the same thing. Try changing the call in the BAT file to match your example below, including the redirected output stream. (and what are the command switches -e /q /v doing for you in the CFExecute example? Is /q quiet mode, which supresses all bat file output?) Now you've got me curious Jerry Johnson [EMAIL PROTECTED] 06/05/02 02:27PM Why is my output file empty? The filename is passed to the executable for processing but shen I specify the output to be redirected to a file its empty. Why? Run from command prompt: c:\tempc:\program\runme.exe c:\temp\filename printer p.log +- --+ Philip Humeniuk [EMAIL PROTECTED] [EMAIL PROTECTED] +- ---+ __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact-sql datediff function
I hate to say it but the function is working as designed. I ran into the same issue when looking for the number of months between dates. From SQL Server's Books Online: The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1. Basically what you will find is that the number of years difference between the 2 dates is the count of how many times you go past January 1. Each time you pass Jan 1 it adds 1 year. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 2:20 PM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: transact-sql datediff function
to summarize, the following transact-sql code is touted by The Guru's Guide to Transact-SQL as the best solution to return only those employees whose hire date anniversaries fall within the next 30 days: select fname, lname, hire_date from employees where datediff (yy, hire_date, getdate()) + 30) datediff (yy, hire_date, getdate()) but it doesn't work. here are some of your comments about why it doesn't work: Just a guess, but in the datediff you are just extracting the year. we're not really 'extracting' the year; we're trying to return the difference in dateparts (years) between two dates, the startdate and the enddate. If you want to include the month in the calculation, you should probably get the month part too. well you can only enter one datepart. besides, it's implied from the above code that datediff is supposed to compare the entire date and just return the difference of the indicated datepart. but i see where you're going with this and, truth is, when the year datepart is used with this function, it *acts like* it's not evaluating on the entire date. Somebody back me up on this, but I THINK the book has a typo. .. I did some real quick testing, and if you change the yy (years) to dd (days), it works. thanks for taking a look at this so late in the workday, but you might want to test that again. by returning the difference in days, the left side of the expression will always be greater than the right side, so *all* employees are returned. :) probably not a typo; the code correlates to the author's logic, which is clearly spelled out: If the number of years between the hire date and today's date plus 30 days exceeds the number of years between the hire date and today's date, a hire date anniversary must have occurred within those thirty days, regardless of the actual date. My guess would be that it's returning an integer and rounding it up... did you mean it's rounding the result to the nearest integer? actually, that occurred to me; but i didn't understand how your code would prove it. besides, i doubt if it's a rounding issue because the datediff function is supposed to increment the count as it crosses datepart boundaries, according to msdn library's transact sql reference: The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1. I hate to say it but the function is working as designedBasically what you will find is that the number of years difference between the 2 dates is the count of how many times you go past January 1. Each time you pass Jan 1 it adds 1 year. yes. i realized the entire date was *not* being considered by the datediff function to establish a boundary when i tested using 12/31/2001 as a startdate and 1/01/2002 as an end date. the result was 1 for both the yy and mm dateparts. so i would have to conclude that this piece of code is, conceptually, in error. incredible! maybe *i* should write a book. it seems you can say just about anything, and get paid to do it. :) ~ dina __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: transact-sql datediff function
hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact-sql datediff function
Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: transact-sql datediff function
thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact-sql datediff function
I think the only way to check for that a year has passed is to use the day, and determine if it's greater than 365. If DateDiff(dd,myDate,Current_Timestamp) 365 Begin . End -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 4:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: transact-sql datediff function
well, here... i'm a newbie to transact-sql and i have my nose in this book, guru's guide to transact-sql. don't laugh...that's just like me to start with the headscratcher and fill in the gaps as i go. :) anyway, the book goes thru code for possible solutions to the classic problem of finding all employees whose hire date anniversaries fall within the next 30 days. they go on to say that there are a number of 'false' solutions, one of which is filtering the resultset to include only records where the months are the same for both the hire date and today's date. this solution isn't good because is doesn't allow for the fact that the 30 days may span over one, two, or even three months. the other 'false' solution does all this casting and converting to the hire date (which i don't even pretend to understand yet) then checks to see if the result is between today's date and today's date plus 30 (days) by using the getdate() function. but this solution doesn't factor in the leap year exception. now here's what the author says is the 'best' solution but i can't get it to work as expected: select fname, lname, hire_date from employees where datediff (yy, hire_date, getdate()) + 30) datediff (yy, hire_date, getdate()) maybe i'm doing something stupid. does this code work for you? ~ dina - Original Message - From: Ryan Pieszak [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 3:31 PM Subject: RE: transact-sql datediff function I think the only way to check for that a year has passed is to use the day, and determine if it's greater than 365. If DateDiff(dd,myDate,Current_Timestamp) 365 Begin . End -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 4:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact-sql datediff function
Dina... My guess would be that it's returning an integer and rounding it up... Try doing: (datediff(mm, hire_date, getdate())) / 12 This should return a floating number. Then floor it if you want it to always round down as so: Floor((datediff(mm, hire_date, getdate())) / 12) -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 1:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: transact-sql datediff function
Somebody back me up on this, but I THINK the book has a typo. I THINK it should be this: select fname, lname, hire_date from employees where datediff(dd,hire_date,getdate()) + 30) datediff(dd,hire_date,getdate()) I did some real quick testing, and if you change the yy (years) to dd (days), it works. Let me know if it works for you. I'm leaving for the day, I'll check my mail in the morning. Ryan -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 5:16 PM To: CF-Talk Subject: Re: transact-sql datediff function well, here... i'm a newbie to transact-sql and i have my nose in this book, guru's guide to transact-sql. don't laugh...that's just like me to start with the headscratcher and fill in the gaps as i go. :) anyway, the book goes thru code for possible solutions to the classic problem of finding all employees whose hire date anniversaries fall within the next 30 days. they go on to say that there are a number of 'false' solutions, one of which is filtering the resultset to include only records where the months are the same for both the hire date and today's date. this solution isn't good because is doesn't allow for the fact that the 30 days may span over one, two, or even three months. the other 'false' solution does all this casting and converting to the hire date (which i don't even pretend to understand yet) then checks to see if the result is between today's date and today's date plus 30 (days) by using the getdate() function. but this solution doesn't factor in the leap year exception. now here's what the author says is the 'best' solution but i can't get it to work as expected: select fname, lname, hire_date from employees where datediff (yy, hire_date, getdate()) + 30) datediff (yy, hire_date, getdate()) maybe i'm doing something stupid. does this code work for you? ~ dina - Original Message - From: Ryan Pieszak [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 3:31 PM Subject: RE: transact-sql datediff function I think the only way to check for that a year has passed is to use the day, and determine if it's greater than 365. If DateDiff(dd,myDate,Current_Timestamp) 365 Begin . End -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 4:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM To: CF-Talk Subject: OT: transact-sql datediff function hi all, i posted this on the sql list but got no response. i have a test table named employees with the following fields: fname varchar lname varchar hire_date datetime the record i'm querying contains 2001-05-11 00:00:00.000 in the hire_date field. but this query returns 1 rather than the expected 0: select datediff(yy, hire_date, getdate()) as yearsdiff from employees where fname = 'ann' this should give me the number of years between 5/11/2001 and today's date of 5/6/2002, which should be 0 since it's not 5/11 yet. so why am i getting 1??? ~ dina __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Transact SQL book
I seem to recall someone asking on here if anyone knew of a definitive Transact SQL reference book. I've stumbled across this one at Amazon called The Gurtu's guide to Transact SQL by Kenneth Henderson - anyone read it? http://www.amazon.co.uk/exec/obidos/ASIN/0201615762/qid=990451170/sr=1-29/20 2-5900567-1244606 -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Transact SQL book
Yes. It's excellent, but I wouldn't call it a definitive Transact SQL reference book. It's more of an advanced SQL techniques book. It won't teach you the basics, but it will give you some really neat ideas if you have gotten past the basics. Bob -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, May 21, 2001 9:27 AM To: CF-Talk Subject: Transact SQL book I seem to recall someone asking on here if anyone knew of a definitive Transact SQL reference book. I've stumbled across this one at Amazon called The Gurtu's guide to Transact SQL by Kenneth Henderson - anyone read it? http://www.amazon.co.uk/exec/obidos/ASIN/0201615762/qid=990451170/sr=1-29/20 2-5900567-1244606 -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Transact SQL book
Cool thanks for the feedback Bob. I've come across numerous SQL Server books in the past but most have only briefly touched on Transact SQL. I've always used the books online and the SQL help files to solve any issues I've had over the years but it's good to know there is a decent book touching on advanced techniques..off to Amazon then Oh by the way...whilst I'm on the topic of books. Anyone passed the Microsoft exam 70-029 (Designing and Implementing SQL Server 7)? If so what study book did you use. I've been looking at the Readiness Review from Microsoft and the MCSE Fast Track: SQL Server 7 Database Design. Anyone have any views? -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Bob Silverberg [mailto:[EMAIL PROTECTED]] Sent: 21 May 2001 14:45 To: CF-Talk Subject: RE: Transact SQL book Yes. It's excellent, but I wouldn't call it a definitive Transact SQL reference book. It's more of an advanced SQL techniques book. It won't teach you the basics, but it will give you some really neat ideas if you have gotten past the basics. Bob -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, May 21, 2001 9:27 AM To: CF-Talk Subject: Transact SQL book I seem to recall someone asking on here if anyone knew of a definitive Transact SQL reference book. I've stumbled across this one at Amazon called The Gurtu's guide to Transact SQL by Kenneth Henderson - anyone read it? http://www.amazon.co.uk/exec/obidos/ASIN/0201615762/qid=990451170/sr=1-29/20 2-5900567-1244606 -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Difference between CF calling the transact vs. SQL?
Is there a difference between using CFTransaction and using "begin transaction" in a regular CFQuery? Lon Lentz Applications Developer CyberEntomologist - Alvion Technologies DataWarehousing and List Sales - Market Your Lists on the Net! [EMAIL PROTECTED] 941-574-8600 Ext. 210 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Transact
Hy! What sort of a sodtware can we buy to make a transaction. We know for OpenMarket and their Transact. Are there any others (links, info, price...)? Bye, Iztok -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
