We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Wierd Behavior with Semi-Recursive SQL Function — Vertica Forum

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
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 ...