| Subquery [message #7420] |
Thu, 12 June 2003 11:21  |
Pranab K ghosh
Messages: 2
Registered: June 2003
|
Junior Member |
|
|
I am pretty new with the Oracle (9i) environment. I will appreciate if one of the "GURU's" can put some light into my very limited "Oracle" knowledge base.
From several tables I managed to extract one (single) table consists of (among others) following elements:
Customer number, Meter(reading date), Meter(reading type), and Service type.
Meter reading date will vary;
Meter reading type may be "E" for estimated or "A" for actual readings;
Service type may be for "GS" for gas and "WT" for water service.
My question is can I write a subquery to count and display the number of "GS" and "WT" estimated readings when they are in sequence and if the count is >= 4 for water service and >= 2 for gas services.
Hope, my description of the problem make sense. Thanks ans sincerely appreciate your help in advance.
Pranab (Pro....knob).
|
|
|
|
| Re: Subquery [message #7422 is a reply to message #7420] |
Thu, 12 June 2003 12:53  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
A sample set of data and the desired result set is always helpful. I'm not sure what you mean by "when they are in sequence."
What are you grouping by? Customer? Date?
|
|
|
|
| Re: Subquery [message #7428 is a reply to message #7420] |
Thu, 12 June 2003 14:41 |
Pranab K ghosh
Messages: 2
Registered: June 2003
|
Junior Member |
|
|
Here is a sampling of data. The first three group of "E's" should not be counted but the following three group should be counted as three separate sub groups within the same customer indicating that they are of a different time frame and are estimated reads.
Cust# Read Date Read type Service type
1590 1-Apr-01 E WT
1590 1-Mar-01 A WT
1590 1-Apr-01 A WT
1606 5-Feb-01 A WT
1607 1-Feb-01 A WT
1607 1-Mar-01 A WT
1623 1-Jan-01 A WT
1623 1-Feb-01 E WT
1623 1-Mar-01 E WT
1623 1-Apr-01 A WT
1623 1-May-01 E WT
1623 1-Jun-01 E WT
1623 1-Jul-01 E WT
1623 1-Aug-01 A WT
1623 10-Sep-01 A WT
1623 1-Sep-01 A WT
1632 1-Jan-01 A WT
1632 1-Feb-01 A WT
1634 1-Jan-01 A WT
1634 1-Feb-01 A WT
1636 1-Jan-01 A WT
1636 1-Feb-01 A WT
1640 1-Jan-01 A WT
1640 1-Feb-01 A WT
1640 1-Mar-01 A WT
1640 1-Jul-01 E WT
1640 1-Jul-01 E WT
1640 1-Sep-01 E WT
1640 1-Oct-01 E WT
1640 1-Nov-01 E WT
1640 22-Apr-03 E WT
1640 6-Apr-03 E WT
1640 1-Apr-03 E WT
1640 1-Mar-03 E WT
1640 1-Feb-03 E WT
1640 1-Jan-03 E WT
1640 1-Dec-01 E WT
1640 1-Jul-01 E WT
1640 1-Jul-01 E WT
1640 1-Oct-01 E WT
1640 1-Apr-03 E WT
1640 22-Apr-03 E WT
1640 6-Apr-03 E WT
1645 15-Sep-02 A WT
1645 2-Jun-02 A WT
1646 1-Jul-02 A WT
1646 1-Aug-02 A WT
1646 10-Mar-03 A WT
1646 25-Feb-03 A WT
1646 25-Jan-03 A WT
1646 25-Dec-02 A WT
1646 25-Oct-02 A WT
1646 25-Sep-02 A WT
1646 25-Aug-02 A WT
1650 1-Jul-02 A WT
1650 1-Aug-02 A WT
1650 25-Sep-02 A WT
1650 25-Oct-02 A WT
1650 25-Nov-02 A WT
1650 10-Mar-03 A WT
1650 25-Feb-03 A WT
1650 25-Jan-03 A WT
1650 25-Dec-02 A WT
1650 25-Aug-02 A WT
1653 1-Jul-02 A GS
1653 25-Aug-02 A GS
1653 3-Mar-03 E GS
1653 5-May-03 E GS
1653 20-May-03 E GS
1653 4-Apr-03 E GS
1653 25-Feb-03 A GS
1653 25-Dec-02 A GS
1653 25-Nov-02 A GS
1653 25-Oct-02 A GS
1653 25-Sep-02 A GS
1653 1-Jul-02 A WT
1653 25-Aug-02 A WT
1653 20-May-03 E WT
1653 5-May-03 E WT
1653 4-Apr-03 E WT
1653 3-Mar-03 E WT
1653 25-Feb-03 A WT
1653 25-Dec-02 A WT
1653 25-Nov-02 A WT
1653 25-Oct-02 A WT
1653 25-Sep-02 WT
Thanks.
|
|
|
|
| Re: Subquery [message #7452 is a reply to message #7420] |
Sun, 15 June 2003 23:26 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
Hi,
I want to execute 10 DML statements in a procedure.
All DML operations r doing on different tables.
while executing the DML statements if anyone fails in the middle, i have to rollback the previousely executed
statements.
Eg. After executing 5 statements, if 6th one fails , i have to rollback the previousely executed 5 statements.
please send me ASAP.
sridhar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
