Alexei Novakov wrote: > Hi list, > > I ran into interesting issue. I have a query: > > SELECT ID_F, (SELECT VAL_F FROM ...) AS VAL_F > FROM MAIN_TBL > WHERE VAL_F = 3 >
This kind of subquery you are using is named a one-value_subquery, meaning that exactly one value is returned by the subquery, included in the upper query and handled the same as is done in the following sequence select val_f into :val_f_value from ... select id_f, :val_f_value from main_tbl But that is obviously not what you wanted. On the other hand identifier used in where-clause and group by clause are those member of the tables mentioned in the from-clause. The 'renaming' of output-columns is done 'after' handling the where/group-by and therefore these alias-names can not be used in these clauses. > This doesn't work: > > Unknown column name:VAL_F > > Actually I cannot refere any field by alias, not only subselect, but for > other types of fields > there are other ways to refere them. > > Is there any way to refere this VAL_F field in the condition? Conditions should be done at the nearest level possible, therefore IN the select, not returning EVERY VAL-F-value out of the select and THEN doing the qualification. Therefore the idea of Robert is not too bad: SELECT ID_F, tsub.VAL_F FROM MAIN_TBL, (SELECT VAL_F FROM ... WHERE tsub.VAL_F = 3) tsub where tbl.idf = tsub.... and .... It has only one mistake: the alias used for the result of the from-select van NOT be used as tablename IN that select, only in the upper one, where the alias is known. (The second difference was, I assume, just a misspelling: tbl --> main_tbl) --> SELECT ID_F, tsub.VAL_F FROM MAIN_TBL, (SELECT VAL_F FROM ... WHERE VAL_F = 3) tsub where MAIN_TBL.idf = tsub.... and .... should do. But why do it that way? Why don't use just a normal join? Select id_f, val_f from main_tbl, ... where val_f = 3 and Main_tbl.idf = ... Your answer, that correlated subqueries and views will not work is correct. But what has this to do with correlation? Nothing. Correlations are queries where values from the upper query are used in the lower one for qualification. But neither Roberts nor mine query has a correlation in. And the next version of query I saw: SELECT ID_1, COUNT(*) AS TMP_FLD FROM TBL WHERE TMP_FLD = 2 GROUP BY ID_1 Can be handled with correct SQL. The result of set-functions (SUM/COUNT/MIN/..) cannot be handled in WHERE-clause (except for special correlated subqueries). But to restrict to those results having exactly 2 rows per group SQL uses the HAVING-clause: SELECT ID_1, COUNT(*) AS TMP_FLD FROM TBL GROUP BY ID_1 HAVING COUNT(*) = 2 Now, I hope, you are able to prepare a SQL statement convenient for your needs. Elke SAP Labs Berlin > > Thanks in advance. > Alexei Novakov > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
