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
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
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
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
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
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
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
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
-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
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
-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
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
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
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
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
-
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
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
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
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,
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
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
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
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,
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
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.
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
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
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 *
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
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
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
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
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
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 =
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
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:
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
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
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
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
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
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
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,
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
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
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
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
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
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
, 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
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
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
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
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
, 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
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
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
), 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
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
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
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
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
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
63 matches
Mail list logo
