CODEWITHSUNDEEP
sqlsql-server-2005
user6615498
user6615498

Reputation: 29

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?

Upvotes: 0

Views: 81

Answers (2)

halfer
halfer

Reputation: 20487

(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)

Upvotes: 1

Ante T.
Ante T.

Reputation: 1

You're comparing string (varchar) with int values, so try to cast varchar numbers to int instead of varchar before you're comparing them with int values or doing some operations with them

Maybe something like this (not sure if this will work, but just wrote it as example how the solution could look):

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

Upvotes: 0

Related Questions