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