Nested Query in ORACLE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Nested Query in ORACLE

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Unhappy Nested Query in ORACLE

    Nested query is kicking my, well I won't say.

    Here is the SQL: explanation of what I am trying to do follows the SQL:

    select a.warehouse,
    count(1) NUM_LINES, (select count(1) as zero_bal
    FROM inventory b where b.warehouse='ACE' and
    b.Q_RO > 0 and b.q_oh=0)
    FROM inventory a WHERE a.warehouse='ACE'
    and a.q_RO > 0 GROUP BY a.warehouse;

    What I am trying (very uncessfully) is to get counts from a single table where
    different conditions are met. In this eaample, I need to get acount of lines
    in warehouse ACE where (Q_RO > 0) and a count of all the zero balance or Quanity on hand = 0. (Q_OH=0)

    I am expecting something like

    WAREHOUSE NUM_LINES ZERO_BAL
    ----------------------------------
    ACE 960 27


    I keep getting a not a group by Expression like below.

    count(1) NUM_LINES, (select count(1) as zero_bal
    *
    ERROR at line 2:
    ORA-00979: not a GROUP BY expression

    Can anyone help a feller out??

  2. #2
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    maybe this is what you are looking for

    SELECT warehouse,
    COUNT(*) NUM_LINES,
    (SELECT COUNT(1) OVER( PARTITION BY warehouse )
    FROM inventory
    WHERE warehouse='ACE'
    AND q_RO > 0
    AND b.q_oh=0) zero_bal
    FROM inventory
    WHERE warehouse='ACE'
    AND q_RO > 0
    Andrej

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Never go into a table more than once if you don't have to.

    Code:
    SELECT
       WAREHOUSE,
       COUNT(*)
          AS NUM_LINES, 
       SUM(
          CASE
             WHEN
                B.Q_OH   =  0
             THEN
                1
          END
          )
          AS ZERO_BAL
    FROM
       INVENTORY
    WHERE
       WAREHOUSE  =  :WAREHOUSE AND
       Q_RO       >  0 
    GROUP BY
       WAREHOUSE   ;
    - Chris
    Last edited by chrisrlong; 04-09-2003 at 06:27 PM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Aug 2001
    Posts
    267
    Select count(*) will go for full table scan. If it is very huge table it may take more time. Is rewriting SQL with select count(1) is better or ??
    Raghu

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Man, but this one never dies....

    COUNT(*) is the accepted standard and there is ABSOLUTELY NO DIFFERENCE IN PERFORMANCE OR PLAN between COUNT(1) and COUNT(*).

    Period.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Nov 2002
    Posts
    80
    I'm having a similar problem. This works fine in 9i (accessing 8i through a database link) but not in 8i.

    code:

    select
    a.row_owner,
    max(b.full_name),
    count(1) total_rows,
    max(c.period_total) "6 MONTH UPDATES",
    max(d.period_total) "12 MONTH UPDATES"
    from
    accounts a,
    row_owner_table b,
    (select count(owner_id) period_total, owner_id from accounts where last_update > ( sysdate - 180) group by owner_id ) c,
    (select count(prd_ow_i) period_total, owner_id from accounts where last_update > ( sysdate - 360) group by owner_id ) d
    where
    b.id = a.row_owner and
    d.owner_id (+) = a.owner_id and
    c.owner_id (+) = a.owner_id
    group by a.owner_id order by count(1) desc;



    I managed to rewrite it using decode()/greater()/sum() but now I have to do another query where I need to use 2 group by's in a subquery.

    (see also http://saloon.javaranch.com/cgi-bin/...&f=55&t=000417)

    Anybody got some other suggestions on this type of query?

    Simon

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Question about the solution you posted on the other forum...

    - Why are you doing max(A.location)? Is this table simply not designed correctly?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Well, in any case, the solution you posted there was much better than the original anyway. And here is an even better version:
    Code:
    SELECT
       A.USER_ID             ID        ,
       ROT.FULL_NAME         FULL_NAME ,
       A.LOCATION            LOCATION  ,
       A.PAST_WEEK           WEEK      ,
       A.PAST_MONTH          MONTH     ,
       A.PAST_QUARTER        QUARTER   ,
       A.PAST_YEAR           YEAR      ,
       A.TOTAL_DAYS          TOTAL
    FROM
       (
       SELECT
          USER_ID              ,
          MAX(LOCATION)
             AS   LOCATION     ,
          COUNT(*)
             AS   TOTAL_DAYS   ,
          SUM(
             CASE WHEN LAST_UPDATE >= SYSDATE   -   7            THEN 1 END
             )
             AS   PAST_WEEK    ,
          SUM(
             CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE,  -1)   THEN 1 END
             )
             AS   PAST_MONTH   ,
          SUM(
             CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE,  -3)   THEN 1 END
             )
             AS   PAST_QUARTER ,
          SUM(
             CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE, -12)   THEN 1 END
             )
             AS   PAST_YEAR
       FROM
          ACCOUNTS
       WHERE
          LAST_UPDATE >= ADD_MONTHS(SYSDATE, -12)
       GROUP   BY
          USER_ID
       )                  A   ,
       ROW_OWNER_TABLE   ROT
    WHERE
       ROT.USER_ID   =   A.USER_ID
    ORDER   BY
       PAST_WEEK   ,
       PAST_MONTH
    This version will only hit the Accounts table once, much like your second statement, but I made further changes:

    - DECODE -> CASE
    ...- CASE is more readable
    ...- CASE is faster
    - Moved GROUP BY
    ...- I waited until after the GROUP BY to join to the ROT table, saving time
    - Added WHERE clause
    ...- Since you are only interested in records from the past year, this predicate will make it faster.
    - Used ADD_MONTHS, which is more precise, based on your final names. You called the field MONTH, but it was actually the past 30 days, which is not always a month.

    You should still look into that MAX(LOCATION) thing

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by raghud
    Select count(*) will go for full table scan. If it is very huge table it may take more time. Is rewriting SQL with select count(1) is better or ??
    tell us why count(1) would go faster?

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