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] 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. :
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.