OraFAQ Forum: Forms » to create views from forms

Home » Developer & Programmer » Forms » to create views from forms (oracle 8i with dev 60)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to create views from forms [message #311875]

Mon, 07 April 2008 04:04

orarep
Messages: 56
Registered: September 2007

Member

hello everyone

how to creat view from developer as i need it to make it for report.i.e after making the views i will make the report from that view. any document regarding this problem

please help

regards

orarep

Report message to a moderator

Re: to create views from forms [message #311904 is a reply to message #311875]

Mon, 07 April 2008 06:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is a bad idea. Oracle isn't supposed to work that way. Creating objects online will only bring you trouble. You, as a developer, probably have required privileges to create a view. Do you plan to grant it to all of your users? Because, end user doesn't need to have such a privilege.

I'd suggest you to think it over and avoid creating views during runtime. By the way, what kind of a view is it that you think it must be created that way? Why wouldn't you create it "now" and use "later", possibly with different DEFAULT_WHERE form property (and corresponding WHERE condition in report (which, additionally, may be acquired as a LEXICAL parameter))?

Report message to a moderator

Re: to create views from forms [message #312198 is a reply to message #311875]

Tue, 08 April 2008 03:20

orarep
Messages: 56
Registered: September 2007

Member

sir you are write but i have the following problem which force me to do so.

sir i have a table containig the student roll no and their results of sessional test, class activity and terminal exam and total marks and then final grade.

i need to run the report of individual classes and these resports are order by student roll no and i need to show the serial number as well in that report too i am using ROWNUM for serial number but when i use "order by roll no" then the serail no distorted as it was on the basis of row number in a table

so to avoid this i wanted to make a view of that report query which stores the data 'order by roll no' and then i use ROWNUM to display the serail no.

now if you have an idea please do tell me as i need to change 8 complex report.

thank you

orarep

Report message to a moderator

Re: to create views from forms [message #312202 is a reply to message #312198]

Tue, 08 April 2008 03:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you provide a sample (which would include CREATE TABLE and INSERT INTO statements) so that we could see it? Because, if there's a column in a table which can be used in the ORDER BY clause, there's really no need to create a view during runtime.

Besides, ROWNUM isn't stored in a table. It is a pseudocolumn available in a query. Read something more about it in this article written by Tom Kyte.

It isn't clear what "roll no", "serial no" and "rownum" represent. I'd strongly recommend you to present actual data, query you use and output you get. Then we'll see what next.

Report message to a moderator

Re: to create views from forms [message #312425 is a reply to message #312202]

Tue, 08 April 2008 19:27

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Also, an Oracle view applies to the record structure NOT the record contents. An Oracle view contains NO data.

David

Report message to a moderator

Re: to create views from forms [message #312523 is a reply to message #312202]

Wed, 09 April 2008 02:31

orarep
Messages: 56
Registered: September 2007

Member

ok sir here is the data

i have table with following col

student(std_id,subject,sessional_test,clas_ctivity, total)
and the data it have it as follows

std_id subject sessional_test cals_activity, total
43121 Arts 40 30 70
43221 Arts 30 30 60
40111 Science 40 30 70
39121 Arts 40 30 70
23211 Arts 20 40 60
44310 Science 30 30 60
23230 science 40 20 60

now i wanted to have a rport on the basis of subject and the std_id must be in Asending order so i will use the following query

select std_id ,sessional_test as st,class_activity as CA, total from student where subj='Arts';

i want the output as follows

SNO Std_id ST CA total
1 23211 20 40 60
2 39121 40 30 70
3 43121 40 30 70
4 43221 30 30 60

Now how to generate this SNO

please guide

Report message to a moderator

Re: to create views from forms [message #312532 is a reply to message #312523]

Wed, 09 April 2008 02:50

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Add an 'order by std_id' to the query.

David

Report message to a moderator

Re: to create views from forms [message #312537 is a reply to message #312532]

Wed, 09 April 2008 03:17

orarep
Messages: 56
Registered: September 2007

Member

oh yes i forgot to include it but my problem is how to generate that SNO i can't use rownum for this as after being sorted it will display different row number so how to do that

please help as i ned to change 8 to 9 reoprts and every reoprt contain 12 to 14 sq quries so a complete one month job

regards

Report message to a moderator

Re: to create views from forms [message #312540 is a reply to message #312537]

Wed, 09 April 2008 03:37

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK, here you go: two ways to do that in a query: use ROW_NUMBER analytical function, or use INLINE VIEW.

Here are the examples:

SQL> select row_number() over (order by std_id) rn, std_id, subject
  2  from student
  3  where subject = 'arts'
  4  order by std_id;

        RN     STD_ID SUBJECT
---------- ---------- --------------------
         1      23211 arts
         2      39121 arts
         3      43121 arts
         4      43221 arts

SQL>
SQL> select rownum, std_id, subject
  2  from (select std_id, subject
  3        from student
  4        where subject = 'arts'
  5        order by std_id
  6       );

    ROWNUM     STD_ID SUBJECT
---------- ---------- --------------------
         1      23211 arts
         2      39121 arts
         3      43121 arts
         4      43221 arts

SQL>

Just to mention: there's no need to use the 'total' column. It is a summary of 'sessional test + class activity'. Why would you maintain 'total' value, when it can be easily computed any time (for reporting or any other purposes)? If it was, for example, a data warehouse, then it would make sense. Otherwise - no (in my opinion, of course).

Report message to a moderator

Re: to create views from forms [message #312951 is a reply to message #312540]

Thu, 10 April 2008 03:33

orarep
Messages: 56
Registered: September 2007

Member

Thank you sir my problem is solved and you save my time sir
but now another more commmon problem and that is when i implement the row_number() over (order by std_id) so two of the field which are GROUP ABOVE fields are giving me the error of ' fequency below it 's group ' i don't know why it is happened. this happened earlier as well but you know many errors like this make me very angry and i don't know how to tackle

i don't know that what happend to these fields as i didn't chnage them so please conceptually speaking , what is the cause for such behaviour and how can i correct it or which thing i should be carefull in such cases

please guide

regards

orarep

Report message to a moderator

Previous Topic:	stacked canvas with scrollbar
Next Topic:	when button pressed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 12:33:05 CDT 2025