Unanswered: Help with SQL Function - Can't change Null value
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;
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
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
strPresence:= strPresence||', '||idr."TypePresence";
strPresence:= substring(strPresence,char_length(', ')+1);
Re: Help with SQL Function - Can't change Null value
Originally posted by andrewst
I don't quite understand your problem. The CASE statement works OK:
SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;
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 would be Null
Right now, my string is returning "P" when I test it... I also tought my case was ok but I'm now wondering why I don't get what I want. Thanks for your help