Solved

Oracle query

Posted on 2014-12-17
13
179 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 51

Expert Comment

by:HainKurt
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 51

Assisted Solution

by:HainKurt
HainKurt 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
 
LVL 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:HainKurt
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 51

Expert Comment

by:HainKurt
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 51

Assisted Solution

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

>>> move it to end
0
 
LVL 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now