amazing problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: amazing problem

  1. #1
    Join Date
    Sep 2006
    Posts
    7

    amazing problem

    Hi Gurus,

    I am sgoogling and askTom for quite a while and I cannot find a good solution for my kind of problem.

    I have a table with a couple of rows, sometimes more sometimes less, the table will be created from a csv file using sqlloader.

    My problem is:
    PHP Code:
    from this table
    __________________________________________________________________________________________
    |       id    |    mon1        |    mon2    |    mon3    |    mon4    |    mon5    |
    |
    _________________________________________________________________________________________
    |    1    |    1        |    2    |    5    |    2    |    1    |
    |
    _________________________________________________________________________________________
    |    2    |    1        |    2    |    5    |    7    |    2    |
    |
    _________________________________________________________________________________________



    --> 
    all values should inserted into a new table: (these are routes from 1 point to another)

    |
    Aid  |   mob1_id   |   mob2_id       |
    |
    _____________________________|
    1    |      1       |       2              |
    ______________________________|
    1    |      2        |       5             |
    _______________________________
    1    |       5       |       2             |
    |
    _____________________________|
    |  
    1   |       2       |        1            |
    |
    _____________________________
    in words: just split each row in each colum and but it in the new table.


    I have already written a lot of code but nothing works..
    Maybe one of you had a similar problem.

    Help or a tipp would really be great...I am getting mad...


    THX a lot.
    Petra.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,479

    Cool


    Try this:
    PHP Code:
    Select idmon1mon2 From MyTable
    UNION
    Select id
    mon2mon3 From MyTable
    UNION
    Select id
    mon3mon4 From MyTable
    UNION
    Select id
    mon4mon5 From MyTable
    Order by 1
    ,2,3

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    That's amazing all right
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by LKBrwn_DBA

    Try this:
    PHP Code:
    Select idmon1mon2 From MyTable
    UNION
    Select id
    mon2mon3 From MyTable
    UNION
    Select id
    mon3mon4 From MyTable
    UNION
    Select id
    mon4mon5 From MyTable
    Order by 1
    ,2,3

    UNION ALL, surely?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2006
    Posts
    7
    Hello,

    thanks for your hint, but it's not working the way I need it.
    because the amount of values in the table1 is not always the same...sometimes there are 3, 5, or 7 values or more in a single row...


    --------
    Table1:
    --------
    1 mon1 mon2 mon5 mon2 mon1
    2 mon1 mon2 mon3 mon4 mon6 mon1 mon2 mon1
    3 mon1 mon5 mon1



    ---------
    Table2: |
    ----------
    1 mon1 mon2
    1 mon2 mon5
    1 mon5 mon2
    1 mon2 mon1

    2 mon1 mon2
    2 mon2 mon3
    2 mon3 mon4
    2 mon4 mon6
    2 mon6 mon2
    2 mon2 mon1



    then the union command does not work...

    any other suggestions...
    Thx...
    Petra.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    no amount of programming will overcome a bad design.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Sep 2006
    Posts
    7
    Oh, thanks for that answer, but this is not based on a BAD Design.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by petzi74
    then the union command does not work...
    why not?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Sep 2006
    Posts
    7
    a row contains sometimes 4, sometimes 5, or 3, 6,7,8 values.

    As mentioned above this is not because of a bad design.
    A short explanation:
    these values are just objects of different routes - some routes have only 3 monizoring objects, another 4, or 5...etc.
    As soon as 1 route failed - every 10 minutes a token is send - the result is the route where the token has been failed.

    e.g. mon1 --> mon2 --> mon5 --> mon1


    Petra.

  10. #10
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,479

    Cool


    Ok try this one (assuming there are no gaps (nulls) in the middle of route):
    PHP Code:
    Select IdMon1Mon2 From Mytable 
    Union
    Select Id
    Mon2Mon3 From Mytable
     Where Mon3 Is Not Null
    Union
    Select Id
    Mon3Mon4 From Mytable 
     Where Mon4 Is Not Null
    Union
    Select Id
    Mon4Mon5 From Mytable 
     Where Mon5 Is Not Null
     Order By 1
    ,2,3


    PS: Union all will make no difference unless routes are duplicate.


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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


Click Here to Expand Forum to Full Width