how to create function with extra parameters
The Oracle function is this i need to create same in vertica CREATE FUNCTION "GET_CHECK_DIGIT" (p_varBCode IN VARCHAR2) RETURN VARCHAR2 AS in_bcode VARCHAR2(20 BYTE); ZBCODE NUMBER; mbcode VARCHAR2(20); m_expcode VARCHAR2(20 BYTE); OldBcode VARCHAR2(20 BYTE); v_Out_BCode VARCHAR2(20 BYTE); Y INTEGER; T_SUM INTEGER; chkdigit NUMBER; digit NUMBER; T_REM INTEGER; BEGIN in_bcode := p_varBCode; IF in_bcode <= 9999 THEN v_Out_BCode := '0'; END IF; IF LPAD(in_bcode,1) <> '0' THEN zbcode := TO_NUMBER(LPAD(in_bcode,12)); END IF; IF zbcode >= 100000 THEN mbcode := CAST(zbcode AS CHAR); CASE WHEN SUBSTR(mbcode,6,1) = '0' THEN m_expcode := SUBSTR(mbcode,1,2)||'00000'||SUBSTR(mbcode,3,3); WHEN SUBSTR(mbcode,6,1) = '1' THEN m_expcode := SUBSTR(mbcode,1,2)||'10000'||SUBSTR(mbcode,3,3); WHEN SUBSTR(mbcode,6,1) = '2' THEN m_expcode := SUBSTR(mbcode,1,2)||'20000'||SUBSTR(mbcode,3,3); WHEN SUBSTR(mbcode,6,1) = '3' THEN m_expcode := SUBSTR(mbcode,1,3)||'00000'||SUBSTR(mbcode,4,2); WHEN SUBSTR(mbcode,6,1) = '4' THEN m_expcode := SUBSTR(mbcode,1,4)||'00000'||SUBSTR(mbcode,5,1); WHEN SUBSTR(mbcode,6,1) = '5' THEN m_expcode := SUBSTR(mbcode,1,5)||'00005'; WHEN SUBSTR(mbcode,6,1) = '6' THEN m_expcode := SUBSTR(mbcode,1,5)||'00006'; WHEN SUBSTR(mbcode,6,1) = '7' THEN m_expcode := SUBSTR(mbcode,1,5)||'00007'; WHEN SUBSTR(mbcode,6,1) = '8' THEN m_expcode := SUBSTR(mbcode,1,5)||'00008'; WHEN SUBSTR(mbcode,6,1) = '9' THEN m_expcode := SUBSTR(mbcode,1,5)||'00009'; END CASE; zbcode := CAST(m_expcode AS NUMBER); END IF; OldBcode := TO_CHAR(mbcode,'FM000000000000'); Y := 1; T_SUM := 0; WHILE Y <= 12 LOOP IF MOD(Y,2) = 0 THEN digit := TO_NUMBER(SUBSTR(OldBcode,Y,1)) * 3; ELSE digit := TO_NUMBER(SUBSTR(OldBcode,Y,1)) * 1; END IF; T_SUM := T_SUM + digit; Y := Y + 1; END LOOP; T_REM := 0; T_REM := MOD(T_SUM,10); chkdigit := 0; IF T_REM <> 0 THEN chkdigit := 10 - T_REM; ELSE chkdigit := 0; END IF; v_Out_BCode := OldBcode||chkdigit; RETURN v_Out_BCode; END;
0
Comments
Hi,
Currently Vertica does not support the variable declaration in functions like oracle does. Very simple functions can be written in vertica.
You can use the UDF for complex functions, they can be written in C++ or Java or any other programming langauge, please go through the below link
https://my.vertica.com/docs/6.1.x/HTML/index.htm#15713.htm
Thanks & Regards,
Shobhit