Home » SQL & PL/SQL » SQL & PL/SQL » Help in sql Query . (Oracle9i Enterprise Edition Release 9.2.0.5.0)
| Help in sql Query . [message #448390] |
Tue, 23 March 2010 02:32  |
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
|
Member |
|
|
Hi All
I need a help to create a query .
(Oracle version- Oracle9i Enterprise Edition Release 9.2.0.5.0)
I have two tables
create table booklet_MST
(
book_no varchar2(5),
from_no number(5),
to_no number(5),
STATUS VARCHAR2(10)
);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B1', 101, 110);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B2', 111, 120);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B3', 121, 130);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B4', 131, 140);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B5', 141, 150);
COMMIT;
-----------------------------
SELECT * FROM booklet_MST;
BOOK_NO FROM_NO TO_NO
B1 101 110
B2 111 120
B3 121 130
B4 131 140
B5 141 150
and the second table is
create table booklet_DET
(
book_no varchar2(5),
from_no number(5),
to_no number(5)
);
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B1', 101, 101, 'BOOKED');
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B1', 102, 102, 'BOOKED');
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B1', 105, 108, 'BOOKED');
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B3', 121, 130, 'BOOKED');
COMMIT;
----------------------
select * from booklet_DET;
BOOK_NO FROM_NO TO_NO STATUS
B1 101 101 BOOKED
B1 102 102 BOOKED
B1 105 108 BOOKED
B3 121 130 BOOKED
In booklet_MST we have column FROM_NO and TO_NO ,it is showing the range of the book_no.from these ranges we will pick some range and will book it.
Now i want to create a query that gives the below output
BOOK_NO FROM_NO TO_NO STATUS
B1 101 101 BOOKED
B1 102 102 BOOKED
B1 103 104 N
B1 105 108 BOOKED
B1 109 110 N
B2 111 120 N
B3 121 130 BOOKED
B4 131 140 N
B5 141 150 N
THANKS IN ADVANCE
NUSRAT
[Updated on: Tue, 23 March 2010 02:36] Report message to a moderator |
|
|
|
|
|
| Re: Help in sql Query . [message #448396 is a reply to message #448390] |
Tue, 23 March 2010 02:57  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
There is no STATUS value in BOOKLET_MST but no STATUS column in your SELECT *, how could this be possible?
There is no STATUS column in booklet_DET defintion, how could you insert with a value for this column?
Is STATUS in BOOKLET_MST or booklet_DET?
Post a working, coherent and faked test case.
Regards
Michel
[Updated on: Tue, 23 March 2010 02:58] Report message to a moderator |
|
|
|
| Re: Help in sql Query . [message #448398 is a reply to message #448396] |
Tue, 23 March 2010 03:24 |
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
|
Member |
|
|
Sorry Michel
its my mistake . I will change and post it again.
create table booklet_MST
(
book_no varchar2(5),
from_no number(5),
to_no number(5)
);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B1', 101, 110);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B2', 111, 120);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B3', 121, 130);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B4', 131, 140);
Insert into BOOKLET_MST
(BOOK_NO, FROM_NO, TO_NO)
Values
('B5', 141, 150);
COMMIT;
-----------------------------
SELECT * FROM booklet_MST;
BOOK_NO FROM_NO TO_NO
B1 101 110
B2 111 120
B3 121 130
B4 131 140
B5 141 150
and the second table is
create table booklet_DET
(
book_no varchar2(5),
from_no number(5),
to_no number(5),
STATUS VARCHAR2(10)
);
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B1', 101, 101, 'BOOKED');
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B1', 102, 102, 'BOOKED');
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B1', 105, 108, 'BOOKED');
Insert into BOOKLET_DET
(BOOK_NO, FROM_NO, TO_NO, STATUS)
Values
('B3', 121, 130, 'BOOKED');
COMMIT;
----------------------
select * from booklet_DET;
BOOK_NO FROM_NO TO_NO STATUS
B1 101 101 BOOKED
B1 102 102 BOOKED
B1 105 108 BOOKED
B3 121 130 BOOKED
In booklet_MST we have column FROM_NO and TO_NO ,it is showing the range of the book_no.from these ranges we will pick some range and will book it.
Now i want to create a query that gives the below output
BOOK_NO FROM_NO TO_NO STATUS
B1 101 101 BOOKED
B1 102 102 BOOKED
B1 103 104
B1 105 108 BOOKED
B1 109 110
B2 111 120
B3 121 130 BOOKED
B4 131 140
B5 141 150
Thanks
Nusrat
|
|
|
|
| Re: Help in sql Query . [message #448404 is a reply to message #448398] |
Tue, 23 March 2010 04:08 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 lines as ( select level-1 line from dual connect by level <= 10 ),
3 mst as (
4 select book_no, from_no+line no
5 from booklet_mst, lines
6 where from_no+line <= to_no
7 ),
8 det as (
9 select book_no, from_no+line no, status
10 from booklet_det, lines
11 where from_no+line <= to_no
12 ),
13 results as (
14 select mst.book_no, mst.no, det.status,
15 decode(lag(mst.no)
16 over (partition by mst.book_no, det.status order by mst.no),
17 mst.no-1, null,
18 row_number() over(order by mst.book_no, det.status, mst.no)) rn
19 from mst, det
20 where det.book_no (+) = mst.book_no
21 and det.no (+) = mst.no
22 ),
23 grouped as (
24 select book_no, no, status,
25 last_value(rn ignore nulls) over (order by book_no, no) grp
26 from results
27 )
28 select min(book_no), min(no) from_no, max(no) to_no, min(status)
29 from grouped
30 group by grp
31 order by 1, 2
32 /
MIN(B FROM_NO TO_NO MIN(STATUS
----- ---------- ---------- ----------
B1 101 102 BOOKED
B1 103 104
B1 105 108 BOOKED
B1 109 110
B2 111 120
B3 121 130 BOOKED
B4 131 140
B5 141 150
Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help in sql Query . [message #448598 is a reply to message #448435] |
Wed, 24 March 2010 03:43 |
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
|
Member |
|
|
Thanks to all of you for your support.
Query is working in "Oracle 10g" but not in "Oracle9i Enterprise Edition Release 9.2.0.5.0"
giving error "ORA-00907- missing right parenthesis" for
last_value(rn ignore nulls)
after removing "ignore nulls" it is showing "ORA-24374: define not done before fetch or execute and fetch" error..
Thanks for the help.
Nusrat
|
|
|
|
| Re: Help in sql Query . [message #448601 is a reply to message #448598] |
Wed, 24 March 2010 04:20 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Ooups! missed the version.
This one should work in your version:
SQL> with
2 lines as ( select level-1 line from dual connect by level <= 10 ),
3 mst as (
4 select book_no, from_no+line no
5 from booklet_mst, lines
6 where from_no+line <= to_no
7 ),
8 det as (
9 select book_no, from_no+line no, status
10 from booklet_det, lines
11 where from_no+line <= to_no
12 ),
13 results as (
14 select mst.book_no, mst.no, det.status,
15 decode(lag(mst.no)
16 over (partition by mst.book_no, det.status order by mst.no),
17 mst.no-1, null,
18 row_number() over(order by mst.book_no, det.status, mst.no)) rn
19 from mst, det
20 where det.book_no (+) = mst.book_no
21 and det.no (+) = mst.no
22 ),
23 grouped as (
24 select book_no, no, status,
25 max(rn) over (partition by book_no, status order by no) grp
26 from results
27 )
28 select min(book_no), min(no) from_no, max(no) to_no, min(status)
29 from grouped
30 group by grp
31 order by 1, 2
32 /
MIN(B FROM_NO TO_NO MIN(STATUS
----- ---------- ---------- ----------
B1 101 102 BOOKED
B1 103 104
B1 105 108 BOOKED
B1 109 110
B2 111 120
B3 121 130 BOOKED
B4 131 140
B5 141 150
8 rows selected.
Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:05:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
