Results 1 to 4 of 4

Thread: distinct values

  1. #1
    Join Date
    Apr 2011
    Posts
    31

    Red face Unanswered: distinct values

    My query returns the following output :


    INDEX_NAME Schema Name Table Name Column Name

    BOOK DEMO5 TBK FBKID
    BOOK PRU00N TBK FBKID
    BOOK CPB004 TBK FBKID

    BOOKTY DEMO5 TBKTYP FBKTYPID
    BOOKTY EVG004 TBKTYP FBKTYPID
    BOOKTY PIMCO5 TBKTYP FBKTYPID
    BOOKTYP PRU00N TBKTYP FBKTYPID

    BOOK_FU DEMO4 TBKCON FBKINID
    BOOK_FU BOAP04 TBKCON FCONID
    BOOK_FU COL004 TBKCON FBOOKID
    BOOK_FU DI0004 TBKCON FBOOKID

    The BOOK index is in 3 schemas. I want to display it only once for every schema and the other data remains the same.
    Similarly for the other indexes also.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand your required result.
    (Same about your previous post "Sysindexes & syskeys".)

    Please show your required result from the sample data.
    INDEX_NAME Schema Name Table Name Column Name

    BOOK DEMO5 TBK FBKID
    BOOK PRU00N TBK FBKID
    BOOK CPB004 TBK FBKID

    BOOKTY DEMO5 TBKTYP FBKTYPID
    BOOKTY EVG004 TBKTYP FBKTYPID
    BOOKTY PIMCO5 TBKTYP FBKTYPID
    BOOKTYP PRU00N TBKTYP FBKTYPID

    BOOK_FU DEMO4 TBKCON FBKINID
    BOOK_FU BOAP04 TBKCON FCONID
    BOOK_FU COL004 TBKCON FBOOKID
    BOOK_FU DI0004 TBKCON FBOOKID

  3. #3
    Join Date
    Apr 2011
    Posts
    31

    Sql guidance

    Desired Out put : and its Explanation : Indexes exist in different schema's. I want to show distinct indexes from all schemas. In the 3rd index BOOK_FU we see that different columns are used on same index in different schemas. We want to synchronize all schemas so that they contain indexes which are based on same columns and delete the rest. So after getting the result set from the index BOOK_FU we may further decide that the index on the FCONID column is ok and so we remove the others.

    Desired Output :

    IX_NAME Sch Name Tb Name Col Name

    BOOK DEMO5 TBK FBKID
    PRU00N
    CPB004

    BOOKTY DEMO5 TBKTYP FBKTYPID
    EVG004
    PIMCO5
    PRU00N

    BOOK_FU DEMO4 TBKCON FBKINID
    BOAP04 TBKCON FCONID
    COL004 TBKCON FBOOKID

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm sure you will be able to achieve this using one of the analytical functions, say, ROW_NUMBER(), and a CASE expression.

Posting Permissions

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