Thank you Elke, > 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
Initially I was trying to optimize the query, which looked like: select ... from ... where value((select ...), 0) = 1 and ... This query was very slow and I tryed several ways to rewrite the query. The solution with "HAVING" clause gives the fastest results. Thanks for good advice. There is still one problem - when I started using having-clause more actively I started to get wrong results in very similar manner with the bug I wrote about some time ago (http://lists.mysql.com/maxdb/26550). I don't know if anyone looked at it yet. Best regards. Alexei Novakov. -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
