The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file