Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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;

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.