Answer Posted / sumathy

Create table as 'f_order' with coulmn f1.

Run the following statement to get the above quesion's
results.

DECLARE @a INT, @b int
DECLARE @f1 CURSOR, @f2 CURSOR
Print 'Ascending '
SET @f1 = CURSOR FOR
SELECT f1
FROM f_order order by 1 asc
OPEN @f1
FETCH NEXT
FROM @f1 INTO @a
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @a
FETCH NEXT
FROM @f1 INTO @a
END
CLOSE @f1
Print 'Descending '
SET @f2 = CURSOR FOR
SELECT f1
FROM f_order order by 1 desc
OPEN @f2
FETCH NEXT
FROM @f2 INTO @b
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @b
FETCH NEXT
FROM @f2 INTO @b
END
CLOSE @f2
DEALLOCATE @f1
DEALLOCATE @f2

Can I create table without primary key?

989

---

What is auto increment in sql?

1112

---

how many ways we can we find the current date using mysql? : Sql dba

1092

---

What is the difference between jpql and sql?

1087

---

What does select * from mean in sql?

3209

---

Enlist some predefined exceptions?

1051

---

What are pl/sql cursors?

1045

---

what are the limitations of mysql in comparison of oracle? Mysql vs. Oracle. : Sql dba

956

---

How can you view the errors encountered in a trigger?

954

---

How exception handling is done in advance pl/sql?

974

---

What is a nested table in word?

952

---

Can we use views in stored procedure?

1029

---

what is the difference between join and union? : Sql dba

1104

---

what is the difference between clustered and non clustered index in sql? : Sql dba

1116

---

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

2346

---