user6615498 user6615498 - 4 months ago 6
SQL Question

Why my code doesn't work? (using multi expressions + cast + case)

My column's example:

(name) as varchar(50)
s01
s02
s16


I want to Select first missing panelname above 0 also with using case and cast(in my example desired select will be 's03')

WITH cte AS(
SELECT name
FROM customers
UNION ALL
SELECT 0
)

SELECT CASE WHEN cast(min(right(name, 2) + 1) as varchar(50)) < 10 THEN 's0' ELSE 's' END +
cast(min(right(name, 2) + 1) as varchar(50))
FROM cte
WHERE NOT EXISTS (
SELECT name
FROM customers
WHERE cast(right(customers.name, 2) as varchar(50)) = cast(right(cte.name, 2) as varchar(50))+1)


This code works but only in case when my table is null or table contains only numbers.

Example my columns in name-> Result will be s03

1 or 101
2 102
16 116


But when it contain letter

example my columns in name
s01
s02
s016


It doesnt work. I get an error : Conversion failed when converting the varchar value 's01' to data type int. Should I put somewhere else cast to varchar?

Answer

(Posted on behalf of the OP).

It was need just to convert the union select.

    WITH cte AS(
    SELECT name 
    FROM customers 
    UNION ALL 
    SELECT CAST(0 AS VARCHAR(50)) 
    FROM INVENTORYPANELCAPTIONS
) 

SELECT  CASE WHEN  cast(min(right(name, 2) + 1) as varchar(50)) < 10 THEN 's0' ELSE 's' END 
        + cast(min(right(name, 2) + 1) as varchar(50)) 
FROM cte 
WHERE NOT EXISTS ( 
    SELECT name 
    FROM customers 
    WHERE right(customers.name, 2)= right(cte.name, 2)+1)