Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Question 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;
    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;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help with SQL Function - Can't change Null value

    I don't quite understand your problem. The CASE statement works OK:
    PHP Code:
    SQLSELECT 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;

    P T
    - -
    t P
    f A
      
    -


  3. #3
    Join Date
    Feb 2004
    Posts
    2

    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:
    PHP Code:
    SQLSELECT 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;

    P T
    - -
    t P
    f A
      
    -

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •