Options

Wierd Behavior with Semi-Recursive SQL Function

mark_d_drakemark_d_drake Community Edition User ✭
edited June 2022 in General Discussion

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.

Answers

  • Options
    mark_d_drakemark_d_drake Community Edition User ✭

    Created a non-recursive solution. Not fully tested yet.

    */
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_SECONDS(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when  INSTR(ISO8601_VALUE,'S') > 0 then
          SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'S')-1) || ' Seconds' 
        else
          ''
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_MINUTES(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when INSTR(ISO8601_VALUE,'M') > 0 then
          SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'M')-1) || ' Minutes ' || YADAMU.PARSE_ISO8601_SECONDS(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1))         
        else
          YADAMU.PARSE_ISO8601_SECONDS(ISO8601_VALUE)
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_HOURS(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when INSTR(ISO8601_VALUE,'H') > 0 then
          SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'H')-1) || ' Hours ' || YADAMU.PARSE_ISO8601_MINUTES(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'H')+1))       
        else
          YADAMU.PARSE_ISO8601_MINUTES(ISO8601_VALUE)
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_TIME(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when INSTR(ISO8601_VALUE,'T') > 0 then
          YADAMU.PARSE_ISO8601_HOURS(SUBSTR(ISO8601_VALUE,2))
        else 
          ''
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_DAYS(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when INSTR(ISO8601_VALUE,'D') > 0 then
          SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'D')-1) || ' Days ' || YADAMU.PARSE_ISO8601_TIME(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'D')+1))       
        else
          YADAMU.PARSE_ISO8601_TIME(ISO8601_VALUE)
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_WEEKS(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when INSTR(ISO8601_VALUE,'W') > 0 then
          SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'W')-1) || ' Weeks ' || YADAMU.PARSE_ISO8601_DAYS(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'W')+1))          
        else
          YADAMU.PARSE_ISO8601_DAYS(ISO8601_VALUE)
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_MONTHS(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        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')-1) || ' Months ' || YADAMU.PARSE_ISO8601_WEEKS(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'M')+1))        
        else
          YADAMU.PARSE_ISO8601_WEEKS(ISO8601_VALUE)
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_YEARS(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return
     case 
        when INSTR(ISO8601_VALUE,'Y') > 0 then
          SUBSTR(ISO8601_VALUE,1,INSTR(ISO8601_VALUE,'Y')-1) || ' Years ' || YADAMU.PARSE_ISO8601_MONTHS(SUBSTR(ISO8601_VALUE,INSTR(ISO8601_VALUE,'Y')+1))        
        else
          YADAMU.PARSE_ISO8601_MONTHS(ISO8601_VALUE)
     end;
end;
--
CREATE OR REPLACE FUNCTION YADAMU.PARSE_ISO8601_INTERVAL(ISO8601_VALUE VARCHAR) 
return VARCHAR
as
BEGIN
   return YADAMU.PARSE_ISO8601_YEARS(SUBSTR(ISO8601_VALUE,2));
end;
--

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...