Home » SQL & PL/SQL » SQL & PL/SQL » CRITICAL SELECT STATEMENT
CRITICAL SELECT STATEMENT [message #247705] Tue, 26 June 2007 23:02 Go to next message
syedmajidahmed
Messages: 4
Registered: December 2006
Location: Hyderabad,India
Junior Member
Hi,

I am trying to write a select statement on a table say "abc" based on 1 date column say "Process Date" and detail records count column say "Reccnt". Data of this table will be;

Process date Reccnt
------------- ---------
20/06/2007 10
21/06/2007 0
22/06/2007 5
23/06/2007 3
24/06/2007 0
25/06/2007 0
26/06/2007 0

I want data from this TABLE if the RECCNT of SYSDATE-1 is 0. And data should be fetched until the last 0 (i.e till 24th). If RECCNT of SYSDATE-1 is not zero then no rows should be fetched.

Thanks.

[Updated on: Wed, 27 June 2007 03:03]

Report message to a moderator

Re: SELECT STATEMENT [message #247706 is a reply to message #247705] Tue, 26 June 2007 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You should read & FOLLOW the posting guideline as stated in the #1 STICKY post at the top of this forum.

We don't do homework assignments so you better start coding.

[Updated on: Tue, 26 June 2007 23:07] by Moderator

Report message to a moderator

Re: CRITICAL SELECT STATEMENT [message #247855 is a reply to message #247705] Wed, 27 June 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Show us what you already tried.

Regards
Michel
Re: CRITICAL SELECT STATEMENT [message #248058 is a reply to message #247705] Wed, 27 June 2007 23:00 Go to previous messageGo to next message
syedmajidahmed
Messages: 4
Registered: December 2006
Location: Hyderabad,India
Junior Member
Hi Michel,

I have done it using loop but the requirement is that if the record count of sysdate -1 is 0 then only I should fetch the data because of table size.



Thanks.

----------------------------------------------------------------

BEGIN
DECLARE
CURSOR c10
IS
SELECT TRUNC (rptenddat) rptenddat, reccnt
FROM comtabdnllog
WHERE loccod = pi_loccod
AND coucod = l_coucod
AND curcod = l_comcrccod
AND ngtjobind = 'Y'
AND TRUNC (rptenddat) <= TRUNC (sysdate) - 1
ORDER BY rptenddat DESC, upddat DESC;

cr10 c10%ROWTYPE;
l_olddnldate DATE;
BEGIN
FOR cr10 IN c10
LOOP
IF c10%ROWCOUNT = 1
THEN
IF cr10.reccnt > 0
THEN
EXIT;
END IF;
ELSE
IF cr10.reccnt > 0
THEN
l_dnlfrmdat := cr10.rptenddat - 1;
EXIT;
END IF;
END IF;
END LOOP;
END;
END;
Re: CRITICAL SELECT STATEMENT [message #248059 is a reply to message #247705] Wed, 27 June 2007 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You appear to be somewhat clue resistant.
You should read & FOLLOW the posting guideline as stated in the #1 STICKY post at the top of this forum.
Re: CRITICAL SELECT STATEMENT [message #248081 is a reply to message #248058] Thu, 28 June 2007 00:41 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forget PL/SQL.
Try it in SQL.

And as Ana said:
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

I will add:
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version.

Regards
Michel
Previous Topic: pl/sql tuning
Next Topic: Triggers -Help
Goto Forum:
  


Current Time: Sun Dec 11 04:14:32 CST 2016

Total time taken to generate the page: 0.11286 seconds