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 Go to next message
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 #448394 is a reply to message #448390] Tue, 23 March 2010 02:55 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
From where does from_no,to_no comes for status='N'?
Re: Help in sql Query . [message #448396 is a reply to message #448390] Tue, 23 March 2010 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
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 #448408 is a reply to message #448404] Tue, 23 March 2010 04:26 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Good One.
But i really Dont know Why this row required...

Quote:
B1 103 104


sriram Smile
Re: Help in sql Query . [message #448410 is a reply to message #448408] Tue, 23 March 2010 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it is in MST (B1 101 110) but not in DET BOOKED.

Note that I assumed that MST books and numbers are a superset of DET ones.

Regards
Michel

Re: Help in sql Query . [message #448413 is a reply to message #448404] Tue, 23 March 2010 04:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Tue, 23 March 2010 04:08
SQL>  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



BUt OP Want this...
Quote:
Now i want to create a query that gives the below output


Code: [Select all] [Show/ hide]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


Any way Let OP take this as an Example And do his work.

@ Michel

The row i asked no where in the Given test case...
Thats why i asked that ...


sriram Smile
Re: Help in sql Query . [message #448422 is a reply to message #448413] Tue, 23 March 2010 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes I noticed the difference in the output, just a small change in the query that I let to OP (in addition I was not sure this was not an input or output error in the test case).

Quote:
The row i asked no where in the Given test case..

I don't see it, where?
That's what I said, it is include in MST 101-110 and not in DET which contains only 101-102,105-108. B1 109 110 does not disturb you, it is the same case.
SQL> SELECT * FROM booklet_MST;
BOOK_    FROM_NO      TO_NO
----- ---------- ----------
B1           101        110
B2           111        120
B3           121        130
B4           131        140
B5           141        150

5 rows selected.

SQL> select * from booklet_DET;
BOOK_    FROM_NO      TO_NO STATUS
----- ---------- ---------- ----------
B1           101        101 BOOKED
B1           102        102 BOOKED
B1           105        108 BOOKED
B3           121        130 BOOKED

4 rows selected.


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 #448435 is a reply to message #448422] Tue, 23 March 2010 06:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Any way Let OP take this as an Example And do his work.


sriram Smile
Re: Help in sql Query . [message #448598 is a reply to message #448435] Wed, 24 March 2010 03:43 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 63809
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
Previous Topic: show result random
Next Topic: Sorting VARCHAR2 field
Goto Forum:
  


Current Time: Thu Sep 29 02:15:17 CDT 2016

Total time taken to generate the page: 0.07179 seconds