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

    Lightbulb Unanswered: common table expression (With statement)

    Hi,

    Im having the following code in DB2 , I want the equivalent code in Oracle.
    i suppose there is no direct equivalent for REPEAT function in Oracle.
    I tried using RPAD function.

    DB2 code:

    with temptab (name) as (values repeat('genome',5))
    select * from temptab

    Oracle:

    with temptab (name) as (rpad('genome',5*length('genome'),'genome')
    select * from temptab


    Is this the correct equivalent, Please suggest me

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Not really sure what the "correct equivalent" should be since I am not familiar with DB2... it would help if you specify what you would like the query to return.

    However, I "think" you might be looking for something like this:

    Code:
    WITH myTable AS 
       (SELECT 'genome' col1 FROM dual CONNECT BY LEVEL <=5)
    SELECT * FROM myTable;
    This outputs the following:
    Code:
    COL1
    -------
    genome
    genome
    genome
    genome
    genome
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this:
    Code:
    with temptab as 
    (select rpad('genome',5*length('genome'),'genome') as name
      from dual)
    select * from temptab;
    The result is:

    NAME
    ------------------------------
    genomegenomegenomegenomegenome

    (If you don't yet know it, DUAL is a special one-row system table that exists just to enable you to select single values like this.)

Posting Permissions

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