The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

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.