DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    This is just a part of a query, the actual query has a join and some other stuffs.

    SQLWKS> select work_id, work_level from work
    2> where work_level in(0,1)
    3> order by work_level asc
    4>
    WORK_ID WORK_LEVEL
    ---------- ----------
    737 0
    734 0
    826 0
    1466 0
    1544 0
    2667 0
    2220 0
    1543 0
    1234 0
    1 1
    2 1

    What I want to do is I want to select work_level in certain order and do not want to get rest of the records.
    The order is say work_level = 1, 0, 2, 5, 7, 9 (which is fixed but not serial) so I cannot use GREATEST or LEAST functions.

    In the above example it should display only records with work_level = 1 and not =0.
    Suppose there are no records with work_level = 1 then should go for work_level = 0 , if not then go for work_level = 2 if not then go for work_level = 5.
    So it is a OR clause but with specific order and the processing should stop after it finds the match for the 1 st in the sequence.
    Does any one know a query which will do this ??

    Thanks
    Sonali

    Sonali

  2. #2
    Join Date
    Jan 2001
    Posts
    515

    DECODE

    I am not sure but if you were to use the decode like this:

    select work_id, work_level from work
    where work_level = DECODE(work_level,1,1,DECODE(work_level,0,0,DECODE(work_level,2,2,DECODE(
    work_level,5,5,DECODE(work_level,7,7,9)))));

    Let me know what happens.

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Thanks, I had tried using DECODE too.
    YOur query also does not work, it displays all !
    here is part of the result set:
    WORK_ID WORK_LEVEL
    ---------- ----------
    1 1
    35 2
    60 2
    747 5
    141 2
    737 0
    734 0
    236 2
    256 2
    547 5
    553 2
    542 5
    543 5
    544 5
    546 5
    607 2
    705 5
    721 5
    664 2
    667 5
    668 5
    669 5
    672 5
    697 2
    698 5
    803 2
    805 2
    820 5
    826 0
    6101 5
    6102 7
    6103 5
    6124 5
    6125 5
    6127 5
    6141 5
    6149 7
    6150 7
    6151 7
    6152 7
    6153 7
    6163 5
    6803 5
    6808 5
    6816 5
    6822 5
    7090 5
    7096 2
    7349 5
    7350 5
    ..................
    9460 5
    9480 2
    9520 5
    9561 9
    9563 5
    9565 7
    9580 5
    9581 5
    9601 5
    9641 7
    9661 5
    9663 7
    9720 2
    9721 5
    9780 2
    9824 2
    9840 2
    1120 rows selected.
    Sonali

  4. #4
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking use this

    This is probably not efficient, but it gets the results you want.

    select work_id, work_level from work
    where work_level = 1
    UNION
    select work_id, work_level from work
    where work_level = 0
    UNION
    select work_id, work_level from work
    where work_level = 2
    UNION
    select work_id, work_level from work
    where work_level = 5
    UNION
    select work_id, work_level from work
    where work_level = 7
    UNION
    select work_id, work_level from work
    where work_level = 9;

    - Magnus

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sonaliak,

    This will probably get you what you want (provided you are using 8i):

    SELECT work_id, work_level FROM work
    WHERE work_level =
    (SELECT work_level FROM
    (SELECT work_level FROM work
    ORDER BY DECODE(work_id, 1,1, 0,2, 2,3, 5,4, 7,5, 9,6)
    );

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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