HawkerHunter wrote:Thanks, that's very helpful!
... I am pasting the following script to create the required tables and also insert appropriate data in each of the table.
--*******************************************Don't use Oracle-supplied schemas (such as hr) for your own objects. Create your own scehma for your own tables.
create table HR.STUDENT_DETAILS (
STUDENT_ID NUMBER(10),This site won't display some words that are associated with pornography.
ACTIVE CHAR(1),What does the month_name column represent?
create table HR.PAYMENT_HEADER (
primary key(PAY_HEADER_ID)This site thinks something is naughty here, too.
create table HR.PAYMENT_DETAILS (
CUM_AMOUNT NUMBER(10), ...
... 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 monthWhich 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.It's unclear what you want the view to do, and what you want queries that reference the view to do.
HawkerHunter wrote:It's a little clearer now, but not much.
... 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.
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.
SELECT * FROM student_payment -- or whatever you want to name the view 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.
SELECT * FROM student_payment -- or whatever you want to name the view WHERE doc_num IN (1, 4) ORDER BY student_id , doc_num ;
You'll notice that there are 2 doc_num columns.
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 ;
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:
SELECT * FROM student_pay_details WHERE doc_num = 3 ORDER BY student_id ;
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.
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
Here's one way to create a view that is based on that parmeter table, as well as your regular tables:
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);
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.)
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 ;
SELECT * FROM student_pay_2 ORDER BY student_id , doc_num ;
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.
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
HawkerHunter wrote:No. "PARTITION BY" applies to all values: the highest, the lowest, and everything in between.
... 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.Sorry, it's not clear what you're asking.