We help IT Professionals succeed at work.

sqll_query

sam15
sam15 asked
on
263 Views
Last Modified: 2013-12-19
I have 3 tables. attached file have DDL for creating tables and loading data.

 book_review
(bkno    number(10),
 stage    number(1),
 status   varchar2(1),
 created_date   date );
 
 
 param
 (no_rows  number(3) )
    /
 book_assign
 (  assign_no  number(10),
    bk_no      number(10),
    assign_to  varchar2(10),
    created_date date )


I want to create a view/query that displays all the books that have:

1.  Last stage = 0 and status = 'A' sorted by created date

and then pick the number of rows from this list based on param.no_rows.

I wrote this which seems to work.


select * from
(
select * from book_review where (bkno,created_Date) in
(
select bkno,max(created_date) from book_review group by bkno
)
and status = 'A' and stage=0
order by created_Date
)
where rownum <= (select no_rows from param);



Now i need to add two columns to result set above as follows:

COLUMN ONE:

--Find Previous assignment (PREV_ASSIGN)
--Not assigned = NULL = no records in book_assign table
--Assigned = Show last record for assignment in book assign_table

I think this query gives me this:

select assign_to from book_assign Prev_assign where (bk_no,created_date) in
(select bk_no,max(created_date) from book_assign group by bkno)
/

COLUMN TWO:

--Find current assignment (CUR_ASSIGN)
--Not assigned = Null = no records in book_assign table
--There is a record in "book_assign" but book_assign.created_Date < book_review.created_Date
-- show NULL value
--There is a record in "book_assign" but book_assign.created_Date >= book_review.created_Date
-- show userid

I thnk query should look like this.

select assign_to from book_assign Prev_assign where (bk_no,created_date) in
(select bk_no,max(created_date) from book_assign group by bkno)
and created_date >= select b.created_date from book_review where b.created_Date >=
(the created_date in booK_review record).
/


Do you know to merge the last two queries into the main one? Would you include it as part of the column select of the main one like :
select *,query1,query2 from table.

book-review.txt
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
There seems to be an issue

SELECT * from book_assign. i only have 3 records for book 1000.
SQL> select * from book_assign;

 ASSIGN_NO       BKNO ASSIGN_TO  CREATED_D
---------- ---------- ---------- ---------
         1       1000 mike       21-JUL-08
         2       1000 john       22-JUL-08
         3       1000 sam        23-JUL-08

But the query is listing those for othe books

      BKNO      STAGE S CREATED_D PREV_ASSIG CURR_ASSIG
---------- ---------- - --------- ---------- ----------
      1000          0 A 20-JUL-08 sam        sam
      1001          0 A 21-JUL-08 sam        sam
      1003          0 A 22-JUL-08 sam        sam
      1005          0 A 26-JUL-08 sam

4 rows selected.


Author

Commented:
I got this working. I just added a filter to the top two *select queries) to filter on the A.bkno.

 I want to create a similar view except that it should query the books that do not have
 a stage of "5" and status of "A".
 
 How would you implement this? would you first find the book numbers that do have
 stage=5 and status="A" and then exclude those from the main list. A book can have 8 stages so the stage=5 may not be the last one in the table. But i want to getthe last record in book review if that book does not have that condition.

would you do it like this:
 
select * from
(
select * from book_review where (bkno,created_Date) in
(
select bkno,max(created_date) from book_review
where bkno not in (select distinct bkno from book_review where stage=5 and status='A')
group by bkno
)
order by created_Date
)
where rownum <= (select no_rows from param);

 
book_review
(bkno    number(10),
 stage    number(1),
 status   varchar2(1),
 created_date   date );
 
 1000,0,A,sysdate
 1000,1,A,sysdate+1
 1002,0,R,sysdate
 1003,0,R,sysdate+1
 1003,0,A,sysdate+2
 1004,0,A,sysdate
 1004,1,A,sysdate+1
 1004,2,A,sysdate+2
 1004,5,A,sysdate+3
 
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.