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
