This discussion is archived
13 Replies Latest reply: Apr 7, 2013 10:12 PM by HawkerHunter RSS

Query to create a view using the following tables

HawkerHunter Newbie
Currently Being Moderated
Hi,
I am struggling to write the proper query to create a view using three tables.
I would really be thankful if anyone can help me.

I am pasting the following script to create the required tables and also insert appropriate data in each of the table.

--*******************************************
create table HR.STUDENT_DETAILS (
STUDENT_ID NUMBER(10),
STUDENT_NAME VARCHAR2(50),
DOB DATE,
SEX CHAR(1),
ACTIVE CHAR(1),
CONTACT_NUMBER NUMBER(20),
primary key(STUDENT_ID)
);

create table HR.PAYMENT_HEADER (
PAY_HEADER_ID NUMBER(10),
DOC_NUM NUMBER(5),
MONTH_NAME VARCHAR2(10),
primary key(PAY_HEADER_ID)
);

create table HR.PAYMENT_DETAILS (
HEADER_ID NUMBER(10),
LINE_ID NUMBER(10),
STUDENT_ID NUMBER(10),
CUM_AMOUNT NUMBER(10),
primary key(HEADER_ID,LINE_ID)
);


INSERT ALL
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1112,'James','17-JUN-05','M','Y',23674378)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1113,'David','21-SEP-05','M','Y',24565457)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1114,'Michael','13-JAN-06','M','Y',25436784)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1115,'Joseph','03-JAN-06','M','Y',23435673)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1116,'Juliet','21-MAY-05','F','Y',23234527)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1117,'Monica','25-JUN-05','F','Y',24873245)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1118,'William','05-FEB-05','M','Y',23623245)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1119,'Karen','07-FEB-06','F','Y',26757543)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1120,'Erika','17-AUG-05','F','Y',25435465)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1121,'Noah','16-AUG-05','M','Y',23457645)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1122,'Angelina','28-SEP-05','F','Y',26456787)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1123,'Gabriela','30-SEP-05','F','Y',29767543)
INTO HR.STUDENT_DETAILS (STUDENT_ID,STUDENT_NAME,DOB,SEX,ACTIVE,CONTACT_NUMBER) VALUES (1124,'Sofia','07-MAR-06','F','Y',27656578)
SELECT * FROM DUAL;

INSERT ALL
INTO HR.PAYMENT_HEADER (PAY_HEADER_ID,DOC_NUM,MONTH_NAME) VALUES (305,1,'JAN')
INTO HR.PAYMENT_HEADER (PAY_HEADER_ID,DOC_NUM,MONTH_NAME) VALUES (306,2,'FEB')
INTO HR.PAYMENT_HEADER (PAY_HEADER_ID,DOC_NUM,MONTH_NAME) VALUES (307,3,'MAR')
INTO HR.PAYMENT_HEADER (PAY_HEADER_ID,DOC_NUM,MONTH_NAME) VALUES (308,4,'APR')
INTO HR.PAYMENT_HEADER (PAY_HEADER_ID,DOC_NUM,MONTH_NAME) VALUES (309,5,'MAY')
SELECT * FROM DUAL;

INSERT ALL
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (305,12,1112,40)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (305,13,1113,40)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (305,14,1114,40)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (306,15,1112,80)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (306,16,1113,80)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (306,17,1114,80)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (306,18,1115,40)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (306,19,1116,40)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,20,1112,120)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,21,1113,120)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,22,1114,120)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,23,1115,80)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,24,1116,80)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,25,1117,40)
INTO HR.PAYMENT_DETAILS (HEADER_ID,LINE_ID,STUDENT_ID,CUM_AMOUNT) VALUES (307,26,1118,40)
SELECT * FROM DUAL;
--*******************************************

The above table STUDENT_DETAILS stores the details of all the students and each student has a unique student id.
Another table PAYMENT_HEADER saves the payment details as a document for each month which has a header id.
In the PAYMENT_DETAILS table, payment details are stored for the students (who made the payment for that month). This table does not save the data for a student if he has not paid on that month. This table is connected to the PAYMENT_HEADER table through a header Id.
The view should be such that when I pass the document number, it shall show the names of all the students, but shall show payment amount only for those students who had paid under the selected document number, for other it should show null.

I tried the following query:

--*****************************************
select * from (
select sd.student_name,sd.DOB,sd.sex,sd.contact_number,pd.doc_num,pd.month_name,pd.cum_amount
from hr.student_details sd left join
(select hdr.doc_num,hdr.month_name,det.student_id,det.cum_amount
from hr.payment_header hdr, hr.payment_details det
where hdr.pay_header_id = det.header_id) pd on sd.student_id = pd.student_id)

--*****************************************

But when I pass the filtering values like document number, it does not show some students with null values.

--********************************************
select * from (
select sd.student_name,sd.DOB,sd.sex,sd.contact_number,pd.doc_num,pd.month_name,pd.cum_amount
from hr.student_details sd left join
(select hdr.doc_num,hdr.month_name,det.student_id,det.cum_amount
from hr.payment_header hdr, hr.payment_details det
where hdr.pay_header_id = det.header_id) pd on sd.student_id = pd.student_id)
where doc_num = 1 or doc_num is null;
--*********************************************

My requirement is that, every time I use a document number in the where clause for the view it should show the paid amount against the student names who has paid on that month and for all other student it should null. Total student number is 13. So every time it should show 13 students only even when I pass document No 2 or 3.

Seeking your help.
Regards
Hawker
  • 1. Re: Query to create a view using the following tables
    BluShadow Guru Moderator
    Currently Being Moderated
    Wow... nearly 4 years on the forum, over 300 posts made, and you still don't ask a question or format it properly.

    Read: {message:id=9360002}

    You should know better by now.
  • 2. Re: Query to create a view using the following tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Hawker,
    HawkerHunter wrote:
    ... I am pasting the following script to create the required tables and also insert appropriate data in each of the table.
    Thanks, that's very helpful!
    --*******************************************
    create table HR.STUDENT_DETAILS (
    Don't use Oracle-supplied schemas (such as hr) for your own objects. Create your own scehma for your own tables.
    STUDENT_ID NUMBER(10),
    STUDENT_NAME VARCHAR2(50),
    DOB DATE,
    SEX CHAR(1),
    This site won't display some words that are associated with pornography.
    When posting here, use something else (such as GENDER) for a column denoting "male" or "female".
    ACTIVE CHAR(1),
    CONTACT_NUMBER NUMBER(20),
    primary key(STUDENT_ID)
    );

    create table HR.PAYMENT_HEADER (
    PAY_HEADER_ID NUMBER(10),
    DOC_NUM NUMBER(5),
    MONTH_NAME VARCHAR2(10),
    What does the month_name column represent?
    If it indicates a month, independent of any particular year, and you're using values like 'JAN' and 'FEB', then why is it 10 characters long?
    If it indicates a particular month and year (that is, Jan. 2013 is not the same as Jan. 2012), then use DATE, not VARCHAR2.
    primary key(PAY_HEADER_ID)
    );

    create table HR.PAYMENT_DETAILS (
    HEADER_ID NUMBER(10),
    LINE_ID NUMBER(10),
    STUDENT_ID NUMBER(10),
    CUM_AMOUNT NUMBER(10), ...
    This site thinks something is naughty here, too.
    As an abbrevialtion for "cumulative", you can use CMLTV.
    ... My requirement is that, every time I use a document number in the where clause for the view it should show the paid amount against the student names who has paid on that month
    Which month?
    Do you mean that, when you use the query at any point in March, 2013, it should only include payments for March, 2013? To do that, use SYSDATE in the query.
    and for all other student it should null. Total student number is 13. So every time it should show 13 students only even when I pass document No 2 or 3.
    It's unclear what you want the view to do, and what you want queries that reference the view to do.
    A view can depend on SYSDATE, so it's fairly easy to create a view that automatically produces different results every month (for example).
    It's more difficult to make a view depend on user input; that's usually done in querioes referencing the view. If you really need a view that depends on user input (e.g., a set of document numbers), then you should probably create a table (perhaps a global temporary table) to hold the user input. The view can reference that table.

    Edited by: Frank Kulash on Mar 27, 2013 10:28 AM
  • 3. Re: Query to create a view using the following tables
    HawkerHunter Newbie
    Currently Being Moderated
    Hi Frank,
    thanks for your reply.

    Don't use Oracle-supplied schemas (such as hr) for your own objects. Create your own scehma for your own tables.
    - I had created the tables in Oracle Supplied schema in my local Oracle XE database as I tried to recreate my problem in my personal laptop. I shall drop those tables once this issue is resolved.

    This site won't display some words that are associated with pornography.
    When posting here, use something else (such as GENDER) for a column denoting "male" or "female".
    - Yes you are correct, I had better named the field as 'Gender'.

    What does the month_name column represent?
    If it indicates a month, independent of any particular year, and you're using values like 'JAN' and 'FEB', then why is it 10 characters long?
    If it indicates a particular month and year (that is, Jan. 2013 is not the same as Jan. 2012), then use DATE, not VARCHAR2.
    - Well its a field the user would use for reference while creating the document. The governing field is 'DOC_NUM'.

    This site thinks something is naughty here, too.
    As an abbrevialtion for "cumulative", you can use CMLTV.
    - Yes, you are right. I should have used a field as CMLTV_AMOUNT (Thanks)

    Well my requirement is:
    a) Initially when there is no data in the 'PAYMENT_HEADER' and 'PAYMENT_DETAILS' tables, my view shall display only those 13 student names from 'STUDENT_DETAILS' table.
    b) Now the user may create the first document and under it, enter the Payment details of first four students. I don't want to unnecessarily insert all the 13 student ids in the 'PAYMENT_DETAILS' table under document 1. In the 'PAYMENT_DETAILS' table I would just insert the payment details of the four students. So my view should show the names of all the 13 students from 'STUDENT_DETAILS' table of the left side and on the right side it should show the payment details of the four students and NULL for other students.
    c) Similarly under next document number, there shall be some more student's payment details. So in my view when I use the where clause with doc_number = 2, it should show all the 13 students name on the left and on the right it should show the payment details for those students who has paid and NULL values for other students.

    Hope I have been able to express my requirement a little more clearly now.
    Thanks once again.
    Regards
    Hawker
  • 4. Re: Query to create a view using the following tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Hawker,
    HawkerHunter wrote:
    ... Well my requirement is:
    a) Initially when there is no data in the 'PAYMENT_HEADER' and 'PAYMENT_DETAILS' tables, my view shall display only those 13 student names from 'STUDENT_DETAILS' table.
    b) Now the user may create the first document and under it, enter the Payment details of first four students. I don't want to unnecessarily insert all the 13 student ids in the 'PAYMENT_DETAILS' table under document 1. In the 'PAYMENT_DETAILS' table I would just insert the payment details of the four students. So my view should show the names of all the 13 students from 'STUDENT_DETAILS' table of the left side and on the right side it should show the payment details of the four students and NULL for other students.
    c) Similarly under next document number, there shall be some more student's payment details. So in my view when I use the where clause with doc_number = 2, it should show all the 13 students name on the left and on the right it should show the payment details for those students who has paid and NULL values for other students.

    Hope I have been able to express my requirement a little more clearly now.
    It's a little clearer now, but not much.

    Please post what you want the view to look like, given the sample data you posted. That is, post the results you would want to see from
    SELECT    *
    FROM      student_payment  -- or whatever you want to name the view
    ORDER BY  student_id
    ,         doc_num
    ;
    If that's too much to post, remove some rows from the sample data, and post INSERT statements for the new sample data. You can probably show what you want with about 4 students.
    I assume the view should contain all the columns that were in the result set of the queries you posted earlier. Again, if you want to cut down on the amount you have to post, you can omit some columns, such as dob and gender, for now. Once we have a working view, it should be easy to include all the columns again.

    Do you not know what the view itself is supposed to look like? For example, do you know what the output of
    SELECT    *
    FROM      student_payment  -- or whatever you want to name the view
    WHERE     doc_num   IN (1, 4)
    ORDER BY  student_id
    ,         doc_num
    ;
    should look like, with the sample data given, but not know what the full view contains? If so, post a couple of examples iof different queries, and the results you want from each of those queries.

    Do you want a view that will produce exactly 13 rows of output (assuming there are 13 rows in the student_details table), regardless of how you query it? That's impossible. You can write queries that produce exactly 13 rows of output, but they will have to be more complicated that just changing conditions in a WHERE clause.
  • 5. Re: Query to create a view using the following tables
    HawkerHunter Newbie
    Currently Being Moderated
    Hi Frank,
    as per your advice, I am omitting the 'DOB', 'GENDER','ACTIVE','CONTACT_NUMBER' fields from the 'STUDENT_DETAILS' table.
    I shall create separate tables and insert the desired output from the view for each where clause.

    First let us create three tables each for the desired out put:
    1) For the desired output I want to see when I pass doc_num as 1
    --***************************************** 
    create table HR.SDT_PAY_DET_DOC_ONE (
    STUDENT_ID NUMBER(10),
    STUDENT_NAME VARCHAR2(50),
    DOC_NUM NUMBER(5),
    C_AMOUNT NUMBER(10),
    primary key(STUDENT_ID)
    );
    --*****************************************
    2) For the desired output I want to see when I pass doc_num as 2
    --*******************************************
    create table HR.SDT_PAY_DET_DOC_TWO (
    STUDENT_ID NUMBER(10),
    STUDENT_NAME VARCHAR2(50),
    DOC_NUM NUMBER(5),
    C_AMOUNT NUMBER(10),
    primary key(STUDENT_ID)
    );
    --******************************************
    3) For the desired output I want to see when I pass doc_num as 3
    --********************************************
    create table HR.SDT_PAY_DET_DOC_THREE (
    STUDENT_ID NUMBER(10),
    STUDENT_NAME VARCHAR2(50),
    DOC_NUM NUMBER(5),
    C_AMOUNT NUMBER(10),
    primary key(STUDENT_ID)
    );
    --******************************************

    Now I shall insert values in each of the above three tables:
    --**********************************************
    INSERT ALL
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1112,'James',1,40)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1113,'David',1,40)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1114,'Michael',1,40)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1115,'Joseph',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1116,'Juliet',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1117,'Monica',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1118,'William',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1119,'Karen',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1120,'Erika',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1121,'Noah',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1122,'Angelina',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1123,'Gabriela',null,null)
    INTO HR.SDT_PAY_DET_DOC_ONE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1124,'Sofia',null,null)
    SELECT * FROM DUAL;
    --*********************************************
    INSERT ALL
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1112,'James',2,80)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1113,'David',2,80)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1114,'Michael',2,80)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1115,'Joseph',2,40)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1116,'Juliet',2,40)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1117,'Monica',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1118,'William',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1119,'Karen',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1120,'Erika',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1121,'Noah',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1122,'Angelina',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1123,'Gabriela',null,null)
    INTO HR.SDT_PAY_DET_DOC_TWO(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1124,'Sofia',null,null)
    SELECT * FROM DUAL;
    --********************************************
    INSERT ALL
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1112,'James',3,120)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1113,'David',3,120)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1114,'Michael',3,120)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1115,'Joseph',3,80)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1116,'Juliet',3,80)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1117,'Monica',3,40)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1118,'William',3,40)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1119,'Karen',null,null)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1120,'Erika',null,null)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1121,'Noah',null,null)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1122,'Angelina',null,null)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1123,'Gabriela',null,null)
    INTO HR.SDT_PAY_DET_DOC_THREE(STUDENT_ID,STUDENT_NAME,DOC_NUM,C_AMOUNT) VALUES (1124,'Sofia',null,null)
    SELECT * FROM DUAL;
    --*********************************************

    Thanks & Regards
    Hawker
  • 6. Re: Query to create a view using the following tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    What will be the contents of the view? Post what you expect the result set of
    SELECT  *
    FROM    view_name;
    to be, given that sample data.

    As long as I don't know where you want to go, I can't give you very good directions.
  • 7. Re: Query to create a view using the following tables
    HawkerHunter Newbie
    Currently Being Moderated
    Hi Frank,

    suppose the name of my view is STUDENT_PAY_DETAILS, then when I query as:
    select * from STUDENT_PAY_DETAILS where doc_num = 1, then it should return what you have inserted in the table HR.SDT_PAY_DET_DOC_ONE with the script I had provided in my last message.

    When I quary as :
    select * from STUDENT_PAY_DETAILS where doc_num = 2, then it should return what you have inserted in the table HR.SDT_PAY_DET_DOC_TWO with the script I had provided in my last message.

    when I quary as:
    select * from STUDENT_PAY_DETAILS where doc_num = 3, then it should return what you have inserted in the table HR.SDT_PAY_DET_DOC_THREE with the script I had provided in my last message.

    Regards
    Hawker
  • 8. Re: Query to create a view using the following tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    This might be as close as you can come:
    CREATE OR REPLACE VIEW     student_pay_details
    AS
    WITH   payment     AS
    (
         SELECT  pd.student_id
         ,     pd.line_id
         ,     pd.c_amount
         ,     ph.pay_header_id
         ,     ph.doc_num
         FROM     payment_details      pd
         JOIN     payment_header      ph   ON   ph.pay_header_id      = pd.header_id
    )
    SELECT     s.student_id
    ,     s.student_name
    ,     NVL2 ( p.student_id
              , p.doc_num
              , NULL
              )          AS d_doc_num
    ,     p.c_amount
    ,     p.doc_num
    FROM           student_details  s
    LEFT OUTER JOIN  payment         p   PARTITION BY (p.doc_num)
                                       ON    p.student_id   = s.student_id
    ;
    You'll notice that there are 2 doc_num columns.
    The column called d_doc_num corresponds to that is actually in the payment_header table,. It will be NULL if a given student is not related to that doc_num.
    The column called doc_num is suitable for searching. It is never NULL. For example, a typical query is:
    SELECT       *
    FROM       student_pay_details
    WHERE       doc_num     = 3
    ORDER BY  student_id
    ;
    This query returns 13 rows, 1 for each studnet in the student_details table, whether or not that student is related to doc_num=3. That is:
    STUDENT_ID STUDENT_NA  D_DOC_NUM C_AMOUNT    DOC_NUM
    ---------- ---------- ---------- -------- ----------
          1112 James               3      120          3
          1113 David               3      120          3
          1114 Michael             3      120          3
          1115 Joseph              3       80          3
          1116 Juliet              3       80          3
          1117 Monica              3       40          3
          1118 William             3       40          3
          1119 Karen                                   3
          1120 Erika                                   3
          1121 Noah                                    3
          1122 Angelina                                3
          1123 Gabriela                                3
          1124 Sofia                                   3
    Look at the last student, Sofia, for example. There is nothing at all related to her in the payment tables, yet she appears in the view results with doc_num=3; not because she is related to doc_num=3, but merely because we asked for doc_num=3.

    This view will be good for searching for specific doc_nums, but I'm not sure if it will be useful for anyhting else. (That may not be a oproblem for you.)
    Another approach is a parameterized view, where, before running any query, you store parameters (such as doc_nums wanted) somewhere, e.g. in a global temporary table. The view takes those stored parameters into account, so you usually wouldn't use a WHERE clause with the view; you would set parameters instead. While harder to set up, this approach would be more versatile, and perhaps more efficient, too.
  • 9. Re: Query to create a view using the following tables
    HawkerHunter Newbie
    Currently Being Moderated
    Hi Frank,

    thanks a lot. The query you have sent to set up my view fits my requirement very well.
    I have really learnt from you how to use 'PARTITION BY' for such cases.
    Frank I really appreciate the time you took to understand my problem and try to help me.

    Once again I thank you for helping me.

    "Another approach is a parameterized view, where, before running any query, you store parameters (such as doc_nums wanted) somewhere, e.g. in a global temporary table. The view takes those stored parameters into account, so you usually wouldn't use a WHERE clause with the view; you would set parameters instead. While harder to set up, this approach would be more versatile, and perhaps more efficient, too."

    - Can we use those stored parameters anywhere in our query? Can you please let me know any link from where I can learn more about this?

    Sincere Regards
    Hawker
  • 10. Re: Query to create a view using the following tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Hawker,

    Here's an example of what I was calling a parameterized view.
    Once again, the idea is to store the parameters in a table. If you make this a global temporary table, then multiple sessions can use the view at the same time, each with its own set of parameters.
    CREATE GLOBAL TEMPORARY TABLE target
    (   doc_num   NUMBER (5)
    )
    ON COMMIT PRESERVE ROWS
    ;
    
    INSERT INTO target (doc_num) VALUES (1);
    INSERT INTO target (doc_num) VALUES (3);
    Here's one way to create a view that is based on that parmeter table, as well as your regular tables:
    CREATE OR REPLACE VIEW     student_pay_2
    AS
    WITH   payment     AS
    (
         SELECT  pd.student_id
         ,     pd.line_id
         ,     pd.c_amount
         ,     ph.pay_header_id
         ,     ph.doc_num
         FROM     payment_details      pd
         JOIN     payment_header      ph   ON   ph.pay_header_id      = pd.header_id
         JOIN     target           t    ON   t.doc_num           = ph.doc_num
    )
    SELECT     s.student_id
    ,     s.student_name
    ,     p.c_amount
    ,     p.doc_num
    FROM           student_details  s
    LEFT OUTER JOIN  payment         p   ON    p.student_id   = s.student_id
    ;
    This view will include at least one row per student, regardless of what's in the other tables. If any given student happens to be related to 2 (or more) doc_nums in the other tables (inclding target), then the view will contain 2 (or more) rows for that student, each with a different doc_num. (See James, below.)

    You might use the view like this:
    SELECT       *
    FROM       student_pay_2
    ORDER BY  student_id
    ,            doc_num
    ;
    Output:
    STUDENT_ID STUDENT_NAME   C_AMOUNT    DOC_NUM
    ---------- ------------ ---------- ----------
          1112 James                40          1
          1112 James               120          3
          1113 David                40          1
          1113 David               120          3
          1114 Michael              40          1
          1114 Michael             120          3
          1115 Joseph               80          3
          1116 Juliet               80          3
          1117 Monica               40          3
          1118 William              40          3
          1119 Karen
          1120 Erika
          1121 Noah
          1122 Angelina
          1123 Gabriela
          1124 Sofia
    This kind of view will often be used without a WHERE clause, because filtering based on the parameters is done in the view itself, not in the queries that use the view.
  • 11. Re: Query to create a view using the following tables
    HawkerHunter Newbie
    Currently Being Moderated
    Hi Frank,
    I think using a temporary table is a good idea. I shall give it a try. Thanks for stating the example. I need to explore more into it.
    Meanwhile if I use the first method using 'PARTITION BY' then in the STUDENT_DETAILS table if I have different sets of students based on school branch wise, and also the doc_num in the PAYMENT_HEADER are also maintained as per school branch then the 'PARTITION BY' partitions the data based on highest document number.

    Suppose in School Branch A I have 8 documents created so far and in School Branch B I have 2 documents created so far, then in my query if I give the filter as school Branch B, it partitions for 8 sets of student names for school B but with null values. Can you please let me know how we can restrict this.

    Regards
    Hawker
  • 12. Re: Query to create a view using the following tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Hawker,
    HawkerHunter wrote:
    ... Meanwhile if I use the first method using 'PARTITION BY' then in the STUDENT_DETAILS table if I have different sets of students based on school branch wise, and also the doc_num in the PAYMENT_HEADER are also maintained as per school branch then the 'PARTITION BY' partitions the data based on highest document number.
    No. "PARTITION BY" applies to all values: the highest, the lowest, and everything in between.
    Suppose in School Branch A I have 8 documents created so far and in School Branch B I have 2 documents created so far, then in my query if I give the filter as school Branch B, it partitions for 8 sets of student names for school B but with null values. Can you please let me know how we can restrict this.
    Sorry, it's not clear what you're asking.
    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements) that shows the situation, and also post the results you want from that data.
  • 13. Re: Query to create a view using the following tables
    HawkerHunter Newbie
    Currently Being Moderated
    Hi Frank,
    I shall post the data shortly.
    Thanks & Regards
    Hawker

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points