Danny Tramnitzke wrote
>
> Hi all
>
> I still have a question about subqueries. I know there are
> two types of
> subqueries, the first is just like the following :
>
> Select (Select count(*) from abteilung), pname from Perso
>
> So, it has a simple "Select (select ...)...)" form.
>
> But sometimes there comes the Error (sorry, don't know the
> errornumber) :
> "Subquery not allowed"
>
> In this case I make a workaround :
>
> Select first from table, (select column first FROM table) tab01
>
> So, here I have to take an alias parameter for the subquery, which is
> explained in the FROM part.
>
> But, I don't realised yet, whether there is a rule when to
> take the first
> or the second version.
> Is there a page in the docu somehow ?
>
> Here
> http://www.sapdb.org/7.4/htmhelp/60/515f3eca2a11d2a97100a0c944
9261/content.htm
I found nothing...
Let's try to find out what you want:
SELECT (select count(*) from abteilung), pname from Perso
- is only possible, if the subquery will result in exactly one column and one row ->
one value
- is the same as
select 12, pname from Perso
in case abteilung includes 12 rows
Select first from table, (select column first FROM table) tab01
- can be done, no matter how many rows and columns will be in the result of the
subquery
- is ( well, how to say it in a polite way? ) a little bit strange, because in that
case
Select table.first, tab01.first from table , table tab01
is exactly the same, without the overhead of the subquery.
A subquery there is of any use only if
* a set-function
and/or
* group by/having
or
* distinct
or
* union/intersect/...
is specified in the subquery
- is ( well, see above) strange, because only first of table (that column in the select
on the left) will be part of the final result, because in the main select list
nothing
of tab01 is given (or nothing of table) and the whole overhead of the subquery/the
join
is just for fun (and bad performance)
- will be handled like every join: if no join-predicate is given,
combine every row from one table with every row of the result of the
from-select-subquery
You can do much more things with the second variant (but should avoid this if a normal
join would do, too).
If exactly one row with one column will be the result of the subquery, the first
version
should be preferred.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
