samedi 23 juillet 2016

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

My column's   example 
          (panelname) as varchar(50)
              o01
              o02
              o16

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

WITH cte AS(SELECT panelname FROM inventorypanelcaptions UNION ALL SELECT 0) 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 cast(right(inventorypanelcaptions.panelname, 2) as varchar(50))= cast(right(cte.panelname, 2) as varchar(50))+1) ", con);

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

example my columns in panelname -> Result will be o03
             1     or   101    
             2          102
             16         116

But when it contain letter

 example my columns in panelname 
             o01         
             o02
             o016

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

Aucun commentaire:

Enregistrer un commentaire