SQL 2000 INCORECT SYNTAX NEAR WITH

Question

I'm using this code:

WITH cte AS(SELECT panelname FROM inventorypanelcaptions UNION ALL SELECT CAST(0 AS VARCHAR(50)) FROM INVENTORYPANELCAPTIONS) SELECT CASE WHEN  cast(min(right(panelname, 2) + 1) as varchar(50)) < 10 THEN 'o0' ELSE 'o' END + cast(min(right(panelname, 2) + 1) as varchar(50)) FROM cte WHERE NOT EXISTS ( SELECT panelname FROM inventorypanelcaptions WHERE right(inventorypanelcaptions.panelname, 2)= right(cte.panelname, 2)+1) ", con);

I'm getting incorrect syntax near 'With'

Answer 1

To make it work in SQL 2000, you can move it to a subquery

SELECT CASE WHEN  cast(min(right(panelname, 2) + 1) as varchar(50)) < 10 THEN 'o0' ELSE 'o' END + cast(min(right(panelname, 2) + 1) as varchar(50)) 
    FROM (
        SELECT panelname FROM inventorypanelcaptions UNION ALL SELECT CAST(0 AS VARCHAR(50)) 
        FROM INVENTORYPANELCAPTIONS
    ) cte 
    WHERE NOT EXISTS (  SELECT panelname 
                        FROM inventorypanelcaptions 
                        WHERE right(inventorypanelcaptions.panelname, 2)= right(cte.panelname, 2)+1
                     ) ", con);