Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Unanswered: Sql Query To Handle Flags

    Hello Everyone,

    I need some help. I need to build a sql query to handle a flag field from one table. The table can have several records for the same person and for each record he can have either a Y or N in the flag field.

    I am trying to, if possible, write a sql statement that will tell me that if the person has either Y or N flag in any of his records, I will say Y. If he only has N, I will say no.

    I can manage this using a stored procedure but hoping that someone has already done this in sql only.

    Thank you all for your help.

    Randy

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Code:
    SELECT 
      CASE 
        WHEN (
          SELECT COUNT(*) 
          FROM <table> 
          WHERE <field>='Y' AND <field>=<personsid>
        ) > 0 THEN 'Y'
        ELSE 'no'
      END
    Just read what you said. Am rethinking...
    Last edited by aschk; 04-27-07 at 13:21.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Or you could try a join like this:

    Code:
    Select P.Person_Id, P.Name, Decode(Y.Cnt,0,'No','Yes')
      From (Select Person_Id, Count(*) Cnt
              From Persontab 
             Group By Person_Id) Y
         , Persontab P
     Where P.Person_Id = Y.Person_Id;


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by LKBrwn_DBA

    Or you could try a join like this:

    Code:
    Select P.Person_Id, P.Name, Decode(Y.Cnt,0,'No','Yes')
      From (Select Person_Id, Count(*) Cnt
              From Persontab 
             Group By Person_Id) Y
         , Persontab P
     Where P.Person_Id = Y.Person_Id;


    Don't you mean
    Code:
    Select distinct P.Person_Id, P.Name, Decode(Y.Cnt,0,'No','Yes')
      From (Select Person_Id, Count(*) Cnt
              From Persontab 
              where my_field = 'Y'
             Group By Person_Id) Y
         , Persontab P
     Where P.Person_Id = Y.Person_Id;
    Last edited by beilstwh; 04-30-07 at 11:57.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could also do this to avoid the self join.

    Alan

    Code:
    Select Person_Id, 
    case when Count(*) = count(decode(flag,'N',1,null)) then 'N' else 'Y' end flagcnt
    From Persontab 
    Group By Person_Id

  6. #6
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Smile

    Hello ... Thank you all for your help. It is greatly appreciate.

    Sincerely,

    Randy

Posting Permissions

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