i have this query for automatic creation of BP code which is used as FMS at the BP bode in BP master data. However, this query works fine when the cardtype is "supplier" but once it is changed to customer, it does not generate any code! Can anyone please correct this query and let me know why is this query not working for the customer?
Declare @A Varchar(10)
when @A='s' then 'VE' +
cast((select max(cast(substring(( T0.CardCode ),3,3) as int))+1 from OCRD T0 where cardtype='s')as varchar(10))
--when @A='c' or @A='l'
--then 'CU' +
--cast((select max(cast(substring(( T0.CardCode ),3,5) as int))+1 from OCRD T0 where cardtype='c'or cardtype='l')as varchar(10))
from OCRD T0
Thanks in advance,