Link to home
Start Free TrialLog in
Avatar of sam15
sam15

asked on

sqll_query

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
ASKER CERTIFIED SOLUTION
Avatar of gajmp
gajmp

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sam15
sam15

ASKER

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.


Avatar of sam15

ASKER

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