For me, it’s CTE’s. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.

What construct do you like using on a daily basis?

  • itty53@vlemmy.net
    link
    fedilink
    arrow-up
    2
    ·
    edit-2
    1 year ago

    You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.

    Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.

    Example

    CREATE VIEW AS enum.OrderType
    SELECT 
    CAST (1 as 'New'),
    CAST (2 as 'Pending'),
    CAST (3 as 'Shipped')
    GO
    
    -- Assuming a table with OrderId and OrderTypeId
    
    SELECT              o.OrderId 
    FROM                dbo.Orders AS o
    CROSS JOIN          enum.OrderType AS ot
    WHERE               o.OrderTypeId = ot.[Pending]
    
    -- Only returns orders where TypeId = 2
    
    • megaman1970@beehaw.orgOP
      link
      fedilink
      arrow-up
      1
      ·
      1 year ago

      Hey, that’s pretty neat. Perhaps you could wrap a the OrderType view in a CTE and join on that to get the values back?