Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    2

    Question Unanswered: Alternative to COALESCE

    Hi All,

    So here is my hypothetical situation:

    I have a table full of users, and the groups that they can belong to, which is controlled by a series of flags. A user can belong to multiple groups. Because the user names can be the same, and because of poor table design (not my fault), the flags essentially make the records unique.

    Example:
    (I put the hyphens in to retain the formatting so it was easier to read)
    FName___LName___GroupA1___GroupA2___GroupB1___Grou pB2___GroupC
    John-----Smith-------T-----------F----------F----------F---------F
    John-----Smith-------F-----------F----------T----------T---------F
    John-----Smith-------F-----------F----------F----------F---------T

    Now for the application that I am writing, I need to return the user that belongs to either GroupB1 or GroupB2. I would normally use COALESCE(GroupB1, GroupB2) = 'T', but the flags can't be null, so COALESCE won't work. Is there an alternative to COALESCE that will keep me from writing the clause WHERE NOT (GroupB1 = 'F' AND GroupB2 = 'F')?

    In reality, I have a table with 18 flags, which can be divided into 3 subsets of 6, so I need to evaluate if a record has 1 of 6 flags set to 'T', and having to write the WHERE NOT clause evaluating each of the 18 flags would be ugly.

    Any Thoughts?

    Thanks,

    CRB
    Last edited by CRB3428; 05-30-12 at 12:22. Reason: my formatting got screwy

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you tried MAX function ?

    Code:
    with temp(member,grpa,grpb,grpc,grpd) as
    (
    values
    (1,'F','T','F','F') ,
    (2,'F','F','F','F'),
    (3,'T','T','T','T')
    )
    select member from temp where max(grpa,grpb,grpc,grpd)='T'
    output is


    MEMBER
    -----------
    1
    3

    2 record(s) selected.
    Last edited by sathyaram_s; 05-30-12 at 12:29. Reason: included resultset for reference
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Beautiful!! Works like a charm! Thanks!!!

Tags for this Thread

Posting Permissions

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