Brent had a query exercise recently about train stations moving in some order and having overrides to this order in emergencies. I suppose he’s been traveling a lot lately and ran into an issue.
In any case, I decided to see if Claude could solve his challenge.
This is part of a series of experiments with AI systems.
The Prompt
I put this into Claude, mostly copy/paste from Brent’s challenge.
I have a sql table of stations in a physical order. Here is the table and data for stations DROP TABLE IF EXISTS dbo.Stations; CREATE TABLE dbo.Stations (StationId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StationName VARCHAR(50), StationPhysicalOrder INT); INSERT INTO dbo.Stations (StationName, StationPhysicalOrder) SELECT CHAR(64 + n), n FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11), (12),(13),(14),(15),(16),(17),(18),(19) ) AS Numbers(n);
I also have an override table, which contains its own data for emergencies. Here is this code: Transact-SQL 1 2 3 4 5 6 7 8 DROP TABLE IF EXISTS dbo.StationRoutingOverride; CREATE TABLE dbo.StationRoutingOverride (StationRoutingOverrideId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StationFromName VARCHAR(50), StationToName VARCHAR(50)); INSERT INTO dbo.StationRoutingOverride (StationFromName, StationToName) VALUES (‘E’, ‘S’), (‘B’, ‘I’), (‘I’, ‘D’);
write a query to return a result set that: * Starts with the station with the lowest StationPhysicalOrder * For each row, if there’s a row in StationRoutingOverride to dictate the next step, jump to that station * Otherwise, go to the station with the next StationPhysicalOrder
The Answer
Here’s the code:
WITH StationRouting AS ( -- Base case: Start with the station having the lowest StationPhysicalOrder SELECT s.StationId, s.StationName, s.StationPhysicalOrder, 1 as StepNumber FROM dbo.Stations s WHERE s.StationPhysicalOrder = (SELECT MIN(StationPhysicalOrder) FROM dbo.Stations) UNION ALL -- Recursive case: Find the next station SELECT next_station.StationId, next_station.StationName, next_station.StationPhysicalOrder, sr.StepNumber + 1 FROM StationRouting sr CROSS APPLY ( SELECT TOP 1 s.StationId, s.StationName, s.StationPhysicalOrder FROM dbo.Stations s WHERE s.StationName = COALESCE( -- First priority: Check if there's an override for current station (SELECT sro.StationToName FROM dbo.StationRoutingOverride sro WHERE sro.StationFromName = sr.StationName), -- Second priority: Next station by physical order (SELECT TOP 1 s2.StationName FROM dbo.Stations s2 WHERE s2.StationPhysicalOrder > sr.StationPhysicalOrder ORDER BY s2.StationPhysicalOrder) ) ) next_station WHERE next_station.StationName IS NOT NULL ) SELECT StepNumber, StationId, StationName, StationPhysicalOrder FROM StationRouting ORDER BY StepNumber;
That doesn’t work, so I added to the prompt:
I got a similar error again with the group by in the CTE and pasted that in. The query got rewritten (Again) and this time it ran with this result, which matches Brent’s answer.
GenAIs make mistakes, but they can also fix things. I wondered how well this would work, but it worked well. I merely told the model it had made an error and it tried again.
Imagine if the AI could connect to a db, run its code, and adjust from the error messages itself? The higher end coding tools are supposed to do this.
Voice of the DBA 