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

Thread: Query Problem

  1. #1
    Join Date
    May 2001
    Posts
    25

    Unhappy

    Hi friends..

    I have to execute one query for following requirement but unable to write query can anybody guide...


    Table format
    ---------------
    empno
    deptno
    salary

    required format
    ------------------

    Deptno sal[0-5000] sal[5000-10000] sal[10000-20000]

    10 20 30 15
    20 40 19 10


    How to get above information through the above table.


    thanks in advance...

    sandeep

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by ctcyl
    10 20 30 15
    20 40 19 10
    I have no idea what these numbers are supposed to represent, so that makes it a little difficult to write the actual query for you. Basically, if you are using >= 8.1.6.2, then you want to look at the new CASE statement. If not, then you either need a lot of DECODEs, a formula to calculate your buckets, or a separate table containing the buckets (ranges).

    HTH

    - Chris

  3. #3
    Join Date
    May 2001
    Posts
    31
    select dept_no,
    sum(decode(sign(salary-5001),-1,1)) "0-5000",
    sum(decode(sign(salary-5000),1,
    decode(sign(salary-10001),-1,1))) "5001-10000",
    sum(decode(sign(salary-10000),1,
    decode(sign(salary-20001),-1,1))) "10001-20000"
    from your_table_name_here

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, just as a curiosity, you could get the desired output here even without using DECODE or CASE function. All you need is a litle gymnastics with the SIGN function:

    select dept_no,
    sum(sign(sign(salary)+sign(5000-salary))) "0-5000",
    sum(sign(sign(salary-5001)+sign(10000-salary))) "5001-10000",
    sum(sign(sign(salary-10001)+sign(20000-salary))) "10001-20000",
    from your_table_name_here
    group by deptno;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Jurij - you've been reading Celko too much, I think

    He had an entire 'sub-language' developed to handle all these types of things before CASE became part of the SQL standard. (for others - DECODE is the poor relative of CASE). Very scary stuff

    Point to ctcyl: These queries essentially do COUNTs by bucket. Is that what you wanted? Or did you want something else, like the sum of the salaries within each bucket. I'm still not sure what you were asking for.

    - Chris

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by chrisrlong
    Jurij - you've been reading Celko too much, I think

    He had an entire 'sub-language' developed to handle all these types of things before CASE became part of the SQL standard. (for others - DECODE is the poor relative of CASE). Very scary stuff
    Chris,

    Tthe fact is I havent read any of Celco's articles for at least three years now . Not that I don't like him, my current employer is simply not subscribed to any magazine with his regular columns.

    But it looks like I've had the same "obsession" as he had in the past, inventing various "sub-language constructs" for implementing IF-THEN-ELSE SQL logic that is now soooo easy to implement with CASE.

    In fact I discovered that you don't even need DECODE (or CASE) in this example purely by incident while I was writing a reply to cheland's solution, where I imediatelly spoted he is using a couple of DECODES too many. Typical and unnecessary using of nested DECODEs that most Oracle users do, because it is not documented anywhere in the documentation how to code IF-ELSIF without nesting the DECODEs.

    For example, his "BETWEEN 5001 AND 10000" implementation with nested DECODEs:

    SUM(DECODE(SIGN(salary-5000), 1, DECODE(SIGN(salary-10001), -1, 1))) "5001-10000"

    could be rewritten into a more efficient, single DECODE construct:

    SUM(DECODE(1, SIGN(5001-salary), NULL,SIGN(10001-salary), 1)) "5001-10000"

    Of course, I would not want to "decipher" the meaning of any of this various sollution implementations (nested decodes, single decode, sign-sign-sign,....) after a month or so - it would probably caused me a terrible headacke. While on the other hand, with CASE it would only take a glimpse to understand it....

    [Edited by jmodic on 05-08-2001 at 06:31 PM]
    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