Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb Unanswered: common table expression

    im curious to know what the below syntax does. Since this is a commomtable expression, which is new to me .

    with mytable (temp ) as (values repeat('splendid',3))
    select substr(temp || rtrim(coalesce(str,' ')),length(rtrim(coalesce(str,' ')))+1,len) from mytable

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by krReddy
    im curious to know what the below syntax does. Since this is a commomtable expression, which is new to me .

    with mytable (temp ) as (values repeat('splendid',3))
    select substr(temp || rtrim(coalesce(str,' ')),length(rtrim(coalesce(str,' ')))+1,len) from mytable
    A CTE can be seen as a (one-time) view. So the above is conceptually equivalent to:
    Code:
    CREATE VIEW mytable AS
    SELECT 'splendidsplendidsplendid' AS temp FROM sysibm.sysdummy1 ;
    select substr(temp || rtrim(coalesce(str,' ')),
                  length(rtrim(coalesce(str,' ')))+1,
                  len)
    from   mytable
    but without making the view visual in the catalog.

    (This sould give an error at "str" and at "len", I believe.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •