Try this
{I wasn't sure whether cd_nature_ltr is in ligne_trans
or transaction. This assumes ligne_trans. If it is in
transaction thatn move "cd_nature_ltrsn = 2" into the ON clause.}
SELECT CASE id_ltrsn
WHEN NULL THEN 0
ELSE id_ltsrn
END AS id_ltrsn,
CASE id_ltrsn
WHEN NULL THEN NOW()
ELSE MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH,
CURDATE()))
END AS subs_start
FROM ligne_trans
LEFT JOIN transaction
ON (transaction.id_trsn = ligne_trans.id_trans_ltrsn
AND transaction.id_pers_trsn = 278
)
WHERE cd_nature_ltrsn = 2
AND ligne_trans.id_cntxt_ltrsn = 1
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
-----Original Message-----
From: Jacques Brignon [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 09, 2005 9:19 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Query producing default values
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
