SSCC label function - SAP HANA

[john_obrien7]

Hi all,

I located the following tSQL function to generate SSCC label number including check digit (for ASN).

I have not yet been able to convert this function to SAP SQLScript using the documentation available.

Has anyone done anything similar using SAP HANA?

My HANA version is 1.00.74.03.392810.

Thanks,

John

CREATE FUNCTION dbo.fnGetSSCC

(

       @SSCC VARCHAR(17)

)

RETURNS VARCHAR(18)

AS

BEGIN

       DECLARE @Index TINYINT,

              @Multiplier TINYINT,

              @Sum SMALLINT

       SELECT @Index = LEN(@SSCC), @Multiplier = 3, @Sum = 0

       WHILE @Index > 0

              SELECT

                     @Sum = @Sum + @Multiplier * CAST(SUBSTRING(@SSCC, @Index, 1) AS TINYINT),

                     @Multiplier = 4 - @Multiplier,

                     @Index = @Index - 1

       RETURN CASE @Sum % 10

                     WHEN 0 THEN @SSCC + '0'

                     ELSE @SSCC + CAST(10 - @Sum % 10 AS CHAR(1))

       END

END

[john_obrien7]

I resolved this by doing a procedure from scratch based on GS1 SSCC spec doc.

Prob not the most elegant, but it works ..

create procedure full_sscc

language sqlscript as

sscc nvarchar(17);

iSum int;

begin

  select '39nnnnnn' || right('000000' || cast("U_SSCC" as integer), 9)

     into sscc

     from "@SSCC";

  select

  (substring (:sscc,1,1) * 3) +

  (substring (:sscc,2,1) * 1) +

  (substring (:sscc,3,1) * 3) +

  (substring (:sscc,4,1) * 1) +

  (substring (:sscc,5,1) * 3) +

  (substring (:sscc,6,1) * 1) +

  (substring (:sscc,7,1) * 3) +

  (substring (:sscc,8,1) * 1) +

  (substring (:sscc,9,1) * 3) +

  (substring (:sscc,10,1) * 1) +

  (substring (:sscc,11,1) * 3) +

  (substring (:sscc,12,1) * 1) +

  (substring (:sscc,13,1) * 3) +

  (substring (:sscc,14,1) * 1) +

  (substring (:sscc,15,1) * 3) +

  (substring (:sscc,16,1) * 1) +

  (substring (:sscc,17,1) * 3)

  into iSum from DUMMY;

  select '00' || sscc || cast(10 - mod (iSum, 10) as char(1)) from DUMMY;

end;