Thanks, that makes a lot of sense. My only problem is that I am using here a standard piece of code on the application side and I would hate to modify it, the thing I have all liberty to change is the query! Reason for trying to ask the query itself to tell me if there is nothing in the DB!
-- Jacques Brignon Selon [EMAIL PROTECTED]: > Jacques Brignon <[EMAIL PROTECTED]> wrote on 11/09/2005 09:58:07 AM: > > > I have a query which scans a subscription databse to locte the most > recent > > expiration date of the subscription to a given periodical or serviceto > compute > > the start date of a renewal. > > > > It works fine when for a given person such a subscription exists. If > none > > exists, as expected the query produces no results. > > > > Any suggestion on how to transform this query to produce a default value > set > > when no past subscription exists? > > > > Here is the query: > > > > SELECT > > id_ltrsn, MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH, CURDATE())) > AS > > subs_start > > FROM > > ligne_trans, transaction > > WHERE > > transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn = 2 > > AND ligne_trans.id_cntxt_ltrsn = 1 > > AND transaction.id_pers_trsn = 278 > > GROUP BY > > ligne_trans.id_cntxt_ltrsn > > > > When past subscipiton exixts it will produce as an example: > > > > id_ltrsn | subs_start > > --------------------- > > 79 | 2006-11-25 > > > > When no past subscription exists I would like to get today's date as a > result > > instead of nothing, example: > > > > id_ltrsn | subs_start > > --------------------- > > 0 | 2005-11-09 > > > > > > Thanks for any help you can provide > > > > -- > > Jacques Brignon > > > > You are asking the database to return with data it does not have. Can you > not detect the fact that you found no records and use that in your > application code to supply a default date? That would be much easier to > implement and maintain than any database-based solution. The SQL can > become quite convoluted when you start trying to simulate missing values. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
