Re: help for a former sybase user

In article <1993Sep20.231840.733_at_gtewd.mtv.gtegsc.com>, davidsonj_at_gtewd.mtv.gtegsc.com (davidsonj_at_gtewd.mtv.gtegsc.com) writes:
>Having used Sybase on a recent project and now using Oracle I have had a
>little difficulty with a couple of things. Could someone tell me.....
>
>a) Why doesn't oracle PL/SQL allow a select that includes both a
> column and an aggregate function. Specifically, I am trying
> to SELECT NAME, COUNT(*) from a table containing a list of names.
> I get the error indicating that the name is "not a single-group
> group function" (error #00937). Sybase allows this returning
> each of the individual rows (i.e., names) along with a count
> reflecting the total number of rows in the table. (Note: the
> count is returned with each row - each row having the same value.)
> Also - don't suggest the %ROWCOUNT qualifier since I am using a
> cursor and the %ROWCOUNT doesn't tell you the total number of
> rows until you have fetched them all....I want the count prior
> to fetching so I can allocate the necessary space.
>
> Looking at the Sybase documentation, they indicate that their
> implementation, "similar to other implementations", extends the
> SQL standard to allow the above type of selects. I guess I don't
> see why Oracle's implementation doesn't allow this too - I found
> it very useful.
>
>2) I am also interested in a tool that would allow me to test my
> SQL commands. Currently I have to put the sql command into a file,
> execute the file from SQLPLUS using the _at_filename command, and then
> re-edit the file to make corrections. For those of you that have
> seen it - I really liked Sybase's Data Workbench - that provided a
> crude screen editor to enter a SQL command (or series of commands),
> edit them, execute them, examine the results, edit the commands
> again, save them to a file (at any time), and reload the file (at
> any time). This was a great way to work out the SQL syntax for
> some of the nastier queries. There was also a very nice interactive
> (motif based) capability for building select commands (but not
> update/inserts/etc for some reason) where you could select the table
> from a list (which could be restricted based on at least owner),
> then select the fields in the select, add necessary clauses, and
> again try this all out interactively. Anyway, is there any
> equivalent for ORACLE????????
>
>Thanks for any help,
> jim
>
>p.s. Don't get the impression that I wish I was still using Sybase - there
> are features of Oracle that I like - these are just 2 that I liked
> better about Sybase.
>
For problem 1, use a 'group by' clause in your select statement.

        select  Name,
                count(*)
        from    TABLE
        group by Name
        

This would produce a result similar to

        NAME            COUNT(*)
        --------------- --------
        JONES                  8
        SMITH                  5
        BAKER                 11
        

For problem 2, SQL*Plus has a very crude line-oriented editor as part of its interactive session. If you have executed a file using an '_at_' or start command, you may then review and edit it line by line using a 'l' command. The most recently executed or typed SQL command (but not SQL*Plus command) is contained in the 'SQL Buffer' and may be modified, added to, or deleted from. As I said, the editor functions are very crude (remember MS-DOS edlin?) but can work for some quick prototyping.

Hope this helps

Regards

Michael Received on Fri Sep 24 1993 - 18:05:39 CEST