| View is not updated. [message #649250] |
Sat, 19 March 2016 03:47  |
shahzad-ul-hasan
Messages: 639
Registered: August 2002
|
Senior Member |
|
|
Please find the view structure of the data: How i can update the view.
SQL> desc vie --This is the view of the tables...
Name Null? Type
----------------------------------------- -------- -------------------------
PERC NUMBER
POS NUMBER
CLASS VARCHAR2(25)
SEC VARCHAR2(50)
EXAMC VARCHAR2(80)
STUID NOT NULL NUMBER(8)
SQL> desc student
Name Null? Type
----------------------------------------- -------- ------------------------
STUID NOT NULL NUMBER(8)
STATUS VARCHAR2(30)
CLASS VARCHAR2(25)
SECTION VARCHAR2(30)
SESION VARCHAR2(30)
ROLLNO NUMBER(4)
ADM_NO VARCHAR2(25)
STUDENTID VARCHAR2(15)
NAME VARCHAR2(250)
F_NAME VARCHAR2(250)
SQL> desc test --Master table with stores class, total marks and subject information
Name Null? Type
----------------------------------------- -------- -------------------
TID NOT NULL NUMBER(9)
TDATE NOT NULL DATE
CLASS VARCHAR2(30)
EXAMC VARCHAR2(80)
SUBJECT VARCHAR2(70)
TOTMARKS NUMBER(3)
SEC VARCHAR2(50)
TYEAR VARCHAR2(22)
SQL> desc test1 --Detail table stores students obtained marks
Name Null? Type
----------------------------------------- -------- -------------------------
TID NUMBER(9)
TDATE DATE
TSTUID NUMBER(7)
OBTMARKS NUMBER(5,2)
PERCT NUMBER(6,2)
REMARK VARCHAR2(300)
SQL> desc class ----Stores only class Names (Master)
Name Null? Type
----------------------------------------- -------- ----------------------------
CNAME NOT NULL VARCHAR2(20)
SQL> desc sub ---Foreign key with class Stores only subject of class
Name Null? Type
----------------------------------------- -------- -----------------------
CNAME VARCHAR2(20)
SNO NUMBER(2)
SUBJECT VARCHAR2(80)
the all data stores in that tables. when i per-mote(move) the classes of the students the data is not showing in reports only otherwise data is present in table and showing i SQL*Plus. but in reports not showing.. PLEASE ADVISED.
[Updated on: Sat, 19 March 2016 03:47] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: View is not updated. [message #649532 is a reply to message #649284] |
Wed, 30 March 2016 01:59  |
shahzad-ul-hasan
Messages: 639
Registered: August 2002
|
Senior Member |
|
|
SQL> select * from vie;
PERC POS CLASS SEC EXAMC
---------- ---------- ------------------------- -------------------------------------------------- -
15 1 PREP BUTTERFLY DECEMBER ASSESSMENT 2015
31.8 1 PREP BUTTERFLY FINAL TERM 2016
33.3333333 1 PREP BUTTERFLY OCTOBER ASSESSMENT2015
76.6666667 1 PREP FLOWERS APRIL ASSESSMENT 2015
70.8 1 PREP FLOWERS FINAL TERM 2016
33.3333333 1 PREP FLOWERS HOLIDAYS WORK 2015
69.1666667 1 PREP FLOWERS MAY ASSESSMENT 2015
66.8 1 PREP FLOWERS MID TERM EXAMS 2015
68.3333333 1 PREP FLOWERS OCTOBER ASSESSMENT2015
45.8333333 1 PREP FLOWERS DECEMBER ASSESSMENT 2015
21.6666667 2 PREP FLOWERS APRIL ASSESSMENT 2015
PERC POS CLASS SEC EXAMC
---------- ---------- ------------------------- -------------------------------------------------- -
55.6 2 PREP FLOWERS FINAL TERM 2016
50.8333333 2 PREP FLOWERS MAY ASSESSMENT 2015
35 2 PREP FLOWERS OCTOBER ASSESSMENT2015
59.2 2 PREP FLOWERS MID TERM EXAMS 2015
0 2 PREP FLOWERS HOLIDAYS WORK 2015
36.6666667 2 PREP FLOWERS DECEMBER ASSESSMENT 2015
23 1 ONE BUTTERFLY APRIL ASSESSMENT 2015
23.6666667 1 ONE BUTTERFLY DECEMBER ASSESSMENT 2015
90 1 ONE BUTTERFLY HOLIDAYS WORK 2015
35.7142857 1 ONE BUTTERFLY FINAL TERM 2016
18.3333333 1 ONE BUTTERFLY OCTOBER ASSESSMENT2015
PERC POS CLASS SEC EXAMC
---------- ---------- ------------------------- -------------------------------------------------- -
54.4285714 1 ONE BUTTERFLY MID TERM EXAMS 2015
39 1 ONE BUTTERFLY MAY ASSESSMENT 2015
36.6666667 1 ONE FLOWERS APRIL ASSESSMENT 2015
94 1 ONE FLOWERS HOLIDAYS WORK 2015
11.6666667 1 ONE FLOWERS OCTOBER ASSESSMENT2015
45.1428571 1 ONE FLOWERS MID TERM EXAMS 2015
52.3333333 1 ONE FLOWERS MAY ASSESSMENT 2015
12.6666667 1 ONE FLOWERS DECEMBER ASSESSMENT 2015
if the ONE class permote to next class TWO then its record in view remains ONE and class of the student table is TWO. please advised.
here is main report query.
SELECT DISTINCT SUB.SNO, round(TEST1.OBTMARKS) OBTMARKS, TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS||'/'||TEST.SEC See, SUB.SUBJECT, STUDENT.STUID, ' '||STUDENT.NAME DIS,VIE.POS,vie.perc
FROM SUB, TEST1, TEST, STUDENT, CLASS,VIE
WHERE (STUDENT.STATUS = 'PRESENT'
AND TEST.EXAMC = :ty
AND STUDENT.CLASS = :cls
AND STUDENT.SECTION = :ssec)
AND SUB.CNAME = CLASS.CNAME
AND TEST.CLASS = CLASS.CNAME
AND TEST1.TDATE = TEST.TDATE
AND TEST1.TID = TEST.TID
AND VIE.CLASS=:CLS
AND VIE.SEC=:SSEC
and vie.stuid=test1.tstuid
AND VIE.EXAMC=:TY
AND (STUDENT.STUID = TEST1.TSTUID)
AND (TEST.SUBJECT = SUB.SUBJECT)
ORDER BY SUB.SNO
[Updated on: Wed, 30 March 2016 02:01] Report message to a moderator |
|
|
|
|
|
| Re: View is not updated. [message #649536 is a reply to message #649533] |
Wed, 30 March 2016 03:06 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Views (apart from materialized views) don't store data.
They are stored queries, nothing more.
Whenever you query a view oracle runs the view query against the underlying tables and returns all the data that currently matches the view query.
So views don't get updated, that's a meaningless concept.
If the view is returning data that you think is wrong then either:
a) The view query is wrong.
b) The table data isn't what you think it is.
We have no way of knowing which of those is the problem since we don't know:
a) what the view query is.
b) what the data in the tables is.
c) what output you think you should be getting.
After the amount of questions you've asked you really ought to know the amount of information others would need to help you.
Also your view output seems to be incomplete - it doesn't have the stuid column referred to the report query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
