Help with SQL Function - Change Null value 1

[Exode]

What I'm trying to do is to build a string that will print all the presences of a user for a session. My problem is that I'd like to put the - value when the pr_presence isn't True or False but right now it only returns pr_presence when it contains a boolean value. This way I can't treat the NULL or blank value. Any help would be really appreciate !

Here is my fonction:

DECLARE strPresence VARCHAR;
sessionID alias for $1;
userID alias for $2;
idr record;
BEGIN
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
Else '-'
End as "TypePresence"
FROM seance, InscriptionEtat, inscriptionSession
RIGHT JOIN presence ON inscriptionSession.usr_id = presence.usr_id
LEFT JOIN session ON inscriptionSession.ses_id = session.ses_id
WHERE session.ses_id = sessionID
AND presence.usr_id = userID
AND presence.sea_id = seance.sea_id
AND seance.ses_id = session.ses_id
AND seance.sea_valide = 't'
AND inscriptionSession.usr_id = usager.usr_id
AND inscriptionSession.ie_id = inscriptionEtat.ie_id
AND inscriptionEtat.ie_OK = 't'
ORDER BY seance.sea_datedebut
LOOP
strPresence:= strPresence||', '||idr."TypePresence";
END LOOP;
strPresence:= substring(strPresence,char_length(', ')+1);
RETURN strPresence;
END;

If my session contains 4 seances, and the user only enters 1 presence for these seances, my string should looks like "P,-,-,-" because the other 3 pr_presence should be Null
Right now, my string is returning "P" when I test it... Thanks for your help

 

[grega]

You may need to look at outer joins.

Greg.

 

[r937]

if i might just add a comment...

the query already uses outer joins

in fact it uses two of them, a right and a left!!

i've looked at this a couple of times, and i still don't understand it:

[tt] FROM seance
, InscriptionEtat
, inscriptionSession
RIGHT
JOIN presence
ON inscriptionSession.usr_id
= presence.usr_id
LEFT
JOIN session
ON inscriptionSession.ses_id
= session.ses_id
WHERE session.ses_id
= sessionID
AND presence.usr_id
= userID
AND presence.sea_id
= seance.sea_id
AND seance.ses_id
= session.ses_id
AND inscriptionSession.usr_id
= usager.usr_id
AND inscriptionSession.ie_id
= inscriptionEtat.ie_id[/tt]

i've written thousands of queries over the years, but i've never mixed inners, a left, and a right quite like that

that, to me, is a recipe for disaster

first of all, i would rewrite it with INNER JOIN syntax, instead of comma-delimited table names

secondly, i would rewrite the right join as a left join

and there's no way i can even begin to try to fix whatever this query is doing wrong, without at least some sample data from each table



rudy

http://r937.com/

SQL Consulting