Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Venkat.

Asked: February 02, 2003 - 6:52 pm UTC

Last updated: August 08, 2003 - 4:54 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I need your help in the SQL Query.

SQL>desc emp_data

Name Null? Type
------------------------------------------- -------- ------------
EMP_NUM NUMBER
EMP_NAME VARCHAR2(30)
TIMESHEET_DT DATE
STATUS_DSC VARCHAR2(30)

SQL>select * from emp_data;

EMP_NUM EMP_NAME TIMESHEET_D STATUS_DSC
======= ============================== =========== ==========
10 Venkat 03-FEB-2003 emp
10 Venkat 13-FEB-2003 emp
10 Venkat 14-FEB-2003 emp
10 Venkat 24-FEB-2003 emp
20 Vincent 03-FEB-2003 emp
20 Vincent 13-FEB-2003 emp
20 Vincent 14-FEB-2003 emp
20 Vincent 24-FEB-2003 emp
20 Vincent
30 xavier

For emp_type 20 there are total 5 records, out of which one is having
NULL value for timesheet_dt and status_dsc. We have only one record
for 30 where again timesheet_dt and status_dsc value is NULL.

I need to show like this,

EMP_NUM EMP_NAME TIMESHEET_D STATUS_DSC
======= ============================== =========== ==========
10 Venkat 03-FEB-2003 emp
10 Venkat 13-FEB-2003 emp
10 Venkat 14-FEB-2003 emp
10 Venkat 24-FEB-2003 emp
20 Vincent 03-FEB-2003 emp
20 Vincent 13-FEB-2003 emp
20 Vincent 14-FEB-2003 emp
20 Vincent 24-FEB-2003 emp
30 xavier

The emp_type 20 with NULL timesheet_dt should not appear because
there are other records with non-null value for this where as
since 30 is having only one record that should be shown.

Please help me in writing the SQL Query for the above.

Thanks in advance,
Venkat


and Tom said...

analytics rock and roll:

ops$tkyte@ORA920> select emp_num,
2 emp_name,
3 timesheet_dt,
4 status_dsc
5 from (
6 select emp_num, emp_name, timesheet_dt, status_dsc,
7 count(timesheet_dt) over ( partition by emp_num ) cnt
8 from emp_data
9 )
10 where cnt = 0 OR timesheet_dt is not null
11 /

EMP_NUM EMP_NAME TIMESHEET STATUS_DSC
---------- ------------------------------ --------- ----------------
10 Venkat 03-FEB-03 emp
10 Venkat 13-FEB-03 emp
10 Venkat 14-FEB-03 emp
10 Venkat 24-FEB-03 emp
20 Vincent 03-FEB-03 emp
20 Vincent 13-FEB-03 emp
20 Vincent 14-FEB-03 emp
20 Vincent 24-FEB-03 emp
30 xavier

9 rows selected.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL Query

Venkat, February 03, 2003 - 9:58 am UTC

Thank you so much Tom.
God...give me 10% of Tom's knowledge.

Perfect, thanks Tom

Marc, February 03, 2003 - 5:56 pm UTC


Sorry for asking an irrelevant question here...

Riaz Shahid, August 05, 2003 - 8:22 am UTC

Consider the following:

cr@STARR.LHR> ed
Wrote file afiedt.buf

1 select * from (
2 select nic_no NIC,reg_no,name,trim(address_1) "Address"
3 from gstt01 a
4 where nic_no in
5 (select nic_no from (
6 (select count(*),nic_no from gstt01
7 where trim(nic_no)!='0'
8 group by nic_no
9 having count(*)>1
10 )
11 )
12 )
13 order by 1
14 )
15* where rownum<10

NIC REG_NO Address
============= ============= ==============================
093-71-045527 0309520091628 410-FIRST FLOOR CLOTH MARKET

0309520086691 410-FIRST FLOOR CLOTH MARKET
101-44-637033 1200581002119 38-E, BLOCK-6, PECHS,
1200840003446 38-E, BLOCK-6, PECHS,
101-47-075162 0701980505219 PLOT # 76, STREET # 6, SECTOR 1- 1100980552364 PLOT # 76, SECTOR-6, SECTOR-I-101-52-486192 0701980125473 XX
0701220204919 PLOT # 82, STREET # 10, I-9/2
101-54-014392 0701170100364 H.NO. 23, GALI NO. 20

9 rows selected.

Elapsed: 00:00:51.97

Statistics
==========================================================
6 recursive calls
132 db block gets
23294 consistent gets
21218 physical reads
0 redo size
1278 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
1 sorts (disk)
9 rows processed


(I've put "where rownum<10" to show you just a set of whole resut set).

I want those records (out of those as shown above) who has:
(1) the same NIC_No
(2) different reg_no
and
(3) Whose first 10 characters of column address_1 are equal.

Can you help me regarding that ???


Riaz

Tom Kyte
August 05, 2003 - 12:57 pm UTC

1) the same nic_no as what?
2) a different reg_no from what?
3) are equal to what?

So Sorry....

Riaz, August 07, 2003 - 4:29 am UTC

I am really sorry for not providing the complete information...really sorry for wasting your time. I solved that ptoblem. But now i am facing problem with another query...consider:

cr@STARR.LHR> create table test (reg_no varchar2(13), address varchar2(60),oper_sts char(1),reg_date date);

Table created.

Elapsed: 00:00:00.46

cr@STARR.LHR> ED
Wrote file afiedt.buf

1* insert into test values('1111111111111','HELLO','1',SYSDATE-10)
cr@STARR.LHR> /

1 row created.

Elapsed: 00:00:00.25
cr@STARR.LHR> ED
Wrote file afiedt.buf

1* insert into test values('2222222222222','HELLO','1',SYSDATE-15)
cr@STARR.LHR> /

1 row created.

Elapsed: 00:00:00.09
cr@STARR.LHR> ED
Wrote file afiedt.buf

1* insert into test values('3333333333333','I AM HERE','1',SYSDATE-15)
cr@STARR.LHR> /

1 row created.

Elapsed: 00:00:00.09
cr@STARR.LHR> ed
Wrote file afiedt.buf

1* insert into test values('4444444444444','I AM NOT HERE','1',SYSDATE-15)
cr@STARR.LHR> /

1 row created.

Elapsed: 00:00:00.25
cr@STARR.LHR> COMMIT;

Commit complete.

Elapsed: 00:00:00.25
cr@STARR.LHR> SELECT * FROM TEST;

REG_NO ADDRESS Oper_STS REG_DATE

1111111111111 HELLO 1 28-JUL-03 2222222222222 HELLO 1 23-JUL-03 3333333333333 I AM HERE 1 23-JUL-03 4444444444444 I AM NOT HERE 1 23-JUL-03

Elapsed: 00:00:00.97

I need only those records who have duplicate address (first 10 characters matching).i.e; i need the following:

REG_NO ADDRESS Oper_STS REG_DATE

1111111111111 HELLO 1 28-JUL-03 2222222222222 HELLO 1 23-JUL-03


Can you help me ???

SQL QUERY

Khan MD, August 08, 2003 - 4:54 am UTC

Try This,
create table test (reg_no varchar2(13), address
varchar2(60),oper_sts char(1),reg_date date);

select a.* from test a , test b
where substr(a.address,1,10) = substr(b.address,1,10)
and a.rowid <> b.rowid;

select * from test a
where exists (select 1 from test
where substr(a.address,1,10) = substr(address,1,10)
group by substr(address,1,10)
having count(substr(address,1,10)) > 1);



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.