Results 1 to 9 of 9

Thread: WITH Clause

  1. #1
    Join Date
    Mar 2004
    Posts
    46

    Unanswered: WITH Clause

    Hi Guys,

    Can you plese help me translate a simple DB2 queries to Oracle 10G.
    ---
    with allhours (hh) as (values(0),(1),(2),(3),(4),(5))
    select hh
    from allhours
    ---
    I need to write a database setup check routine. I will specify the objects in the values.. and join it to user_objects table to check successfull setup of build

    Thanks in advance

    Prashant

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could do this:
    Code:
    with allhours (hh) as (select rownum-1 from all_objects where rownum <= 6)
    select hh
    from allhours

  3. #3
    Join Date
    Mar 2004
    Posts
    46
    Quote Originally Posted by andrewst
    You could do this:
    Code:
    with allhours (hh) as (select rownum-1 from all_objects where rownum <= 6)
    select hh
    from allhours
    Thanks Andrewst,

    Actually this is not what I am looking for. Let me rephrase my question.
    ==========
    with AllColors (Color) as (values('Red'),('Blue'),('Green'),('Purple'),('Yel low'))
    select Color
    from AllColors
    ==========

    Prashant

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select Color
    from AllColors
    WHERE color in ('Red','Blue','Green','Purple','Yellow');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2004
    Posts
    46
    Friends, you are not getting the question.

    "AllColors" table is not a physical table, it is created by WITH clause.

    Thanks
    Prashant

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I knew that. Try this:
    Code:
    with allcolors as 
    (select decode(rownum,1,'Red',2,'Blue',3,'Green',4,'Purple',5,'Yellow') as color from all_objects where rownum <= 5)
    select color
    from allColors;
    Here I am using an arbitrary existing view (or table) all_objects merely as a way of generating numbers.

    NB Oracle doesn't allow you to specify the column list like you had it:

    with allcolors (color) as ... -- Not allowed

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by prashant_bharti
    I need to write a database setup check routine. I will specify the objects in the values.. and join it to user_objects table to check successfull setup of build
    Why don't you just
    Code:
    select * from user_objects where object_name in ('red','blue','whatever',...)

  8. #8
    Join Date
    Mar 2004
    Posts
    46
    Thanks Tony, it is helpful.

    Thanks n_i, but how ops guy will know that a particular object is not created. I am writing just one sql, which will generate a following report:

    Name Type Installed
    ===== ===== =======
    emp TABLE Y
    idx_emp1 INDEX X
    emp_seq SEQUENCE Y
    .....
    ....

    Thanks

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You actually want to check the existence of particular database objects? Tony just used ALL_OBJECTS as a simple way of generating some rows from SQL. If you want to check those objects against a list, try an outer join.

Posting Permissions

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