Home » Developer & Programmer » Reports & Discoverer » View is not updated.
View is not updated. [message #649250] Sat, 19 March 2016 03:47 Go to next message
shahzad-ul-hasan
Messages: 561
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 #649251 is a reply to message #649250] Sat, 19 March 2016 05:16 Go to previous messageGo to next message
John Watson
Messages: 8297
Registered: January 2010
Location: Global Village
Senior Member
Can you describe the problem again? I don't understand what you are trying to say.
Re: View is not updated. [message #649255 is a reply to message #649251] Sat, 19 March 2016 09:19 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

I don't understand too. What precise are you doing? Show us!
Re: View is not updated. [message #649257 is a reply to message #649255] Sat, 19 March 2016 14:17 Go to previous messageGo to next message
Littlefoot
Messages: 21575
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Either you don't correctly Quote:

per-mote(move) the classes of the students

or query you wrote (and use in report) is wrong.
Re: View is not updated. [message #649284 is a reply to message #649257] Mon, 21 March 2016 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you commit the changes you made?
Re: View is not updated. [message #649532 is a reply to message #649284] Wed, 30 March 2016 01:59 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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 #649533 is a reply to message #649532] Wed, 30 March 2016 02:04 Go to previous messageGo to next message
John Watson
Messages: 8297
Registered: January 2010
Location: Global Village
Senior Member
What does "permote" mean?

Re: View is not updated. [message #649536 is a reply to message #649533] Wed, 30 March 2016 03:06 Go to previous message
cookiemonster
Messages: 13894
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.
Previous Topic: How to Export EUL of from Version 9.0.4 to 11.1.1.3.0
Next Topic: Reports parameters
Goto Forum:
  


Current Time: Sun Jul 12 21:56:45 CDT 2020