none
IF EXISTS check RRS feed

  • Question

  • I  have a table PERSON_ROLES. It defines the roles of employers with a client.

    First, I want to check for ROLLN_IDEN: ‘casem’ wich has an open end date (DATE_TOT is null), if it matches this, return the EMPL_id

    If the CLIENT_ID has no actual ROLLN_IDEN: ‘casem’ than I want to retrieve the EMPL_ID  wich has a value: 1 in column: RESPONSIBLE if it matches this,  return also the EMPL_id

    ID

    EMPL_ID

    ROLLN_IDEN

    RESPONSIBLE

    DATE_FROM

    DAT_TOT

    CLIENT_ID

    1

    NAME1

    CASEM

    1

    01-01-2014

    28-02-2014

    001

    2

    NAME2

    HFDBEH

    0

    31-01-2014

    null

    001

    3

    NAME3

    BEH

    0

    01-01-2014

    null

    001

    4

    NAME4

    BEH

    1

    01-03-2014

    null

    001

    This is what i tried so far

     
    SELECT *
    
    From PERSON_ROLES t2
    
    WHERE 
    
    t2.ROLLN_IDEN in (select * from PERSON_ROLES where ROLLN_IDEN in ('casem') and dat_tot is not null) 
    OR
     t2.RESPONSIBLE in (select * from PERSON_ROLES where RESPONSIBLE in ('1') and dat_tot is not null)


    I want to retrieve this data through a view.

    Create view … as 
    
    Select *
    
    from PERSON_ROLES 
    
    WHERE <…>





    • Edited by MVP_88 Friday, October 17, 2014 12:03 PM
    Friday, October 17, 2014 8:30 AM

Answers

  • this?

    SELECT EMP_ID,CLIENT_ID
    FROM
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY CLIENT_ID ORDER BY CASE WHEN ROLLN_IDEN = 'CASEM' AND DATE_TOT IS NULL THEN 1 ELSE 0 END DESC) AS Seq
    FROM PERSON_ROLES
    WHERE RESPONSIBLE = 1
    OR ROLLN_IDEN = 'CASEM'
    )t
    WHERE Seq = 1


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, October 17, 2014 12:09 PM

All replies

  • Please write your posts in the editor provided by the forum. It's hard to read a different font. Please also post concise and complete samples. Include table DDL and sample data INSERT statements instead of textual representations. Also use the embed code button from the toolbar.

    E.g.

    SELECT  EMPL_ID
    FROM    PERSON_ROLES
    WHERE   ( ROLLN_IDEN = 'casem'
              AND DAT_TOT IS NULL
            )
            OR ( ROLLN_IDEN != 'casem'
                 AND RESPONSIBLE = 1
               );


    Friday, October 17, 2014 9:19 AM
  • ---Edited the original post ---

    The query should check if the is ROLLN_IDEN 'CASEM' with DAT_TOT is null and give me back the EMPL_ID

    If there is no 'CASEM' defined it should check the colomn RESPONIBLE on value '1' and DAT_TOT is null and should also give me back the EMPL_ID

    Hopes this makes sense

    Friday, October 17, 2014 12:01 PM
  • this?

    SELECT EMP_ID,CLIENT_ID
    FROM
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY CLIENT_ID ORDER BY CASE WHEN ROLLN_IDEN = 'CASEM' AND DATE_TOT IS NULL THEN 1 ELSE 0 END DESC) AS Seq
    FROM PERSON_ROLES
    WHERE RESPONSIBLE = 1
    OR ROLLN_IDEN = 'CASEM'
    )t
    WHERE Seq = 1


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, October 17, 2014 12:09 PM
  • SELECT  EMPL_ID
    FROM    PERSON_ROLES
    WHERE   ( ROLLN_IDEN = 'casem'
              AND DAT_TOT IS NULL
            )
            OR ( RESPONSIBLE = 1
                 AND NOT EXISTS ( SELECT    *
                                  FROM      PERSON_ROLES
                                  WHERE     ROLLN_IDEN = 'casem' )
               );

    Friday, October 17, 2014 12:39 PM