Solved

Oracle query

Posted on 2014-12-17
13
191 Views
Last Modified: 2014-12-19
I am trying to get duplicates in the query. I do have the query.

select
unit_number,u.chk_digit, item_id,
count(*)
from invoice_detail id, invoice_header ih, units@plab.world u
where id.invoice_number = ih.invoice_number
and id.unit_number = u.unit_id
and debit_credit = 'DB'
and unit_number is not null
and id.unit_number in ('W039713779596','W039714958552')
and invoice_date between '01-jan-2014' and trunc(sysdate-1)
group by
unit_number,u.chk_digit, item_id
having count(*) > 1
order by 3, 4

But I am looking for a query to get duplicates of all debits where count of credits is greater than count of debits for the unit number and item id(product code) combination.


select count(debit_credit)
from invoice_detail
where unit_number in ('W039714958552')
and debit_credit = 'CR'
group by
unit_number, item_id   (3)

select count(debit_credit)  
from invoice_detail
where unit_number in ('W039714958552')
and debit_credit = 'DB'
group by
unit_number, item_id  ( 2)


select count(debit_credit)
from invoice_detail
where unit_number in ('W039713779596')
and debit_credit = 'CR'
group by
unit_number, item_id ( 4 )

select count(debit_credit)  
from invoice_detail
where unit_number in ('W039713779596')
and debit_credit = 'DB'
group by
unit_number, item_id  ( 7 )


In the screen attached I should only get the results for unit number W039714958552

Here count(credits) > count(debits) i.e   3 > 2

expected results
Unit_number       chk_digit  item_id  count(*)

W039714958552      8             E3056        2

Appreciate for the help

 query results
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40505566
this maybe?

select unit_number, item_id, count(credits) c_credit, count(debits)  c_debit
from invoice_detail
group by 
unit_number, item_id
having count(credits) > count(debits)

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40505573
invoice_detail has one column debit_credit and data is CR for credits and DB for debits.
0
 
LVL 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 40505602
here it is:

with invoice_detail as (
select 'W039713779596' unit_number, 'E0896' item_id, 'DB' debit_credit, '11' other from dual
union select 'W039713779596', 'E0896', 'CR', '12' from dual
union select 'W039713779596', 'E0896', 'CR', '13' from dual
union select 'W039713779596', 'E0896', 'DB', '14' from dual
union select 'W039713779596', 'E0896', 'CR', '15' from dual
union select 'W039714958552', 'E3056', 'DB', '21' from dual
union select 'W039714958552', 'E3056', 'DB', '22' from dual
union select 'W039714958552', 'E3056', 'CR', '23' from dual
union select 'W039714958552', 'E3056', 'CR', '24' from dual
)
select unit_number, item_id, sum(decode(debit_credit,'DB',1,0)) cnt_debit, sum(decode(debit_credit,'CR',1,0)) cnt_credit
from invoice_detail
group by unit_number, item_id
having sum(decode(debit_credit,'DB',1,0)) < sum(decode(debit_credit,'CR',1,0))

UNIT_NUMBER	ITEM_ID	CNT_DEBIT	CNT_CREDIT
W039713779596	E0896	2	3

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40505957
Instead of using SUM() when you want a count, simply return NULL from the decode.

SELECT
      unit_number
    , item_id
    , COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT
    , COUNT(decode(debit_credit, 'CR', 1, NULL)) CNT_CREDIT
FROM INVOICE_DETAIL
GROUP BY unit_number
       , item_id
HAVING COUNT(decode(debit_credit, 'CR', 1, NULL)) > COUNT(decode(debit_credit, 'DB', 1, NULL))
;

Open in new window

OR

I prefer to use CASE EXPRESSIONS, and when using COUNT() don't cater for the else condition as this becomes NULL which is what we require.
SELECT
      unit_number
    , item_id
    , COUNT(case when debit_credit = 'DB' then 1 end) CNT_DEBIT
    , COUNT(case when debit_credit = 'CR' then 1 end) CNT_CREDIT
FROM INVOICE_DETAIL
GROUP BY unit_number
       , item_id
HAVING COUNT(case when debit_credit = 'CR' then 1 end) > COUNT(case when debit_credit = 'DB' then 1 end)
;

Open in new window

see: http://sqlfiddle.com/#!4/b2e55/2

    CREATE TABLE INVOICE_DETAIL
    	(UNIT_NUMBER varchar2(13), ITEM_ID varchar2(5), DEBIT_CREDIT varchar2(2), OTHER int)
    //
    
    INSERT ALL 
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'CR', 12)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'CR', 13)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'CR', 15)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'DB', 11)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039713779596', 'E0896', 'DB', 14)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'CR', 23)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'CR', 24)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'DB', 21)
    	INTO INVOICE_DETAIL ("UNIT_NUMBER", "ITEM_ID", "DEBIT_CREDIT", "OTHER")
    		 VALUES ('W039714958552', 'E3056', 'DB', 22)
    SELECT * FROM dual
    //

**Query 1**:

    SELECT
          unit_number
        , item_id
        , COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT
        , COUNT(decode(debit_credit, 'CR', 1, NULL)) CNT_CREDIT
    FROM INVOICE_DETAIL
    GROUP BY unit_number
           , item_id
    HAVING COUNT(decode(debit_credit, 'CR', 1, NULL)) > COUNT(decode(debit_credit, 'DB', 1, NULL))
    

**[Results][2]**:
    
    |   UNIT_NUMBER | ITEM_ID | CNT_DEBIT | CNT_CREDIT |
    |---------------|---------|-----------|------------|
    | W039713779596 |   E0896 |         2 |          3 |


**Query 2**:

    SELECT
          unit_number
        , item_id
        , COUNT(case when debit_credit = 'DB' then 1 end) CNT_DEBIT
        , COUNT(case when debit_credit = 'CR' then 1 end) CNT_CREDIT
    FROM INVOICE_DETAIL
    GROUP BY unit_number
           , item_id
    HAVING COUNT(case when debit_credit = 'CR' then 1 end) > COUNT(case when debit_credit = 'DB' then 1 end)
    

**[Results][3]**:
    
    |   UNIT_NUMBER | ITEM_ID | CNT_DEBIT | CNT_CREDIT |
    |---------------|---------|-----------|------------|
    | W039713779596 |   E0896 |         2 |          3 |



  [1]: http://sqlfiddle.com/#!4/b2e55/2

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40506859
Nice solutions. I need one more information. How can I exclude
COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT when it is '0'
0
 
LVL 6

Author Comment

by:anumoses
ID: 40506881
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40507251
add

and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0

at the end of query...

with invoice_detail as (
select 'W039713779596' unit_number, 'E0896' item_id, 'DB' debit_credit, '11' other from dual
union select 'W039713779596', 'E0896', 'CR', '12' from dual
union select 'W039713779596', 'E0896', 'CR', '13' from dual
union select 'W039713779596', 'E0896', 'DB', '14' from dual
union select 'W039713779596', 'E0896', 'CR', '15' from dual
union select 'W039714958552', 'E3056', 'DB', '21' from dual
union select 'W039714958552', 'E3056', 'DB', '22' from dual
union select 'W039714958552', 'E3056', 'CR', '23' from dual
union select 'W039714958552', 'E3056', 'CR', '24' from dual
)
select unit_number, item_id, sum(decode(debit_credit,'DB',1,0)) cnt_debit, sum(decode(debit_credit,'CR',1,0)) cnt_credit
from invoice_detail
group by unit_number, item_id
having sum(decode(debit_credit,'DB',1,0)) < sum(decode(debit_credit,'CR',1,0)) and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40507258
group function cannot be used in where clause. It is giving error.

ORA-00934: group function is not allowed here
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 40507264
not sure what you did, but the last post with added filter is working fine...
post the query you have
0
 
LVL 6

Author Comment

by:anumoses
ID: 40507269
SELECT
      id.unit_number
    , item_id
      , COUNT(decode(debit_credit, 'DB', 1, NULL)) CNT_DEBIT
    , COUNT(decode(debit_credit, 'CR', 1, NULL)) CNT_CREDIT
FROM INVOICE_DETAIL id, invoice_header ih, units@plab.world u
where id.invoice_number = ih.invoice_number
and id.unit_number is not null
and id.unit_number = u.unit_id
and invoice_date between '01-jan-2014' and trunc(sysdate-1)
and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0
GROUP BY id.unit_number
       , item_id
HAVING COUNT(decode(debit_credit, 'CR', 1, NULL)) > COUNT(decode(debit_credit, 'DB', 1, NULL))
0
 
LVL 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 40507276
and COUNT(decode(debit_credit, 'DB', 1, NULL)) !=0

>>> move it to end
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40508004
:)

the WHERE clause does not understand aggregation functions such as COUNT() (so you get an error)

the HAVING clause is designed to handle those functions, so if you want to filter results using an aggregate function such as COUNT() or SUM() etc. use those in the HAVING clause.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40509613
thanks
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question