How to read this code from DTS package 2

[Catadmin]

I have a Dynamic Properties Task in a SQL Server 2000 DTS package (someone else designed it, now I'm upgrading it to SSIS). It has a list of global variables and when I double click the Value on one, it comes up with a new popup window I've never seen before, "Add/Edit Assignment". This ADA window has Source, Connection and Query. In query, it has the weirdest thing...

SELECT	SUM(~A.CancelFlag * A.Premium) AS 'Premium'
FROM Table1 A 
INNER JOIN Table2 B
ON A.VPID = B.VPID

Does anyone know what's going on with the ~ thing? I've never seen this before in T-SQL.

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Catadmin-

I've never seen this before either. I just looked at some of my packages, and discovered the add/edit assignment you talked about (I usually assign global variables through VB or as output parameters from queries, then use dynamic properties task to change source files, etc... per the global variable). It doesn't look like it's really anything special, just using the query to set the value of a global variable (basically the same thing as using output parameter from a SQL Task).

I did some playing around with the tilde in Query analyzer, and it appears that the tilde operator takes an integer, adds one to it, then takes the inverse of that.

For example, select ~1 will give you -2. This could be a setting thing though, so try this query and see what you get.

select 3, ~3, 300, ~300, (-40), ~(-40)
select 1000, ~1000, 63, ~63, 0, ~0

It appears to only work with integers.

I can't figure out what the use of this would be, except that it returns the value you need to add to an integer to get to negative one. Why this is useful I am not sure.

Your query, if you run it in query analyzer, should return the sum of premiums multiplied by (-1), at least if this is not something that is dependent on system settings.

Let me know how this turns out for you.

Alex






Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

OK, When I refreshed to make sure nobody had posted an answer it reverted to what I had typed before making some changes?

Final bit should read like this:

Your query, if you run it in query analyzer, should return the sum of (-(premium +1) * 100), at least if this is not something that is dependent on system settings.

Let me know how this turns out for you.

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[earthandfire]

It actually seems to flip the bits of (or NOT) the number ie a "ones-complement"


Using a byte (8 bits) as an example:
[tt]
1 = 00000001
~1 = 11111110 = 254 = -2
[/tt]


Hope this helps.

 

[AlexCuse]

The operator is 'not' (based on the error I got when I tried to run it on text), so that makes sense. I wonder why I can't find much documentation on this feature?

Have you figured anything out yet Catadmin?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[earthandfire]

AlexCuse, after reading your post 20 Oct 06 14:19, I checked BOL and found:

~ (Bitwise NOT)
Performs a bitwise logical NOT operation for one given integer value as translated to binary expressions within Transact-SQL statements.

Syntax
~ expression

Arguments
expression

Is any valid Microsoft® SQL Server™ expression of any of the data types of the integer data type category, or of the binary or varbinary data type. expression is an integer that is treated and transformed into a binary number for the bitwise operation.

Result Types
Returns an int if the input values are int, a smallint if the input values are smallint, a tinyint if the input values are tinyint, or a bit if the input values are bit.

Remarks
The bitwise ~ operator performs a bitwise logical NOT for the expression, taking each corresponding bit. The bits in the result are set to 1 if one bit (for the current bit being resolved) in expression has a value of 0; otherwise, the bit in the result is cleared to a value of 1.

The ~ bitwise operator can be used only on columns of the integer data type category.



Important When performing any kind of bitwise operation, the storage length of the expression used in the bitwise operation is important. It is recommended that you use the same number of bytes when storing values. For example, storing the decimal value of 5 as a tinyint, smallint, or int produces a value stored with different numbers of bytes. tinyint stores data using 1 byte, smallint stores data using 2 bytes, and int stores data using 4 bytes. Therefore, performing a bitwise operation on an int decimal value can produce different results as compared to a direct binary or hexidecimal translation, especially when the ~ (bitwise NOT) operator is used. The bitwise NOT operation may occur on a variable of a shorter length that, when converted to a longer data type variable, may not have the bits in the upper 8 bits set to the expected value. It is recommended that you convert the smaller data type variable to the larger data type, and then perform the NOT operation on the result.


Examples
This example creates a table with int data types to show the values, and puts the table into one row.

USE master
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'bitwise')
DROP TABLE bitwise
GO
CREATE TABLE bitwise
(
a_int_value tinyint NOT NULL,
b_int_value tinyint NOT NULL
)
GO
INSERT bitwise VALUES (170, 75)
GO

This query performs the bitwise NOT on the a_int_value and b_int_value columns.

USE MASTER
GO
SELECT ~ a_int_value, ~ b_int_value
FROM bitwise

Here is the result set:

--- ---
85 180

(1 row(s) affected)

The binary representation of 170 (a_int_value or A, below) is 0000 0000 1010 1010. Performing the bitwise NOT operation on this value produces the binary result 0000 0000 0101 0101, which is decimal 85.

(~A)
0000 0000 1010 1010
-------------------
0000 0000 0101 0101


See Also

Expressions

Operators (Bitwise Operators)

©1988-2000 Microsoft Corporation. All Rights Reserved.

... which confirms my earlier findings with QA testing - that this is a "ones complement" or binary NOT operator as opposed to negation which is the more usual "twos complement".



Hope this helps.

 

[AlexCuse]

Thanks EarthAndFire, after I posted I actually decided to check the BOL again and found the same thing. My problem was that I typed 'tilde' rather than just placing the symbol in the search window... Thanks for the clarification.

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Catadmin]

Guys, sorry it took so long to respond. I've been on vacation for a few days.

Thank you both for all the time you've given to this thread. Your information helps me immensely. Now I just need to figure out why the developer created this the way he did and I'm all set to finish my conversion.

Stars for both of you because I just ran out of cookies. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"