How to do Stored procedure testing? Who does this testing
(Developer or tester)?
72
How do you recover a datafile that has not been physically
been backed up since its creation and has been deleted.
Provide syntax example.
53
How to customize error conditions.
9
As a part of your job, what are the DBCC commands that you
commonly use for database maintenance?
143
Hi,
I am new in oracle(SQL), could anyone help me in writing a
correct SQL.
Below is the table structure.
Table: Subsc
Fields:
1. Sub_no (this field will hold values of subscriber nos,
for e.g. S111111, S222222, S333333, S444444, etc.)
2. s_status (this field will hold values for different
status of subscriber, for e.g. 'A', 'S', 'C', etc.)
3. cus_id (this field will hold values of bill nos for e.g.
11111111, 22222222, 33333333, 44444444, etc.)
Table: Bill
Fields:
1. Bill_no this field will hold values of bill nos for e.g.
11111111, 22222222, 33333333, 44444444, etc.)
2. b_status = (this field will hold values for different
status of bill for e.g. 'O', 'C', 'S', etc.)
Note:
1. The Sub_no is a Primary key of Subsc table.
2. The cus_id is a foreign in Subsc table (referred from
Bill_no field of Bill table)
3. The Bill_no field is the Primary key of Bill table.
Query A --> I wrote a query to select cus_id/Bill_no which
is in status open (b_status = 'O') and having more than two
active subscriber (i.e. S_status = 'A') in it ( i.e. more
the two subscribers in same bill).
select s.cus_id
from subsc s
where exists (select 1 from bill
where bill_no = s.cus_id
and b_status = 'O')
and s_status = 'A'
group by s.cus_id
having count(sub_no) = 2
Problem : The above query will give the cus_id (or rather
bill_no) which are in open status (b_status ='O) and which
are having TWO ACTIVE Subscribers (s_status ='A') in it.
However, this query will also lists the cus_id/bill_no
which are having more than TWO subscribers in it
(but only two subscriber will be in Active status (s_status
= 'A') and the others will be in s_status = 'C' or s_status
= 'S'.
Help needed: I want to write a query which will fetch ONLY
the cus_id/bill_no which are in open status (b_status ='O')
and which are having ONLY TWO ACTIVE subscribers (s_status
='A') in it.
B--> If I include the sub_no in the above query then NO row
are returned.
select s.cus_id, s.sub_no
from subsc s
where exists (select 1 from bill
where bill_no = s.cus_id
and b_status = 'O')
and s_status = 'A'
group by s.cus_id, s.sub_no
having count(sub_no) = 2
Help needed: I want to modify the above query which will
fetch ONLY the cus_id/bill_no which are in open status
(b_status ='O') and which are having ONLY TWO ACTIVE
subscribers (s_status ='A') in it ALONG with the sub_no.
Thanks a lot in advance.
Regards,
Nitin
156
what is diff between pre insert,on insert,post insert
triggers?
251
Which one is the Best approach to put code, among Triggers
and Procedures? Explain?
how to get the values if source table & target table having
duplicate values that matches the lookup condition ?(i want
all duplicate match)
114
issues involved in modeling and building data werahouses
75
Can you have a nested transaction?
216
You have modified 100 store procedures and want to
replicate these changes from development to prodution, and
production can have users using the Server/DB, how would
you replicate without causing issues?
103
what is the role of auditor in auditing data oriented
applications?
160
when we login sql editor using username and password then it
says this error "could not resolve service name" then what
can we do at client side.( here thing is tnsnames.ora file
is ok till yesterday and worked well, but now it giving error.
5
how do u setup a replication site?
67
How To Find That One Week Change in My DataBase we Have Done
Like We Have A database Test I Change Table,Stored Procd.
Then I Find Which SP,Table We Have Edit/Change in Seven
Dayes
To Refer this Site to Your Friends 
Site Map
