1

I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on.

My current query uses this line in the SELECT statement to identify records that have a field that occurs elsewhere in the database:

CASE SIGN(COUNT(*) - 2) WHEN -1 then 0 else 1 END AS "REPEAT_OFFENDER_BIT"

So basically it says "if there are multiple records with this field (which is determined by my GROUP BY clause), put a one in the REPEAT_OFFENDER_BIT column, else put a zero.

I also need to make sure that the timestamps associated with these records it finds as having a certain common field are not the same. Only then would the record get a one rather than a zero.

Let's say my table columns are these:

fname | lname | ssn | chargeddate

A row should get a one for REPEAT_OFFENDER_BIT only if there exists another record with the same SSN and a different charged date.

What are my options for achieving this? My SQL is quite primitive so I came here for help. Thank you!

  • Are there NULL values in the date column? – ypercubeᵀᴹ Aug 13 '15 at 21:22
  • I filter them out – Alex Totheroh Aug 17 '15 at 15:21
2

Sample data:

create table offender
(
  fname varchar2(10),
  lname varchar2 (10),
  ssn number,
  chargeddate date
);

insert into offender values ('A', 'A', 1, date'2015-08-01');
insert into offender values ('B', 'B', 2, date'2015-08-02');
insert into offender values ('C', 'C', 3, date'2015-08-03');
insert into offender values ('D', 'D', 4, date'2015-08-04');
insert into offender values ('E', 'E', 1, date'2015-08-01');
insert into offender values ('F', 'F', 2, date'2015-08-05');
insert into offender values ('G', 'G', 3, date'2015-08-06');
insert into offender values ('H', 'H', 4, date'2015-08-04');
commit;

The query:

select
  fname,
  lname,
  ssn,
  chargeddate,
  decode(count(distinct chargeddate) over (partition by ssn), 1, 0, 1) as repeat_offender_bit
from
  offender;

FNAME      LNAME             SSN CHARGEDDATE         REPEAT_OFFENDER_BIT
---------- ---------- ---------- ------------------- -------------------
A          A                   1 2015-08-01 00:00:00                   0
E          E                   1 2015-08-01 00:00:00                   0
B          B                   2 2015-08-02 00:00:00                   1
F          F                   2 2015-08-05 00:00:00                   1
C          C                   3 2015-08-03 00:00:00                   1
G          G                   3 2015-08-06 00:00:00                   1
H          H                   4 2015-08-04 00:00:00                   0
D          D                   4 2015-08-04 00:00:00                   0
  • Thank you very much! This works great. I appreciate it. – Alex Totheroh Aug 17 '15 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.