Wierd Behavior with Semi-Recursive SQL Function
mark_d_drake
Community Edition User ✭
Try to convert an IS08601 Interval into something vertical can understand.
So far I have the following function
-- CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_INTERVAL(ISO8601_VALUE VARCHAR) return VARCHAR as BEGIN return CASE when INSTR(ISO8601_VALUE,'P') = 1 then YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,2)) when INSTR(ISO8601_VALUE,'Y') > 0 then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'Y')-1) || ' Years ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'Y')+1)) when (((INSTR(ISO8601_VALUE,'M') > 0) and (INSTR(ISO8601_VALUE,'T') = 0)) or (INSTR(ISO8601_VALUE,'M') < INSTR(ISO8601_VALUE,'T'))) then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')) || 'Months ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1)) when INSTR(ISO8601_VALUE,'W') > 0 then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'W')) || 'Weeks ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'W')+1)) when INSTR(ISO8601_VALUE,'D') > 0 then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'D')) || 'Days ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'D')+1)) when INSTR(ISO8601_VALUE,'T') > 0 then YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,2)) when INSTR(ISO8601_VALUE,'H') > 0 then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'H')) || 'Hours ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'H')+1)) when INSTR(ISO8601_VALUE,'M') > 0 then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')) || 'Minutes ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1)) when INSTR(ISO8601_VALUE,'S') > 0 then SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'S')) || 'Seconds ' else '' end; END;
However it appears you can't create a recursive function directly in Vertica.. So I tried this..
C:\Development\YADAMU>vsql -UdbAdmin -hyadamu-db2 -d VMart Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See vsql reference page "Notes for Windows users" for details. VMart=> drop FUNCTION YADAMU.PARSE_ISO8601_INTERVAL(VARCHAR); DROP FUNCTION VMart=> CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_INTERVAL(ISO8601_VALUE VARCHAR) VMart-> return VARCHAR VMart-> as VMart-> begin VMart-> return 'YADAMU.PARSE_ISO8601_INTERVAL'; VMart-> end; CREATE FUNCTION VMart=> -- VMart=> CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_INTERVAL(ISO8601_VALUE VARCHAR) VMart-> return VARCHAR VMart-> as VMart-> BEGIN VMart-> return VMart-> CASE VMart-> when INSTR(ISO8601_VALUE,'P') = 1 then VMart-> YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,2)) VMart-> when INSTR(ISO8601_VALUE,'Y') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'Y')-1) || ' Years ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'Y')+1)) VMart-> when (((INSTR(ISO8601_VALUE,'M') > 0) and (INSTR(ISO8601_VALUE,'T') = 0)) or (INSTR(ISO8601_VALUE,'M') < INSTR(ISO8601_VALUE,'T'))) then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')) || 'Months ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1)) VMart-> when INSTR(ISO8601_VALUE,'W') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'W')) || 'Weeks ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'W')+1)) VMart-> when INSTR(ISO8601_VALUE,'D') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'D')) || 'Days ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'D')+1)) VMart-> when INSTR(ISO8601_VALUE,'T') > 0 then VMart-> YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,2)) VMart-> when INSTR(ISO8601_VALUE,'H') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'H')) || 'Hours ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'H')+1)) VMart-> when INSTR(ISO8601_VALUE,'M') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')) || 'Minutes ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1)) VMart-> when INSTR(ISO8601_VALUE,'S') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'S')) || 'Seconds ' VMart-> else VMart-> '' VMart-> end; VMart-> END; CREATE FUNCTION VMart=> VMart=> select YADAMU.PARSE_ISO8601_INTERVAL('P5Y'); PARSE_ISO8601_INTERVAL ------------------------------- YADAMU.PARSE_ISO8601_INTERVAL (1 row) VMart=> qquit VMart-> quit VMart-> \q C:\Development\YADAMU>vsql -UdbAdmin -hyadamu-db2 -d VMart Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See vsql reference page "Notes for Windows users" for details. VMart=> select YADAMU.PARSE_ISO8601_INTERVAL('P5Y'); PARSE_ISO8601_INTERVAL ------------------------------- YADAMU.PARSE_ISO8601_INTERVAL (1 row) VMart=> -- VMart=> CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_INTERVAL(ISO8601_VALUE VARCHAR) VMart-> return VARCHAR VMart-> as VMart-> BEGIN VMart-> return VMart-> CASE VMart-> when INSTR(ISO8601_VALUE,'P') = 1 then VMart-> YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,2)) VMart-> when INSTR(ISO8601_VALUE,'Y') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'Y')-1) || ' Years ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'Y')+1)) VMart-> when (((INSTR(ISO8601_VALUE,'M') > 0) and (INSTR(ISO8601_VALUE,'T') = 0)) or (INSTR(ISO8601_VALUE,'M') < INSTR(ISO8601_VALUE,'T'))) then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')) || 'Months ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1)) VMart-> when INSTR(ISO8601_VALUE,'W') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'W')) || 'Weeks ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'W')+1)) VMart-> when INSTR(ISO8601_VALUE,'D') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'D')) || 'Days ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'D')+1)) VMart-> when INSTR(ISO8601_VALUE,'T') > 0 then VMart-> YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,2)) VMart-> when INSTR(ISO8601_VALUE,'H') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'H')) || 'Hours ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'H')+1)) VMart-> when INSTR(ISO8601_VALUE,'M') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')) || 'Minutes ' || YADAMU.PARSE_ISO8601_INTERVAL(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1)) VMart-> when INSTR(ISO8601_VALUE,'S') > 0 then VMart-> SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'S')) || 'Seconds ' VMart-> else VMart-> '' VMart-> end; VMart-> END; CREATE FUNCTION
Which appears to allow to to create the function. However, when I execute the function it appears to be combining the original version of the function with the new version
VMart=> select YADAMU.PARSE_ISO8601_INTERVAL('P5Y'); PARSE_ISO8601_INTERVAL --------------------------------------- 5 Years YADAMU.PARSE_ISO8601_INTERVAL (1 row) VMart=>
Any ideas.
0
Answers
Created a non-recursive solution. Not fully tested yet.