help needed in query....

Question

Post reply

help needed in query....

vidya-391318

Old Hand

Points: 387
More actions
April 6, 2007 at 10:41 am

#119541

help needed in query......
suppose i have table like this
doc id  status        dtesubmitime                         dteprocesstime                  comments
1            Y            2007-03-26 14:47:27.500       2007-03-26 14:49:27.000 || '' document has been processed
2           B           2007-03-26 14:50:10.280       2007-03-26 14:55:27.000 || '' document is busy
3           Y           2007-03-26 14:52:22.983       2007-03-26 15:00:27.000 || '' document has processed
4           B           2007-03-26 15:27:07.297       2007-03-26 15:33:27.000  || '' document is busy / stuck
5           N           2007-03-26 16:17:38.530       NULL                                || '' not processed
6           N           2007-03-26 16:27:12.467       NULL                                || '' not processed
i need a query that will return the stuck document id
the query should return "4" coz that document is stuck,..
and the remaining docs are not processed because of that,...
how do i get "4" as the stuck document id ???
u can ask that "2" also has a status of "B" busy,... but the documents that were submitted after that, were processed,...so the process is not stuck, it is busy doing something,......
thanks,
vidya

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Did you try

SELECT * FROM myTable WHERE comments LIKE '%stuck%'

vidya-391318 Old Hand Points: 387 More actions · Answer 2

comments is not a column,... i just added it just for the sake of explanation

thanks

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 3

Could you not do something like

SELECT docid

FROM table a

where exists (select * from table b where a.docid = b.docid + 1 and dteprocesstime IS NULL)

David

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

If I understand the data correctly then I think David's on the right track.

Kurt

SELECT docid

FROM table a

where exists (select * from table b where a.docid + 1 = b.docid and b.dteprocesstime IS NULL)

I think that is what needs to be done. You want to look at the next row in the table from where you are. I would seem to think David's would look at the row before not after.

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Why do everyone expect the DocID to be sequential?

SELECT

t.*

FROM Table1 AS t

WHERE t.dteprocesstime = (SELECT MAX(y.dteprocesstime) FROM Table1 AS y)

N 56°04'39.16"
E 12°55'05.25"

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 6

Good point Peter....

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 7

Hi Vidhya ,

It means u can't categorise it form a table. If both of document has same status..

Is there any categorisation between stucked or busy...

Regards

Amit GUpta

Sergiy SSC Guru Points: 110208 More actions · Answer 8

To avoid false alarms add another check to Peter's solution:

SELECT t.*

FROM Table1 AS t

WHERE t.dteprocesstime = (SELECT MAX(y.dteprocesstime) FROM Table1 AS y)

AND EXIST(select 1 from Table1 AS n where n.dteprocesstime IS NULL)

Now it will return not empty recordset only if there are not processed documents.

_____________
Code for TallyGenerator

vidya-391318 Old Hand Points: 387 More actions · Answer 9

vidya-391318

Old Hand

Points: 387

April 11, 2007 at 3:29 pm

#699918

thanks everybody