Programming

13372 readers

1 users here now

All things programming and coding related. Subcommunity of Technology.

This community's icon was made by Aaron Schneider, under the CC-BY-NC-SA 4.0 license.

founded 1 year ago

MODERATORS

[email protected]

What's your favorite construct in SQL? (beehaw.org)

submitted 1 year ago by [email protected] to c/[email protected]

12 comments fedilink hide all child comments

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?

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 2 points 1 year ago* (last edited 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, no need to know what Id that is or for anyone else to in the future either.