On Thursday 27 March 2008 3:17 pm, Sam Mason wrote:
> On Thu, Mar 27, 2008 at 08:43:46PM +0100, Alain Roger wrote:
> > i have a problem solving my function trouble.
> > this function should return an email address stored in a table
> > (tmp_newsletterreg) based on a number (sessions ID).
> > if the session id is not find it should return a string corresponding to
> > and error.
> > if the email in found but already exists into another table (users), it
> > should also return a string value relative the this error.
>
> This is fun isn't it!
>
> > here is my stored procedure.
>
> And here it is in a single, unreadable, SQL statement:
>
> SELECT CASE WHEN s.email = u.email THEN 'email already exists'
> ELSE COALESCE(s.email, 'no such session') END AS msg
> FROM (VALUES (1)) x(one)
> LEFT JOIN (
> SELECT email FROM tmp_newsletterreg
> WHERE sessionid = $1) s ON TRUE
> LEFT JOIN (SELECT email FROM users) u ON s.email = u.email;
>
> Why not put a foreign key on the "email" column to the users table---one
> less error to handle that way?
>
>
> Sam
Or a simpler way to do handle my previous suggestion:
IF (ret_email IS NULL ) OR (ret_email='') THEN
RETURN ('-3')
--
Adrian Klaver
aklaver@comcast.net