asked
statement Problem
case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end I have seen above query in Job sheduling Procedure used in our server. but i didn't understand this statement "freq_interval & 2 when 2". Please suggest why and how we use this kind of statement
That is a [bitwise and][1] operation. It really depends what freq_interval is. It will probably be someone storing information in a "clever" way to save space, allowing them to evaluate many different values against a single column. This sort of programming saves on resources, but I haven't seen it since working with clipper and dbase where tricks like this were used out of memory and space constraints. I believe that this is something that programmers don't necessarily learn nowadays, and *may* be classed as premature optimisation of code. [1]:
http://msdn.microsoft.com/en-us/library/ms174965.aspx
+1, but I wouldn't say there was too much premature about it. I use bit fields quite often - but I am quite anal about optimisation. For example, say there is a table with 24 'flags' - I would store those in a bit field, simply because bringing them back into the application layer then requires 1 column, not 24, and 1 call to `SqlDataReader.GetInt32()`, rather than 24 `.GetBoolean()` calls. The retrieved value will map quite nicely onto an enumeration that is decorated with the `[Flags]` attribute...
+1 for that. Though I kinda like the construction, I use it to store permission bits because it's easily handled from inside an application. When in an administration module I save permissions ticked in checkboxes, I don't need to check if they should first be deleted, I can save all permissions in one statement and so on. Of course it looses on flexibility, but it's often worth it. Weekdays don't really need flexibility, they won't change often :)
@WilliamD - stuff like that always amazes me. I don't understand how people can do programming without understanding bits. Understanding your car makes you a better driver - same goes with computers and programming, for me at least.
I find it easiest to imagine the column as a binary string ie a series of 1's and 0's like '10011' and the & n is testing the relevant bit in the binary to see if it is 1 or 0. As you know the columns in a binary number increase by a factor or two as the columns are read right to left. EG: we have a value of 19, this equates to 10011 in binary. if we `SELECT 19 & 1` we get 1 as a result - the 1 column is switched if we `SELECT 19 & 2` we get 2 as a result - the 2 column is switched if we `SELECT 19 & 4` we get 0 as a result - the 4 column is NOT switched if we `SELECT 19 & 8` we get 0 as a result - the 8 column is NOT switched if we `SELECT 19 & 16` we get 16 as a result - the 16 column is switched Like this we can save a record of whether there is a true or false value for many different business uses in a single column. Imagine : 1 = staff member has a car 2 = staff member has a balloon 4 = staff member has a liking of soft cheese 8 = staff member has a blue front door 16 = staff member has been on Christmas lunch with a value of 19 we can see that the staff member has been on the lunch, owns a balloon and a car. If we find out they like soft cheese we simply add 4 to their record, making it 23 and therefore 10111, effectively switching the 4 bit.
