cancel
Showing results for 
Search instead for 
Did you mean: 

Query doesnt work well for "Customer"

Former Member
0 Kudos

Dear All,

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)

Set @A=$[OCRD.Cardtype]

SELECT Top(1)

Case

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

end

from OCRD T0

Thanks in advance,

Joseph Antony

View Entire Topic
former_member204969
Active Contributor
0 Kudos

I suggest this one for partner code generation. (Sudau2019s SQL may have problem with generating code for leads.)

If $[OCRD.CardType]='S'
Select 'VE'+isnull(right(str(max(substring(c.CardCode,2,10))+1000000001),5),'00001')
  From OCRD c
  Where c.CardCode like 'VE[0-9][0-9][0-9][0-9][0-9]'
Else 
Select'CU'+isnull(right(str(max(substring(c.CardCode,2,10))+1000000001),5),'00001')
  From OCRD c
  Where c.CardCode like 'CU[0-9][0-9][0-9][0-9][0-9]'

It generate 5 digits, you can change it to less or more.

former_member583013
Active Contributor
0 Kudos

Istvan,

I would like to learn SQL from you....

Could you please tell me how my SQL would not work for LEADS?

Thank you

Suda

Former Member
0 Kudos

Hi Istvan,

Your query is working fine for the customer and vendor, but when i select card type as lead, its still generating the code for the customer. Can you please help by correcting this part too.?

Thanks,

Joseph