Workaround for “ORA-00998: must name this expression with a column alias”

Did you ever encounter

ORA-00998: must name this expression with a column alias

and wondered why ?

 

Doc says

Error: ORA 998
Text: must name this expression with a column alias
-------------------------------------------------------------------------------
Cause: An expression or function was used in a CREATE VIEW statement, but no
corresponding column name was specified.
When expressions or functions are used in a view, all column names for
the view must be explicitly specified in the CREATE VIEW statement.
Action: Enter a column name for each column in the view in parentheses after
the view name.

but that is (at most) only half the truth.

Here is my story about ORA-00998:

sokrates@11.2 > create table temp as select count(*) from dual;
create table temp as select count(*) from dual
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
sokrates@11.2 > REM must ?
sokrates@11.2 > REM I don't think so ...
sokrates@11.2 > REM let's see ...
sokrates@11.2 > create table temp as select * from (select count(*) from dual);

Table created.

sokrates@11.2 > desc temp
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNT(*) NUMBER

sokrates@11.2 > REM hehe !
sokrates@11.2 > select "COUNT(*)" from temp;

COUNT(*)
----------
1

sokrates@11.2 > CREATE VIEW test_v AS
2 SELECT owner || '.' || table_name
3 FROM all_tables
4 WHERE owner = USER;
SELECT owner || '.' || table_name
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
sokrates@11.2 > create view test_v as
2 select * from (
3 SELECT owner || '.' || table_name
4 FROM all_tables
5 WHERE owner = USER
6 );

View created.

sokrates@11.2 > desc test_v
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER||'.'||TABLE_NAME VARCHAR2(61)

sokrates@11.2 > select "OWNER||'.'||TABLE_NAME" from test_v where rownum=1;

OWNER||'.'||TABLE_NAME
-------------------------------------------------------------
SOKRATES.T


🙂

world clock

with z as (
  select
  distinct
     tn.tzname as "timezone",
     cast(
        from_tz(
           cast(sysdate as timestamp),
           dbtimezone
        ) at time zone tn.tzname
        as date
     ) as "time there"
  from V$TIMEZONE_NAMES tn
)
select
   "time there",
   listagg("timezone", ', ') within group (order by "timezone") as "where"
from z
group by "time there"
order by "time there"

counting lines of code

I never understood why some people are counting lines of software-code. What is the goal behind it ? Some people think, you could measure the “size” or the “complexity” (whatever is meant by one of these terms) of a piece of code by counting the number of lines it consists of. Or you could use this metric to measure the efficiency of a developer writing code.
In my opinion, this approach does not apply to code developed in SQL in special and to code in general.

The best example that “counting lines of code” is worth for nothing is the following (which – by the way – has nothing to do with SQL):

In 1995, David Bailey, Peter Borwein and Simon Plouffe discovered the following algorithm:

(the so called “miraculous Bailey-Borwein-Plouffe Pi algorithm” – see for example A New Formula for Pi ).

What’s so miraculous with it ?
It allows you to calculate the dth digit of pi without being forced to calculate all the preceding d-1 digits.
Former to that, no one previously had even conjectured that such a digit-extraction algorithm for pi was possible. Instead of, there was a common agreement in believing that you have to calculate all preceding d-1 digits before you can calculate the dth digit. (The authors applied the algorithm to show that the 10 billion’th hexadecimal digit of Pi is 9).

Note, that it took mankind some milleniums to discover this algorithm:
Algorithms to calculate pi were implemented and applied long before,see for example A history of Pi

Note further, that implementing this algorithm does not require many lines of code – you can find a very compact implementation in Python for example on Pi with the BBP formula (Python)

So, it is an excellent example in showing what you can measure by counting lines of code:

Not more than nothing.

Update 12/07/2011:
a very nice SQL example that “lines of code” measures nothing can be found on the
asktom-“sorting by number”-thread
Thanks to Laurent Schneider and a reader called Brendan !
They show how to solve a quite hard SQL problem with a two-liner.

group by ()

I never saw neither used

group by ()

However, there are cases one may need it:

Support asked me in one of the issues I am working with them

what’s the size of your database ?

They didn’t define “size of database” – I don’t know what is meant by that.
All used space together ?
All segments together ?
All datafiles together ?
All datafiles + online-redo-log-files + controlfiles ?
All datafiles + online-redo-log-files + controlfiles + … (standby log-files, archived log files), rman backup’s ?
…

So, in order to gave them an answer, my first thought was querying SYS.FILE$, I thought this one should contain all necessary informations and if they aren’t content with me using SYS.FILE$ they can define for me what they want to know. I knew DBA_DATA_FILES is built using SYS.FILE$, and I thought SYS.FILE$ would probably contain also information about other files than datafiles …

SQL> desc sys.file$

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#					   NOT NULL NUMBER
 STATUS$				   NOT NULL NUMBER
 BLOCKS 				   NOT NULL NUMBER
 TS#						    NUMBER
....

shows, there are no BYTES in SYS.FILE$, so we have to sum up the Blocks and multiply by

select value from v$parameter where name = 'db_block_size';

, so my thoughts

( In fact, it is a bad idea to query SYS.FILE$ , it is not documented and my thoughts about it were completely wrong )

So I thought, I give them the output of

select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$;

I was very surprised when I saw:

SQL> select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$;

select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$                             *
ERROR at line 1:
ORA-00937: not a single-group group function

I didn’t expect that.

(select value from v$parameter where name = 'db_block_size')

is a scalar subquery, hence, for the duration of the SQL, it is a constant and should be treated like a constant.

Let’s go away from SYS-objects and use the following:
take an aggregate function

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) from n;

    MAX(N)
----------
	10

we can multiply that by a constant without trouble

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * 5 from n;

  MAX(N)*5
----------
	50

but we are getting into trouble when using a scalar subquery instead of a constant:

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * (select 5 from dual) from n;

select max(n) * (select 5 from dual) from n                        *

ERROR at line 2:
ORA-00937: not a single-group group function

Don’t know why and if this is expected / intended.
Scalar Subquery Expressions
shows some restrictions of scalar subquery expressions, but it doesn’t seem to cover this issue here ( I don’t use a scalar subquery expression in a group by clause )

Googling around, I found Laurent Schneider blogging on similar issues on
Why cannot I use subquery there?
and an interesting construct on Comment No.1 by Chen Shapira

group by ()

I never saw this construct, but tried it:

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * (select 5 from dual) from n
  3  group by ();

MAX(N)*(SELECT5FROMDUAL)
------------------------
		      50

Eh voilà – it works.

group by ()

doesn’t seem to be documented (due to Laurent), so I don’t know if it is a good idea to use it in production, though.

Average of DATEs

avg does not support DATEs as arguments:

sokrates@11.2 > select avg(created) from dba_users;
select avg(created) from dba_users
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE


Why ? I don’t know

sokrates@11.2 > select
2 min( created ),
3 max( created ),
4 median( created )
5 from dba_users;
MIN(CREATED) MAX(CREATED) MEDIAN(CREATED)
-------------------- -------------------- --------------------
17-sep-2010 16:08:04 04-apr-2011 11:13:54 09-oct-2010 15:57:09


work without problems.

So, what’s the easiest workaround when you need avg of DATEs ?

sokrates@11.2 > select
2 sysdate - avg ( sysdate - created ) "avg(created)"
3 from dba_users;
avg(created)
--------------------
15-oct-2010 21:19:00

is neat, isn’t it ?

And: it’s deterministic (doesn’t depend on SYSDATE) !
(proof of that is left to the reader)