It is currently a bit of a drag to use bitwise logic in SQL9. The result of every bitwise operator is returned as FLOAT64, regardless of the input types. So for example even CAST(1 AS UINT8) BITOR CAST(2 AS UINT8) returns float64: 3 Here's an example, packing several pieces of data into one UINT32 value. (The objective is to group large amounts of data on a single field, which is given an atomic index. It does prove faster than grouping by multiple fields with a composite index.) SELECT CAST( CAST( -- bits 0..15: positive elevation CAST(ABS([elevation]) AS UINT32) -- mask bits 16..31 pro forma BITAND CAST(2^16 - 1 AS UINT32) AS UINT32 ) BITOR -- bit 16: sign CAST( CASE WHEN [elevation] < 0 THEN 2^16 ELSE 0 -- positive or zero END AS UINT32 ) BITOR -- bit 17: island CAST( CASE WHEN [island] = 1 THEN 2^17 -- north ELSE 0 -- south END AS UINT32 ) BITOR -- bit 18: indefinite CAST( CASE WHEN [definition] = 'indefinite' THEN 2^18 ELSE 0 END AS UINT32 ) BITOR -- bit 19: supplementary CAST( CASE WHEN [designated] = 'supplementary' THEN 2^19 ELSE 0 END AS UINT32 ) BITOR -- bit 20: depression CAST( CASE WHEN [nat_form] = 'depression' THEN 2^20 ELSE 0 END AS UINT32 ) BITOR -- bit 21: closed CAST( CASE WHEN GeomCoordXY([Geom], 0) = GeomCoordXY([Geom], GeomCoordCount([Geom]) - 1) THEN 2^21 ELSE 0 END AS UINT32 ) -- bits 22..31 unused AS UINT32 ) AS [contour code] It's really nice having the bitwise operators, but using them feels clumsy. Would it not be possible for SQL9 functions to use the narrowest input type for their output? (Also it would be nice to have shift operators.) I could use a script function. Maybe that would be better. (Though I think the questions above would remain.)
|