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
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
