Solved

Oracle query

Posted on 2014-12-17
13
186 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
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 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
 
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is the version of ojdbc6.jar 2 58
Oracle - Stored Procedure Privilge access 3 53
VB.Net - Oracle BulkCopy from CSV Date Format 7 59
Oracle Listener Not Starting 11 44
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

770 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