HDTenEightyP/NetNews huggingface dataset:
From: carl.pedersen@dartmouth.edu (L. Carl Pedersen)
Newsgroups: comp.databases.oracle
Subject: Top N Groups of Something [was Re: Explain THIS, please!]
Date: 28 Dec 92 17:15:32 GMT
Followup-To: comp.databases.oracle
Organization: Dartmouth College

In article <1992Dec23.150326.560@hhcs.gov.au>, geemic@hhcs.gov.au wrote:
> 
> In article <1992Dec22.163411.557@hhcs.gov.au>, champs@hhcs.gov.au writes:
> > In article <1gtd91INN7ln@ef2007.efhd.ford.com>, wwm@ef5003.efhd.ford.com (Bill Meahan) writes:
[stuff omitted]
> > It looks to me like you're trying to retreive the 10 parts with the highest
> > sum(cost).  As far as I can tell it's impossible to retrieve the top 10 groups
> > of anything in a single query.  You can retrieve the top ten of something, but
> > not the top 10 GROUPS of something.  Your only chance is to use PL/SQL cursors

I think it can be done without PL/SQL, if you are willing to use views (and
why not).


 create view part_count1 as
 select part, count(*) count 
   from part_usage_table
  group by part;

 create view part_count as
 select a.part, a.count, count(*) rank
   from part_count1 a, part_count1 b
  where b.count > a.count or
        ( b.count = a.count and 
          b.part <= a.part )
  group by a.part, a.count;


 select part, count from part_count where rank <=10 order by rank;

I tested this with a slightly different table of my own and it worked, so
unless I've made a typo, the final select, above, should give the top ten
most frequently used parts (incidentally, in order of usage).

Note that an arbitrary decision is made:  If two parts have the same
frequency of use, then the one with the lower number is ranked earlier.

I make no claims that this is efficient.  A PL/SQL program might be able to
do it faster.

I originally learned this technique from Martin Jensen at dde in Denmark.
Newsgroups: comp.databases.oracle
From: as@progress.COM (Amitabh  Shukla)
Subject: Multibyte character support
Summary: What standard does Oracle database use in order to support pacific rim loc
Keywords: multibyte data
Organization: Progress Software Corp.
Date: Wed, 30 Dec 1992 20:53:23 GMT

Aloha netters,

		Can anyone tell me what coding scheme is used by oracle to support
multbyte locales? (Japanese and other far eastern market). Is it Shift-JIS, 
UNICODE, EUC or some oracle's internal standard?

Thanks in advance.

Amitabh Shukla
email: as@progress.com
From: wliu@memstvx1.memst.edu
Newsgroups: comp.databases.oracle
Subject: A big table or several small tables?
Date: 21 Dec 92 15:56:23 -0600
Organization: Memphis State University

Hi,
I'm working on a project and use ORACLE(RDBMS6.0.36, SQL*FORM 3.0, SQL*FORM
3.0) on a SUN Sparc Station. As a new comer, I need your help.

SCENARIO:

I have created 53 tables. These tables can be divided into 3 categorieds:

1) 20 Look-Up tables, which have 2-3 columns and less than 20 rows.
2) 27 normal tables, which have 10-15 columns and can have 2000 rows.
3) 6 big tables, which have 50-60 column and can have 4000 rows.

QUESTIONS:

A. To increase the speed of query and insert, do I have to divide  
every big table into 2-3 smaller tables which may have about 20 columns?

B. I will put unique index on most of the tables. When make a join 
query between the Look-Up table and rest of the tables, does the 
index of Look-Up table or the its position in the query statement
make big different regarding the query optimization?

Thand you very much.
From: fgreene@hayes.com
Newsgroups: comp.databases.oracle
Subject: Re: What is Oracle?
Date: 29 Dec 92 08:01:28 EDT
Organization: Hayes Microcomputer Products, Norcross, GA

In article <1992Dec23.161943.7494@eagle.lerc.nasa.gov>, tostan@dumbo.lerc.nasa.gov (Stan Mohler) writes:
> What is Oracle?  I know nothing about database programs.
> 
> -- 
> -----------------------------------------------------------------------------
> Stan Mohler
> NASA Lewis Research Center     	|    
> Cleveland, Ohio  44135         	|     email: tostan@dumbo.lerc.nasa.gov 
> -----------------------------------------------------------------------------

People will talk about relational capabilities, quick response for data 
retrieval, etc., but as far as I am concerned the greatest advantage of
Oracle is its oops! capability.  Not OOPS as in object oriented programming,
but oops! as in 'I made a mistake' or 'the user forgot to tell me.'

For example, in the days of big Cobol systems, remember the nightmare when
the Post Office decided to go to zip+4?  All file definitions had to be
changed, all picture clauses in programs needed to be rewritten, and on and
on and on ...(at this point the bunny should walk across the screen)

In Oracle (and any good relational language) you just say 'oops!', modify
the table definition(s) involved and keep on trucking.  Unless a given form
or report actually used the zip code field, the change was transparent.  
Newsgroups: comp.databases.oracle
From: aaj@cmutual.com.au (Tony Jambu)
Subject: New Oz RDBMS SIG
Keywords: RDBMS SIG Oz Aust 
Organization: Colonial Mutual Group 
Date: Wed, 23 Dec 1992 05:12:25 GMT


TO all Oracle sites in Australia,

Oracle Aust is in the process of setting up a national RDBMS special
interest group (SIG).  It was decided during the last Oracle's Asia Pacific
User Conference in Melbourne that it was about time that an RDBMS SIG be
formed.

As it is in its infancy stage, there are not specific objectives except to
be a contact point where users may want to contact others in the group who
are in similar situation to discuss their problems.

To give you an example, a number of users wanted to contact other sites
that were using Oracle 7.

I believe the marketing division from Oracle Aust has been ringing those
that showed interest in joining this SIG, enquiring

    - whether they wanted to join up,
    - do they use Oracle 7
    - do they mind playing an active role in the SIG
    - do they mind having their names and phone published

If you are interested, I suggest that you contact the Marketing division of
Oracle Aust in Sydney (02) 900-1000.  Sorry, I am unable to be more
specific as I am unable to contact the relevant party to disclose his
name.  If you are interested and having hassles trying to contact the
relevant party, please give me a call (number below) and I will put you in
touch with the right person.

PS.  I will cross-post this to News COMP.DATABASES.ORACLE


Merry Christmas




.  .     .    +   .  .  .        #     #
       .  .  ^^^ .         .     ##   ##  ######  #####   #####    #   #
   .     .  ^^^#^  . .   .       # # # #  #       #    #  #    #    # #
       .   ^^^^^^^.      .       #  #  #  #####   #    #  #    #     #
. .  .   .^^*^^^^^^.  .          #     #  #       #####   #####      #
      .  ^o^^^^^^^^^       .     #     #  #       #   #   #   #      #
  . . . ^^^^^^^^^^^^^ .  .       #     #  ######  #    #  #    #     #
. .    ^^^^^+^^^^*^^^^     .   
   .  ^^^^^*^^^#^^^^^^^  .   .   #     #
  .  ^^^^^^^^^^^^^o^^^^^          #   #   #    #    ##     ####
..  ^^^^^+^^^^^^^*^^^^^^^ .        # #    ##  ##   #  #   #
   ^^^^^^^*^^^+^^^^^^*^^^^.  .      #     # ## #  #    #   ####
. ^^o^^^^^^^^^^^^^^^^^^^^^^        # #    #    #  ######       #
 ^^^^^^^^^^^^^^^^^^^^o^^^^^^      #   #   #    #  #    #  #    #
             III                 #     #  #    #  #    #   ####
             III               
           =======                      and a Happy New Year for 1993
           =======             
                                             from Tony Jambu &

				 Colonial Mutual Investment Management




-- 
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  __       /_ __ /       |Colonial Mutual Invest Mgmt Aust (ACN 004021809)
 /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu@cmutual.com.au
 \_______/                   |PHONE:  +61-3-6418448       FAX:  +61-3-6076198
Xref: sparky comp.databases.oracle:2601 comp.os.os2.apps:9576
From: holowczk@rwja.umdnj.edu (Richard Holowczak)
Newsgroups: comp.databases.oracle,comp.os.os2.apps
Subject: Re: Loading Oracle 5.1A on OS/2 2.0 in a DOS BOX
Date: 22 Dec 92 01:16:18 GMT
Followup-To: comp.databases.oracle
Organization: Robert Wood Johnson Medical School, Piscataway NJ

orville%weyrich@uunet.uu.net (Orville R. Weyrich) writes:

[Tale of Oracle install deleted]

>Has anyone successfully installed this version of Oracle with OS/2 2.0?

>How?

>[Or does anyone have a developer's copy of Oracle that works with OS/2
>for sale real cheap?]

>Thanks for advice,
          
           In an OS/2 article in BYTE magazine, Oracle was one
           application which was said to be incompatible with OS/2 2.0.
           It seem the Oracle PME is not compatible with OS/2 2.0 XMS.
           I have not tried it myself having read the article first.

           Oracle has an OS/2 edition but it is currently only supported
           by OS/2 1.x.


>orville

>--------------------------------------           ******************************
>Orville R. Weyrich, Jr.                           Weyrich Computer Consulting
>Certified Data Processor                         POB 5782, Scottsdale, AZ 85261
>Certified Systems Professional                        Voice: (602) 391-0821
 ^^^^^^^^^^^^^^
      Certified by who?

[1mRich Holowczak[m               holowcza@andromeda.rutgers.edu
Rutgers University
Ph.D. Computers and Information Systems Program
"Availability of Distributed Databases During Network Partitioning"
+---------------------------------------------------+---------------+
| The .sig, the whole .sig and nothing but the .sig | Ask about the |
|           <<< This space for rent >>>             | car alarm FAQ |
+---------------------------------------------------+---------------+
From: kevin.bongiovanni@sqlware.uunet.UU.NET (Kevin Bongiovanni) 
Newsgroups: comp.databases.oracle
Subject: RE: ORACLE SQLPLUS MATRIX
Date: 29 Dec 92 00:56:00 GMT
Distribution: world
Organization: SQLWare Inc., Leesburg, VA - (703) 771-3979
Reply-To: kevin.bongiovanni@sqlware.uunet.UU.NET (Kevin Bongiovanni) 

In response to your SQLPLUS matrix question...
   I found the following program floating around a book in my office.
The idea is that you have a table R(A,B,C) such that element A can be
the row, element B can be the column, and element C the value.
          B1.....Bn
       A1 C1,1...C1,n
       ...
       An Cn,1...Cn,n
The SQL Script is as follows:
   column x noprint
set pause off
set heading off
set feedback off
.
spool temp.sql
select 0 x,'select <element A>, ' from dual
   union
select 1 x,'sum(decode(<element B>,''' || <element B> || ''',<element
C>, 0))' ||' '||<element B>||','
from <table name>
where <element B> is not null
   union
select 2 x,'sum(<element c>) Total from <table name> group
   <element A>;'
from dual
order by 1
/
spool off
set header on
spool result
start temp
spool off
/
                                                                                     
----
+--------------------------------------------------------------------------+
| SQLWare BBS  703-771-4158 (HST V.32BIS-Specializing in ORACLE and other  |
| RDBMS topics.  Sponsored by SQLWare Inc., Leesburg,VA-300 conferences.   |
+--------------------------------------------------------------------------+
Newsgroups: comp.databases.oracle
From: sjs@netcom.com (Stephen Schow)
Subject: Monday Morning Blues?
Organization: Netcom - Online Communication Services  (408 241-9760 guest) 
Date: Mon, 28 Dec 1992 18:16:25 GMT

We reboot our UNIX Oracle Server every weekend.  Every monday morning, all
of our Oracle users complain about unusually slow performance.  Anyone have
any clues why?  After a few hours it usually speeds up again, but the first 
few hours of monday are slower than molasses(sp).


-- 
------------------------------------------------------------------
Steve Schow    | But you don't have to use the claw, if you
sjs@netcom.com | pick the pear with the big paw paw......
(415) 354-4908 | Have I given you a clue......?
               |                       - Baloo the Bear
------------------------------------------------------------------
Newsgroups: comp.databases.oracle
From: pls@spica.jpl.nasa.gov (Paul Springer)
Subject: Oracle on the nCUBE
Organization: JPL
Date: Mon, 28 Dec 1992 16:57:52 GMT

We're in the process of getting Oracle installed on our nCUBE.  I'd appreciate
hearing from anyone else who has Oracle on the nCUBE, to find out what their
experience has been.

______________

Paul Springer
Distributed Systems Technology Group
Jet Propulsion Laboratory

Newsgroups: comp.databases.oracle
From: vkg1@Ra.MsState.Edu (VINAY GIDWANI)
Subject: Query only forms
Organization: Mississippi State University
Distribution: usa
Date: Wed, 30 Dec 1992 03:50:28 GMT

I have a form which I am invoking from a menu application.
I want the form to be a only query form when I invoke it
from a certain option in a menu. If I invoke it from another
part it should be a form where I can commit changes etc.

I tried the packaged procedure CALL_QUERY but could not 
get it to work.

Any suggestions or hints would be welcome.

My email address is vkg1@ra.msstate.edu

Thanks


Vinay
--

Vinay Gidwani
email vkg1@ra.msstate.edu
Newsgroups: comp.databases.oracle
From: rhari@us.oracle.com
Subject: Re: ? How do I declare a structure into which to fetch records?
Organization: Oracle Corporation, USA
Distribution: na
Date: Thu, 31 Dec 1992 17:06:29 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

In article <1992Dec30.054617.3077@kodak.kodak.com>, deal@tempus.Kodak.Com (Stephen M. Deal) writes:
> 
> I don't have my manuals with me (I'm at home) and I am trying to get
> some embedded SQL C code (a.k.a. Pro*C) to precompile. I am not sure
> that the manuals would answer this one anyway so I have turned to the
> net for help.
> 
> I have declared a structure into which a record should be retrieved:
> 
>   EXEC SQL BEGIN DECLARE SECTION;
>   struct emprec_ {
>     char name[35];
>     char dept_name[41];
>     char phone[16];
>   } query_results;        /* Results of query placed here */
>   EXEC SQL END DECLARE SECTION;
> 

Pro*C 1.3, 1.4 and 1.5 do not support structures as host language variables.
Pro*C 2.0 (to be release for ORACELE7) supports structures, pointers to
structs, etc etc and also the long standing preprocessing feature. 


Radhakrishna Hari
rhari@us.oracle.com
Newsgroups: comp.databases.oracle
From: naveen@ms.uky.edu (Naveen Goud)
Subject: What is SQL*FOMS
Date: Wed, 30 Dec 1992 21:52:49 GMT
Organization: University Of Kentucky, Dept. of Math Sciences

Hello Nettors,

I would like to know what is SQL*FORMS. I have used Oracle and manipulated the
data in the database using SQL queries. Other than this I have not worked
with any other tools of the ORACLE products.

Could someone explain the SQL*FORMS

Thanks in advance

- Naveen

Newsgroups: comp.databases.oracle
From: deal@tempus.Kodak.Com (Stephen M. Deal)
Subject: ? How do I declare a structure into which to fetch records?
Keywords: Pro*C, Declare Section, :structure
Reply-To: deal@Kodak.COM
Organization: Eastman Kodak Company
Distribution: na
Date: Wed, 30 Dec 92 05:46:17 GMT


I don't have my manuals with me (I'm at home) and I am trying to get
some embedded SQL C code (a.k.a. Pro*C) to precompile. I am not sure
that the manuals would answer this one anyway so I have turned to the
net for help.

I have declared a structure into which a record should be retrieved:

  EXEC SQL BEGIN DECLARE SECTION;
  struct emprec_ {
    char name[35];
    char dept_name[41];
    char phone[16];
  } query_results;        /* Results of query placed here */
  EXEC SQL END DECLARE SECTION;

but the precompiler errors on the fetch statement as follows:

  Precompiling proc_3.pc
   215           EXEC SQL FETCH emp_csr INTO :query_results;
   215 ....................................................^
       PCC-S-0026:  Undeclared host variable "query_results" at line 215
in file proc_3.pc
  *** Error code 1

It is pretty clear what is wrong. But how do I fix it? I know that char
datatypes are declared as VARCHAR, yet int are declared as int. Is there
a keywork for declaring embedded SQL structures?

Even a snippet of code that shows the proper syntax for the declaration
would be helpful. I just need the precompiler to recognize that the
structure has been declared.

Thanks in advance,
  ======================================================================
  | Steve Deal	 	| Internet: deal@Kodak.COM		       |
  | Eastman Kodak Co.	| UUCP:     ...rutgers!rit!kodak!tempus!deal   |
  | Engr. Systems Div.	| Voice:    (716) 726 - 5396		       |
  | mailcode: 35108	| FAX:	    (716) 726 - 7406	               |
  | 901 Elmgrove Road	| 					       |
  | Rochester, NY	| 					       |
  | 	   14653-5108   |					       |
  ======================================================================
    Disclaimer:	"Everyone is entitled to an opinion, 
		 the above is mine and not that of my employer."
From: champs@hhcs.gov.au
Newsgroups: comp.databases.oracle
Subject: Re: Explain THIS, please!
Date: 22 Dec 92 16:34:11 +1100
Distribution: world
Organization: Aust. Dept. Health, Housing and Community Services

In article <1gtd91INN7ln@ef2007.efhd.ford.com>, wwm@ef5003.efhd.ford.com (Bill Meahan) writes:
> I'm tearing my hair out at the following:
> 
> SQL> l
>   1  select '%' Shift,
>   2   p.prefix||'-'||p.base_code||'-'||p.suffix||p.control_code PartNumber,
>   3   s.scrap_code Reason,
>   4   SUM(s.cost) TotalDollars
>   5   FROM cimcmms.scrap_count_history s, acg.part p
>   6   WHERE s.part_key=p.key
>   7    AND s.work_center_code = '75021'
>   8    AND s.production_date BETWEEN '1-Dec-92' AND '13-Dec-92'
>   9    AND s.shift LIKE '%'
>  10    AND s.part_key IN ( SELECT ss.part_key
>  11       FROM cimcmms.scrap_count_history ss
>  12       WHERE ss.work_center_code = '75021'
>  13        AND ss.production_date BETWEEN '1-Dec-92' AND '13-Dec-92'
>  14        AND ss.shift LIKE '%'
>  15        AND ROWID <11
                 *****
Did you mean ROWID or ROWNUM?
                
>  16       GROUP BY ss.part_key
>  17       ORDER BY SUM(ss.cost) desc )
            **************************
            **************************
WARNING !  WARNING !  ORDER BYs DON'T WORK IN SUB-QUERIES
At least they don't in our version (VAX 3.0.9.7.1).  As soon as we put an order
by in a sub-query we got your error (group by worked fine though).  The SQL 
reference says you can't have order by in a sub-query on insert,update, and 
delete but does not say they're illegal in a select.

It looks to me like you're trying to retreive the 10 parts with the highest
sum(cost).  As far as I can tell it's impossible to retrieve the top 10 groups
of anything in a single query.  You can retrieve the top ten of something, but
not the top 10 GROUPS of something.  Your only chance is to use PL/SQL cursors
(though you'll have to put the results of the query in a temporary table so 
that you can see them after the PL/SQL has finished, as the results of PL/SQL 
statements don't echo to the screen).

Hope this helps.
------------------------------------------------------------------------------
    /\       Steven Champness
   /  \      Dept Health Housing and Community Services
  /    \     Brisbane Queensland
 /      \     via
/   /    \   Canberra Australia
\  / /   /
 \/ / / /    MACINTOSH SOFTWARE AXIOM : If you need a manual to operate it
  \/ / /                                it wasn't designed properly in the
   \/ /                                 first place !
    \/

Newsgroups: comp.databases.oracle
From: casivils@lescsse.jsc.nasa.gov (craig sivils)
Newsgroups: comp.databases.oracle
Subject: Re: Question using BETWEEN/AND cond with NEXT_DAY/DAY math
Date: 22 Dec 92 14:48:26 GMT
Organization: Lockheed Engineering and Sciences

(the mm in the date should be mon, but I assume this was a typo in the post)
It does not work on my machine either, looks like a bug.  A workaround would
be to convert the date into char in the format of yymmdd so that a char sort
would provide the correct order.  The following statement does work on my 
machine.

SQL> r
  1  select 'x' from dual
  2  where '921225' between to_char(next_day(sysdate,'sunday')-2,'yymmdd')
  3*                and     to_char(next_day(sysdate,'sunday'),'yymmdd')

'
-
x


(it worked on 22-dec-92)
 
                      Craig
From: fgreene@hayes.com
Newsgroups: comp.databases.oracle
Subject: Re: Does anyone know the status of SQL*FORMS 5.0
Date: 31 Dec 92 08:20:53 EDT
Organization: Hayes Microcomputer Products, Norcross, GA

In article <1992Dec31.041221.6372@aee.aee.com>, gene@aee.aee.com (Gene Kochanowsky) writes:
> To all Oracle Oracles,
> 
> 	If any of you know what the status of SQL*FORMS 5.0 is please let me
> know.
> 
> Thanks
> 
> Gene Kochanowsky
> -- 
> Gene Kochanowsky                           | "And remember ....
> Associated Electronic Engineers, Inc.      |       The better you look ... 
> (904)893-6741 Voice, (904)893-2758 Fax     |       the more you will see."
> gene@aee.com                               |               Miss Lidia

No such beastie as forms 5.0.  You must mean forms 4.0.  It is my
understanding that this version is either in or about to go into beta test.


 ----------------------------------------------------------------------------
 |      Frank Greene                  |          //////  //////             |
 |      DELPHI SYSTEMS, Inc.          |           ////    ////              |
 |      Telephone [615] 458-6032      |          ////    ////  //////       |
 |      Compuserve 74200,427          |         ////    ////    ////        |
 |      324 Ootsima Way               |        ////    ////    ////         |
 |      Loudon, TN 37774              |       //////  //////  //////        |
 ----------------------------------------------------------------------------
 |         Of course, any opinions or suggestions are strictly my own       |
 ----------------------------------------------------------------------------

Newsgroups: comp.databases.oracle
From: fogelinc@nmr1.Cyanamid.COM (Carl Fogelin)
Subject: Re: ? How do I declare a structure... 
Reply-To: fogelinc@nmr1.Cyanamid.COM
Organization: American Cyanamid Company
Date: Wed, 30 Dec 1992 18:38:13 GMT

In article 3077@kodak.kodak.com, deal@tempus.Kodak.Com (Stephen M. Deal) writes:
>
>I don't have my manuals with me (I'm at home) and I am trying to get
>some embedded SQL C code (a.k.a. Pro*C) to precompile. I am not sure
>that the manuals would answer this one anyway so I have turned to the
>net for help.
>
>I have declared a structure into which a record should be retrieved:

I hate to tell you this Steve, but PRO*C does not support the use of structures.
It states on page 1-6 of the PRO*C Supplement (V1.3)

   You cannot declare a host variable of type struct.  Nor can you refer
   to any of the following:

       - names declared in typedef statements
       - symbolic constants named by the #define directive
       - previously declared structs

I know it stinks, especially since VARCHARs are structs, but that's the way
it is.   :-(

Carl

---------------------------------------------------------------------------
 Carl Fogelin (fogelinc@cyanamid.com)     "All opinions are strictly mine" 
   "Sushi!   That's what my ex-wife called me.  Cold fish." -- Blade Runner
From: dosadi@mills.edu (Dave Edick)
Newsgroups: comp.databases.oracle
Subject: End user query tools
Date: 28 Dec 1992 23:50:35 GMT
Organization: Mills College, Oakland CA
NNTP-Posting-Host: ella.mills.edu

I've been asked to find out what's available in query tools for end users.
Since many of our users are running dumb terminals, we're not looking for
anything that requires desktop computing power or uses a GUI.  We looked
at Oracle Data Query and QMX.  They're along the lines of what we're looking
for, but we want to find out if there are any other such products.  

Any suggestions and/or pointers would be more than welcome.


/Dave Edick/     dosadi@mills.edu


-- 

/Dave Edick/     dosadi@mills.edu

BACKUP: A duplicate copy of critical data that no one bothered to make;