Andris Spruds wrote: > > Hello, > > I'm sorry that this is not MaxDB-specific question, but a more general SQL > problem. Anyway, maybe someone could help me: > > I have the following query (the main purpose of the query is to get latest > measurements for a lake.) My problem is that ANY statement does not like > NULL's. If for some reason dbc_measurements.date_taken is NULL, then ANY > statement does not work (return value undefined, according to MaxDB > manual. > How can I rewrite the query so that it does not ignore rows containing > NULL? > > SELECT DISTINCT(l.id),l.name FROM dbc_measurements t1 , dbc_lakes l > WHERE t1.param_id=46 AND t1.lake_id=l.id > AND (t1.date_taken, t1.lake_id) = ANY (SELECT MAX(date_taken), lake_id > FROM > dbc_measurements WHERE param_id=46 AND measurement>1 GROUP BY lake_id) > ORDER BY l.name
I think the function VALUE http://dev.mysql.com/doc/maxdb/en/cf/633171c03511d2a97100a0c9449261/frameset.htm may help: (VALUE(t1.date_taken, '00010101'), t1.lake_id) = ANY (SELECT MAX(VALUE(date_taken, '00010101'), lake_id Elke SAP Labs Berlin > > The query bellow seems to work, but the cost value, according to > optimizer, > is 100 times higher: > > SELECT DISTINCT(l.id),l.name FROM (SELECT lake_id FROM (SELECT > MAX(date_taken), lake_id FROM dbc_measurements WHERE param_id=46 AND > measurement>1 GROUP BY lake_id)) t1, dbc_lakes l WHERE t1.lake_id=l.id > ORDER > BY l.name > > > Greetings, > Andris Spruds > > > > > > -- > 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]
