f



View to view SQL

I'm not very good with SQL and would appreciate some advice.

I have a view in 8.1.7.4 that retrieves nearly a hundred fields from five
different tables.  It's long but simple and straightforward, and has been
working quite well for some time.  I've just been told that I now need to
also retrieve some SUMs and COUNTs from fields in another table.  Rather
than modifying this view with 100 Group BYs, I wonder if it is feasible to
create another view that gets the SUMs and COUNTs and then add that view's
output fields to this view's?  Is that a smart way to approach this problem?
Is there a better?

Thanks in advance,
Randy Harris


0
Randy
10/31/2003 8:12:53 PM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

3 Replies
964 Views

Similar Articles

[PageSpeed] 32

just covered this with a colleage...

select
    ename
    , job
    , hiredate
    , emp.deptno
    , sal
    , ttl_sals
from
    emp
    , (
        select deptno, sum(sal) as ttl_sals
        from emp
        group by deptno
        ) empsals
where
    emp.deptno = empsals.deptno


-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048


"Randy Harris" <randy@SpamFree.com> wrote in message
news:9nzob.6741$P%1.5602532@newssvr28.news.prodigy.com...
> I'm not very good with SQL and would appreciate some advice.
>
> I have a view in 8.1.7.4 that retrieves nearly a hundred fields from five
> different tables.  It's long but simple and straightforward, and has been
> working quite well for some time.  I've just been told that I now need to
> also retrieve some SUMs and COUNTs from fields in another table.  Rather
> than modifying this view with 100 Group BYs, I wonder if it is feasible to
> create another view that gets the SUMs and COUNTs and then add that view's
> output fields to this view's?  Is that a smart way to approach this
problem?
> Is there a better?
>
> Thanks in advance,
> Randy Harris
>
>


0
mcstock
10/31/2003 8:29:30 PM
I think it is a good idea to create a new view altogether.

For the simple reason, another view creation is not going to occupy any
space as it is a stored query.
Secondly, for the queries which are already using the existing view might
see a difference in performance after you modify
the existing view definition.

If the existing view does not have any aggregate functions then it is all
the most good reason to create a new view.

Let us know how it works

bye,Lakshmi


"Randy Harris" <randy@SpamFree.com> wrote in message
news:9nzob.6741$P%1.5602532@newssvr28.news.prodigy.com...
> I'm not very good with SQL and would appreciate some advice.
>
> I have a view in 8.1.7.4 that retrieves nearly a hundred fields from five
> different tables.  It's long but simple and straightforward, and has been
> working quite well for some time.  I've just been told that I now need to
> also retrieve some SUMs and COUNTs from fields in another table.  Rather
> than modifying this view with 100 Group BYs, I wonder if it is feasible to
> create another view that gets the SUMs and COUNTs and then add that view's
> output fields to this view's?  Is that a smart way to approach this
problem?
> Is there a better?
>
> Thanks in advance,
> Randy Harris
>
>


0
Lakshmi
11/6/2003 2:03:32 AM
"Lakshmi Jagarlapudi" <jlnarayana@comcast.net> wrote in message
news:UZhqb.87254$9E1.440727@attbi_s52...
> I think it is a good idea to create a new view altogether.
>
> For the simple reason, another view creation is not going to occupy any
> space as it is a stored query.
> Secondly, for the queries which are already using the existing view might
> see a difference in performance after you modify
> the existing view definition.

That is a very good point.  Thanks.

> If the existing view does not have any aggregate functions then it is all
> the most good reason to create a new view.
>
> Let us know how it works
>
> bye,Lakshmi
>
>
> "Randy Harris" <randy@SpamFree.com> wrote in message
> news:9nzob.6741$P%1.5602532@newssvr28.news.prodigy.com...
> > I'm not very good with SQL and would appreciate some advice.
> >
> > I have a view in 8.1.7.4 that retrieves nearly a hundred fields from
five
> > different tables.  It's long but simple and straightforward, and has
been
> > working quite well for some time.  I've just been told that I now need
to
> > also retrieve some SUMs and COUNTs from fields in another table.  Rather
> > than modifying this view with 100 Group BYs, I wonder if it is feasible
to
> > create another view that gets the SUMs and COUNTs and then add that
view's
> > output fields to this view's?  Is that a smart way to approach this
> problem?
> > Is there a better?
> >
> > Thanks in advance,
> > Randy Harris
> >
> >
>
>


0
Randy
11/7/2003 5:18:24 AM
Reply:

Similar Artilces:

Why cann't I use Oracle database view the same way as SAS view?
All great SAS-Ls, When I submit the following code in SAS, I get an error message in the log, and no data set is created. Here oralib is a library to a oracle database, and test_results_v is a view in this oracle database. The log messages are also included below. Can anybody explain why I cann't treat orale database view the same way as we usually work on normal sas data step view? Is there any way to get information from an oracle database view?? Any help is great appreciated! Juan data one; set oralib.test_results_v ; run; ERROR: Invalid sequence of commands for file ORALIB.TEST...

view works, but the sql from the view does not
I was looking through our vendors views, searching for something I needed for our Datawarehouse and I came across something I do not understand: I found a view that lists data when I use it in t-sql, however when I try to use the statement when I modified the view (via MS SQL Server Management Studio) I can not execute the statement. I get The column prefix 'dbo.tbl_5001_NumericAudit' does not match with a table name or alias name used in the query. Upon closer inspection, I found two ON for the inner join, which I dont think is correct. So, how can the view work, but not the SQL tha...

Differences between SAS views and SQL views
hello all, for MANY reasons we are moving our SQL views on Oracle to SAS views. but see the differences between that two codes, 'drop' and 'rename' statements are not processed in the same order... Xavier. proc sql; create table TEST (VISIT NUM, VISCOD NUM, TOTO NUM); quit; proc sql; create view TEST1 as select * from TEST; quit; data TEST2/view=TEST2; set TEST; run; proc sort data=TEST1(drop=VISIT rename=(VISCOD=VISIT)) out=SEE1; by TOTO; run; proc sort data=TEST2(drop=VISIT rename=(VISCOD=VISIT)) out=SEE2; by TOTO; run; ...

Are embedded views (Views within views...) evil and if so why?
Fellow database developers, I would like to draw on your experience with views. I have a database that includes many views. Sometimes, views contains other views, and those views in turn may contain views. In fact, I have some views in my database that are a product of nested views of up to 6 levels deep! The reason we did this was. 1. Object-oriented in nature. Makes it easy to work with them. 2. Changing an underlying view (adding new fields, removing etc), automatically the higher up views inherit this new information. This make maintenance very easy. 3. These nested views are only ...

SQL Views
Hi guys, I've been asked to re-write a sql view. The view itself contains several calls to other views (embedded). Is there a way to get around using embedded views. I've written the same query up using temp. tables but obviously temp. tables can't be used in views? Is there any special things I should be looking for? (colabus@gmail.com) writes: > I've been asked to re-write a sql view. The view itself contains > several calls to other views (embedded). Is there a way to get around > using embedded views. I've written the same query up using temp. > tables ...

Re: Differences between SAS views and SQL views
Xavier, Yes, but proc sort data=TEST1 out=SEE1(drop=VISIT rename=(VISCOD=VISIT)); by TOTO; run; proc sort data=TEST2 out=SEE2(drop=VISIT rename=(VISCOD=VISIT)); by TOTO; run; From a recent SAS notes message it became clear that they fixed the 15 year cheat of DROP and KEEP on input being shifted to OUTPUT (V6) by making a work copy of the file using the _DATA_ option as shown by the log: 57 data w ; x = 1 ; y = 2 ; run ; NOTE: The data set WORK.W has 1 observations and 2 variables. NOTE: DATA statement used: real time 0.04 seconds cpu time 0.01 se...

How to view the converted sql from Activerecord method in view
Hi All, I will be happy if someone clear doubt, i can see objects in view by using <%= debug @object %> and lot of methods is there apart from view like to_yml, etc Is there any method available for seeing the converted sql from ActiveRecord method in view, etc. Although I can find it in console but it will confuse when we run multiple queries.. example: User.find :all it will produce "SELECT * FROM users;" in output console But i want it in view are any other specific point like yml , etc ? Thanks, Jak -- Posted via http://www.ruby-for...

Can I have a view of views?
I have a single form which has six pairs of fields on it. Each pair lists a pharmacy and whether or not it has been contacted. It's easy to make a view where the pairs are columns. Patient Pharm 1 Pharm 1 Contacted Pharm 2 Pharm 2 Contacted...etc Jane D CVS Yes Walgreens No Can I make a view, a la a relational database, where there is but a single pair of columns and multiple entries are listed by the pair? Concatenating the fields within a single form with ";" is not quite what I want. Can I create individual ...

View with a query to an oracle database
Hi, In our environment a database view is based on columns in a table from an oracle database. This is a linked server I believe. I am told that since we use this construction no where clause is possible. So we get to many rows in our database which we have to filter later on. How can we do this differently ?? Since it takes a lot of time to go through this process. Bye, Arno de Jong, The Netherlands. On Fri, 14 Nov 2003 09:15:56 +0100, "A.M. de Jong" <arnojo@wxs.nl> wrote: >Hi, > >In our environment a database view is based on columns in a table from an >o...

To View or not to View, that is the question...
In my old MDB databases, I constructed a lot of "subQueries" to filter out records, then based a new query on the subQuery. This results in huge speed increases on large datasets. However... In Accesss2000 ADP Project with SQL Server backend: Is there any benefit to creating Views, then basing a Stored Procedure on that View, rather than on the tables/table joins themselves? Since I can't pass parameters to a View, all I can really do with them is to create simple "=" type criteria. In reading, I've noticed people mention that views are not cached on SQL Server so t...

Oracle DB to MS Sql Server DB Replication question...via Sql Server Views??
Hi, I'm not sure if this is even possible, but I've been asked to look into replication from Oracle to Sql Server. He's the situation: We have access to a remote Oracle database, but only read access (not allowed to configure or change the db). We own a Sql Server database with Views that use a link server to query the Oracle database. With this setup, is it possible to replicate the Oracle database? Can we do a replication from the Sql Server with the Views into another Sql Server database, and if we can, will it be able to only pull the delta for subsequent replications? Any...

views dependent on other views
Hello There, I'm trying to create a view that has calculations dependent on calculations, where the problem resides is that each time I make a calculation I must create an intermediate view so I can reference a previous calculation. for example lets say I have my_table that has columns a & b. now I want a view that has a & b, c = a + b, and d = c + 1. this is grossly simplified, the calculations I actually use are fairly complex and copying / pasting them is out of the question. so what I have is my_view_a which makes column c, and my my_view_final which makes column d (however...

Oracle/SQL
I have a table with userid, date and value. I currently have a view 'monthly' which aggregates these as follows: create view monthly as select userid, trunc(date,'MON') Date, avg(value) MonthlyAverage, count(value) MonthlyCount, max(value) MonthlyMaximum, min(value) MonthlyMinimum, from daily group by userid,trunc(date,'MON'); Which works fine, however I also want to have two columns in the view for 'first' and 'last' (I'm going to invent the aggregates to show what I want): create view monthly as select userid, trunc(date,'MON') date, avg(value) MonthlyAverage, count(value) MonthlyCount, max(value) MonthlyMaximum, min(value) MonthlyMinimum, first(value,date) MonthlyFirstOfMonthValue, last(value,date) MonthlyLastOfMonthValue from daily group by userid,trunc(date,'MON'); Effectively I want (in the view) the minimum,maximum,first and last values for the month. I've tried a subquery, but it will not co-operate because the subquery requires non-group expressions to succeed. Any hints ? Thanks PS: My e-mail address has been hubbled to protect me from spam, feel free to repair it and e-mail me, or reply here. just add new columns into your view: first_value(value) over (partition by userid,trunc(date,'MON') ORDER by date) fv, first_value(value) o...

SAS SQL VIEWS AND ORACLE
I have been working in SAS SQL with large loan files (millions of records) located in an Oracle database all on Solaris. Up to this point, I have been connecting to Oracle with a LIBNAME statement and running DATA steps and SQL statements against the database. Employing a VIEW in place of a TABLE in SQL seems to routinely save 20-30 percent cpu time. A couple of problems have come up, including: o I have been getting syntax errors in SAS, particularly on join requests, that I have trouble diagnosing. How do I get SAS to print out the SQL that it is sending to Oracle (more than just ...

Viewing transformation sample for pilot view and polar view
Hello, I am trying to implement some kind of flight simulator in OpenGL and have some problems with the camera control. I know the basics but I am looking for real sample code of the "pilot view" and "polar view" pseudo code from the red book in the viewing section: void pilotView{GLdouble planex, GLdouble planey, GLdouble planez, GLdouble roll, GLdouble pitch, GLdouble heading) { glRotated(roll, 0.0, 0.0, 1.0); glRotated(pitch, 0.0, 1.0, 0.0); glRotated(heading, 1.0, 0.0, 0.0); glTranslated(-planex, -pla...

switch the way the query opens from a design view to an SQL view
is there an option to switch the way the query opens from a design view to an SQL view without entering to the query befoere? thread wrote: > is there an option to switch the way the query opens from a design > view to an SQL view without entering to the query befoere? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 No. If you want the query to open in SQL view you'll have to save it while that view is on screen. If you want to just see the SQL string w/o opening the query you can do the following: In the Debug window (Ctrl-G) type something like the following: ? currentdb.query...

Details View in Database Window is blank but is fine in other views. This is different than before.
Have a weird problem. When I open my application within access I sometimes have to reset the column widiths in the details view because they reset to zero. When I open the application from access is fine and the columns stay visable. When I open the application from windows explorer and access is opened by file type relation, the columns are reset to zero and I'm unable to reset the columns and there is a grey box in the area where the columns use to be. I have to shut the app down and open it again from within access in order to reset the columns. Any idea what is wrong and how to correct...

Re: SAS SQL VIEWS AND ORACLE
Usually you can see the statements with DESCRIBE VIEW. Try the following simple example: proc sql; create view vtest as select * from sashelp.class where sex="F"; quit; proc sql; describe view vtest; quit; Cannot imagine, that using a pure SAS-view is faster than the SQL on a table. Where should be the reason? The only thing, I could imagine, that you already use the pass-through facility without knowing that? Pass through should nearly always be better than doing the computing in SAS. All the database facilities for performance optimizing should be used with that. Sometimes...

VIEW who works in SQL Server but not in ORACLE
Hello, I need some help, I have a view write into a SQL Server database that I'd like to put on an ORACLE Database The code is SELECT DateTime,Description,(SELECT Description FROM MaTable WHERE DateTime>= T1.DateTime) As DecrNextRow FROM MaTable T1 Can you help me -- Stef "Stef" <s.fauchille@hotmail.com> wrote in message news:423862d7$0$807$8fcfb975@news.wanadoo.fr... > Hello, > > I need some help, > > I have a view write into a SQL Server database that I'd like to put on an > ORACLE Database > > The code is > > SELECT DateTime,Description,(SELECT Description FROM MaTable WHERE > DateTime>= T1.DateTime) As DecrNextRow FROM MaTable T1 > > Can you help me > > -- > Stef > what happened when you tried it on the oracle database? ++ mcs On Wed, 16 Mar 2005 17:46:22 +0100, "Stef" <s.fauchille@hotmail.com> wrote: >The code is > > SELECT DateTime,Description,(SELECT Description FROM MaTable WHERE >DateTime>= T1.DateTime) As DecrNextRow FROM MaTable T1 > >Can you help me And your Oracle version is? No answer without version! -- Sybrand Bakker, Senior Oracle DBA The version is 10g The error message isinvalid column "Sybrand Bakker" <postbus@sybrandb.demon.nl> a �crit dans le message de news: 23ug315p8v5mokd8f1tpo1m311hm92vn4t@4ax.com... > On Wed, ...

VIEW who works in SQL Server but not in ORACL
Thank tou for your answer but it still doesn't work. I said something wrong, it's ORACLE 8i version. Here is the code ( the column Acquit doesn't exist in my table, it's a self made in the request) Sorry for my English prd244pr > SELECT TIMESTMP , TRANSTYPE , TAGNME, DSCRPTN, 2 (SELECT TAGNAME FROM ( 3 SELECT TAGNAME FROM RSV_ALM_LOG WHERE 4 TRANSTYPE = 'Acked' AND TIMESTMP > T1.TIMESTMP 5 AND TAGNAME = T1.TAGNAME) 6 WHERE ROWNUM = 1) AS ACQUIT 7 FROM RSV_ALM_LOG T1; AND TAGNAME = T1.TAGNAME Error at line 5: OAR-00904 : invalid column name Oracle 8i Enterprise Edition Release 8.1.7.4.1 - Production PL/SQL Release 8.1.7.4.0 - Production CORE 8.1.7.2.1 - Production TNS for 32 bit windows : Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production What can I do. Thanks "Stef" <toto@titi.com> wrote in message news:<42396d5f$0$19356$8fcfb975@news.wanadoo.fr>... > Thank tou for your answer but it still doesn't work. I said something wrong, > it's ORACLE 8i version. > Here is the code ( the column Acquit doesn't exist in my table, it's a self > made in the request) > Sorry for my English > > prd244pr > SELECT TIMESTMP , TRANSTYPE , TAGNME, DSCRPTN, > 2 (SELECT TAGNAME FROM ( > 3 SELECT TAGNAME FROM RSV_ALM_LOG WHERE > 4 TRANSTYPE = 'Acked' AND TIMESTMP > T1.TIMESTMP > 5 A...

Why is sorting in the database view bad? (Form Properties > Datasheet View)
Why would one not want to sort records in a form using Form Properties > Datasheet View > Sort Ascending? It's the fastest way, isn't it? <sierrachang@gmail.com> wrote in message news:1130340930.933763.38030@g43g2000cwa.googlegroups.com... > Why would one not want to sort records in a form using Form Properties >> Datasheet View > Sort Ascending? > > It's the fastest way, isn't it? No, the fastest way would be to sort in the Query or SQL you use as Record Source for the Form, I suspect. I don't give the users "datasheet view" i...

VIEW who works in SQL Server but not in ORACLE #2
Thank tou for your answer but it still doesn't work. I said something wrong, it's ORACLE 8i version. Here is the code ( the column Acquit doesn't exist in my table, it's a self made in the request) Sorry for my English prd244pr > SELECT TIMESTMP , TRANSTYPE , TAGNME, DSCRPTN, 2 (SELECT TAGNAME FROM ( 3 SELECT TAGNAME FROM RSV_ALM_LOG WHERE 4 TRANSTYPE = 'Acked' AND TIMESTMP > T1.TIMESTMP 5 AND TAGNAME = T1.TAGNAME) 6 WHERE ROWNUM = 1) AS ACQUIT 7 FROM RSV_ALM_LOG T1; AND TAGNAME = T1.TAGNAME Error at line 5: OAR-00904 : invalid column name Oracle 8i Enterprise Edition Release 8.1.7.4.1 - Production PL/SQL Release 8.1.7.4.0 - Production CORE 8.1.7.2.1 - Production TNS for 32 bit windows : Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production What can I do. Thanks Stef wrote: > Thank tou for your answer but it still doesn't work. I said something wrong, > it's ORACLE 8i version. > Here is the code ( the column Acquit doesn't exist in my table, it's a self > made in the request) > Sorry for my English > > prd244pr > SELECT TIMESTMP , TRANSTYPE , TAGNME, DSCRPTN, > 2 (SELECT TAGNAME FROM ( > 3 SELECT TAGNAME FROM RSV_ALM_LOG WHERE > 4 TRANSTYPE = 'Acked' AND TIMESTMP > T1.TIMESTMP > 5 AND TAGNAME = T1.TAGNAME) > 6 WHERE ROWNUM = 1) AS ACQUIT > 7 FROM RSV_ALM_LOG T1; > AN...

Re: SAS SQL VIEWS AND ORACLE #2
Stephen a.k.a. hiemstra@yahoo.com asks > >In a nutshell, what is the best reference for learning advanced >SAS SQL techniques? > online doc for SAS/Access Interface to RDBMS Oracle specifics, at http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a001386257.htm To see the statements passed to oracle, see the example at http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a000433982.htm#a 002568656 which will probably wrap, so try http://preview.tinyurl.com/uloa3 using the url reduction service from tinyurl.com Good Luck Peter On Tue, 12 Dec 2006 09:25:23 -0800,...

Materialized view set up between Oracle and MS SQL Server
All, I would like to know if we can set up similar to Materized view between Source -->Oracle RDMS replicating site --> MS SQL Server Looks DB link can be created between MS SQL server to Oracle server regards On Tue, 17 May 2011 22:21:15 -0700, Kaparwan wrote: > All, > > I would like to know if we can set up similar to Materized view between > > Source -->Oracle RDMS > > replicating site --> MS SQL Server > > > Looks DB link can be created between MS SQL server to Oracle server > > > regards Golden Gate can do that. -- ht...

Web resources about - View to view SQL - comp.databases.oracle.misc

Hudson County View
The Eye of Hudson County - Your local news source for Jersey City, West New York, Hoboken, Bayonne, North Bergen, Union City, Weehawken, West ...


Riehl World View
Real politics in real time from Washington, DC written for average Americans.

Islamic view of the Last Judgment - Wikipedia, the free encyclopedia
In Islam , Yawm al-Qiyāmah ( Arabic : يوم القيامة ‎ "the Day of Resurrection") or Yawm ad-Din ( Arabic : يوم الدين ‎ "the Day of Judgment ") ...

Earnings vindicate RBA's upbeat view of economy, cooling recession fears
At the halfway point in the reporting season, with some painful exceptions, earnings are holding up surprisingly well.

The Exponential View
... something, I am going to promote something. My partner Albert turned me onto Azeem Azhar ‘s weekly newsletter called The Exponential View . ...

Arista Networks Rising: Q4 Beats, Q1 View Beats
Networking equipment vendor Arista Networks (ANET), which competes with Cisco Systems (CSCO), this afternoon reported Q4 revenue and profit that ...

The majority of IT pros view email as a major security threat
Sixty-five percent of IT professionals don’t feel equipped to defend against email-based cyber attacks, according to the findings of a report ...

View from the left—the LGBT movement's energy deficit
Nothing more plainly revealed the fragility of executive actions than Justice Antonin Scalia’s unexpected departure last weekend. The news at ...

VH1 Assembles ‘Satan’s Sisters’, Scripted Drama Based On Star Jones’ ‘View’-Esque Novel
... Satan’s Sisters , an hourlong scripted drama series based on the 2011 book by Star Jones , one of the original co-hosts of The View . It looks ...

Resources last updated: 2/22/2016 3:42:07 AM