Re: Would be really nice if...

Haximus wrote:
> > One more keyword to SQL syntax? As if it's not a mess already?
> >
> > I would suggest more general solution. Allow nesting arbitrary SQL
on
> > column level. Your example would become something like
> >
> > SELECT COLUMNS(
> > select name from user_columns
> > where table_name = 'EMP'
> > minus
> > select name from user_columns
> > where table_name = 'DEPT'
> > ) from EMP
> >
> > This syntax establishes nice framework for pivot/unpivot
operator...
>
> I like the idea of the nested SQL, but how would you specify aliases
at the
> column level? Expressions?

The Meta Relation COLUMNS have to be 2-column. For example, the query

SELECT COLUMNS(
   select name, name || '_EMP' from user_columns    where table_name = 'EMP'
) from EMP

would produce a relation with columns named EMPNO_EMP, ENAME_EMP, ...

Another question is how to accomodate scalar subqueries in the select clause.

SELECT COLUMNS(
   select name, name || '_EMP'
   from user_columns
   where table_name = 'EMP'
  union
   select (select sum(1) from dept

           where dept.deptno = emp.deptno) dept_count    from dual
) from EMP

Now a really interesting question is if one can express pivot operator in this syntax. Received on Mon Feb 28 2005 - 16:10:12 CST