Stored procedureSQL

I have a subquery in a stored procedure.  Which was supposed to generate a code
If the group name is equal to 250 then my output supposed to 3784 250
if my group name is 2 digit eg: P6 or 02 then my output supposed to 3784 0P6
but my issue is if my group name is equal more than 2 characters it is generating as 3784 0250, which i dont want.
It is supposed to generate as 3784 250.  Can someone help me out with this please.

(Select '3784 0'+ t5.GroupName + ' '
      + SubString('0000000',1,7-Len(T1.U_TenantID))
      + T1.U_TenantID + ' '
      + SubString('00',1,2-Len(DAY(T0.DocDate)))
      + Cast(DAY(T0.DocDate) as Varchar(2))
      + SubString('00',1,2-Len(MONTH(T0.DocDate)))
      + Cast(MONTH(T0.DocDate) as Varchar(2))
      + CAST(YEAR(T0.DocDate) as Varchar(4))
      + ' '
      + SubString('000000000',1,9-Len(Cast(Cast((CAST(T1.Balance as Int)) as Int) As Varchar(10))
      + SubString('00',1,2-Len(Cast(Cast((T1.Balance - (CAST(T1.Balance as Int)))*100 as Int) as Varchar(2))))
      + Cast(Cast((T1.Balance - (CAST(T1.Balance as Int)))*100 as Int) as Varchar(2))))
      + Cast(Cast((CAST(T1.Balance as Int)) as Int) As Varchar(10))
      + SubString('00',1,2-Len(Cast(Cast((T1.Balance - (CAST(T1.Balance as Int)))*100 as Int) as Varchar(2))))
      + Cast(Cast((T1.Balance - (CAST(T1.Balance as Int)))*100 as Int) as Varchar(2))) as 'ScanLine'
romeiovasuAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
(Select '3784 '+ RIGHT('0' + t5.GroupName, 3) + ' '
...rest same as before...
0
 
dsackerContract ERP Admin/ConsultantCommented:
Try changing:

    '3784 0'+ t5.GroupName + ' '

To:

    '3784 ' + RIGHT('000' + t5.GroupName, 3) + ' '

Does that help?
0
 
Salah Eddine ELMRABETTechnical Lead Manager (Owner)Commented:
Hi,

You code can't do that because of you specified the "0" in the text added to the result statically!

You need to define a variable and include the "0" if the result is 2 digit and ignore it if it is 3 digit.

I cannot identify the action as result since your post contains only select.

Please provide more details in order to understand.

Best Regards.

Salah
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.