Results 1 to 6 of 6

Thread: take out union

  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Unanswered: take out union

    Hello,
    I would like to take out the following "union" and just do 1 select in the table instead of 6,
    is it possible ?

    select field1 as FIELD_OUT from table
    union
    select field2 as FIELD_OUT from table
    union
    select field3 as FIELD_OUT from table
    union
    select field4 as FIELD_OUT from table
    union
    select field5 as FIELD_OUT from table
    union
    select field6 as FIELD_OUT from table

    Thank You -

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello,

    try this:
    Code:
    with a(i) as (
    values 1
      union all
    select i+1
    from a
    where i<6)
    select 
    case a.i
      when 1 then t.tabschema
      when 2 then t.tabname
      when 3 then t.owner
      when 4 then t.ownertype
      when 5 then t.type
      when 6 then t.status
    end 
    from syscat.tables t, a
    where t.tabschema='SYSCAT' and t.tabname='TABLES'
    Regards,
    Mark.

  3. #3
    Join Date
    Mar 2015
    Posts
    31
    I think It is not really what I want, let me put a better example ...

    select owner AS person from schema.LIST_PERSON_ITEM
    union all
    select user AS person from schema.LIST_PERSON_ITEM

    with this sentence we do 2 full scan of the table schema.LIST_PERSON_ITEM , I would like to do just one
    thank you -

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    select owner,user from schema.LIST_PERSON_ITEM
    because there is no detailed description, we can not guess what you need,want..
    be more specific.. add small example if possible
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Have you tried to look at the access plan of the query below?
    Do you see 2 table scans of the "schema.LIST_PERSON_ITEM" table?
    If yes, please show this access plan here.
    Code:
    select 
    case a.i
    when 1 then t.owner
    when 2 then t.user
    end 
    from schema.LIST_PERSON_ITEM t, table (values 1,2) a(i)
    Regards,
    Mark.

  6. #6
    Join Date
    Mar 2015
    Posts
    31
    yes it worked , thank you

Posting Permissions

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