Case statement

  • Hello,

    I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension,

    Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned. Sorry if this is elementary, but I haven't figured it out, and have tried many things.

    Agent_ID Agent_ID2

    11111 22222

    <code>

    SELECT DISTINCT

    CASE WHEN [AGENT_ID] is not null then AGENT_ID

    when agent_id2 is not null then agent_id2 end as extension

    FROM [AA_Helper].[dbo].[tblEmpData]

    </code>

  • I'm not 100% sure of what you are after, but maybe start with this:

    SELECT DISTINCT

    Extension = isNull(Agent_Id,'') + isNull(Agent_Id2,'')

    from [AA_Helper].[dbo].[tblEmpData]

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • No...that just concatenates the 2 I want it to create it as a second row

    11111

    22222

    33333

  • I created one cte, and then union all the select of agent_ID2..Like this

    ;with

    csServiceExtAgentID(Extension) as

    (SELECT DISTINCT

    CASE WHEN [AGENT_ID] is not null then AGENT_ID end as extension

    FROM [AA_Helper].[dbo].[tblEmpData]

    )

    SELECT * FROM csServiceExtAgentID where extension is not null

    union all

    SELECT DISTINCT

    CASE WHEN agent_id2 is not null then agent_id2 end as extension

    FROM [AA_Helper].[dbo].[tblEmpData]

    where agent_id2 is not null

  • Still didn't work...Not getting all of the agent_id2's

  • cbrammer1219 (10/6/2014)


    I created one cte, and then union all the select of agent_ID2..Like this

    ;with

    csServiceExtAgentID(Extension) as

    (SELECT DISTINCT

    CASE WHEN [AGENT_ID] is not null then AGENT_ID end as extension

    FROM [AA_Helper].[dbo].[tblEmpData]

    )

    SELECT * FROM csServiceExtAgentID where extension is not null

    union all

    SELECT DISTINCT

    CASE WHEN agent_id2 is not null then agent_id2 end as extension

    FROM [AA_Helper].[dbo].[tblEmpData]

    where agent_id2 is not null

    What is the point of the case expressions here? In the first query if AGENT_ID is null it will return NULL. But then later you select them only where it isn't null. Why not drop the case expression and use a where clause to only return those rows where it isn't null.

    The second query is even more redundant. You have a case expression around agent_id2 so that when it is not null it returns agent_id2 and a where clause that only returns where they are not null. The case expression in both queries is not needed.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Try this

    use AA_Helper

    go

    select extension = AGENT_ID

    from dbo.tblEmpData

    where agent_ID is not null

    union all

    select agent_id2

    from dbo.tblEmpData

    where agent_ID2 is not null

    If you want DISTINCT entries only, change UNION ALL to UNION.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Something like this if I understand what you are after.

    SELECT AGENT_ID

    FROM [AA_Helper].[dbo].[tblEmpData]

    where AGENT_ID is not null

    group by AGENT_ID

    union all

    SELECT agent_id2

    FROM [AA_Helper].[dbo].[tblEmpData]

    where agent_id2 is not null

    group by agent_id2

    Now unless you include another column in these you have no way to keep each "group" together. If you need that you would need to add another column from tblEmpData so you can order by that column.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Because there is also a extension field, sorry guys copied the wrong query. I am trying to put extension, agent_ID and agent_ID into extension, sometimes one extension will have more than one (extension) so they were put into agent_ID and agent_ID2 so I am trying to create a drop down to display the extension, agent_ID and agent_ID. Hope that makes sense.

    ;with

    csServiceExtAgentID(Extension,Department) as

    (SELECT

    DISTINCT

    CASE WHEN [AGENT_ID] is not null then AGENT_ID

    WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT

    FROM [AA_Helper].[dbo].[tblEmpData]

    where extension is not null and LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'

    )

    SELECT * FROM csServiceExtAgentID

    ;with

    csServiceExtAgentID2(Extension,Department) as

    (SELECT DISTINCT

    CASE WHEN agent_id2 is not null then agent_id2

    WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT

    FROM [AA_Helper].[dbo].[tblEmpData]

    where agent_id2 is not null and LEN(extension) > 3

    )

    SELECT * FROM csServiceExtAgentID2

  • cbrammer1219 (10/6/2014)


    Because there is also a extension field, sorry guys copied the wrong query. I am trying to put extension, agent_ID and agent_ID into extension, sometimes one extension will have more than one (extension) so they were put into agent_ID and agent_ID2 so I am trying to create a drop down to display the extension, agent_ID and agent_ID. Hope that makes sense.

    ;with

    csServiceExtAgentID(Extension,Department) as

    (SELECT

    DISTINCT

    CASE WHEN [AGENT_ID] is not null then AGENT_ID

    WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT

    FROM [AA_Helper].[dbo].[tblEmpData]

    where extension is not null and LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'

    )

    SELECT * FROM csServiceExtAgentID

    ;with

    csServiceExtAgentID2(Extension,Department) as

    (SELECT DISTINCT

    CASE WHEN agent_id2 is not null then agent_id2

    WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT

    FROM [AA_Helper].[dbo].[tblEmpData]

    where agent_id2 is not null and LEN(extension) > 3

    )

    SELECT * FROM csServiceExtAgentID2

    Still the case expressions don't make sense here. The second especially. There is no chance that anything other than the first condition can be met in your second query because you filter out the other possibilities with your where predicates.

    Your questions is still extremely vague but I am taking one more shot in the dark.

    with csServiceExtAgentID(Extension,Department) as

    (

    SELECT

    DISTINCT

    ISNULL(AGENT_ID, Extension) as extension

    , DEPARTMENT

    FROM [AA_Helper].[dbo].[tblEmpData]

    where LEN(ISNULL(AGENT_ID, Extension)) > 3 and ISNULL(AGENT_ID, Extension) not like '6%' and DEPARTMENT like 'Service%'

    )

    ,

    csServiceExtAgentID2(Extension,Department) as

    (

    SELECT DISTINCT

    agent_id2

    , DEPARTMENT

    FROM [AA_Helper].[dbo].[tblEmpData]

    where agent_id2 is not null

    and LEN(extension) > 3

    )

    SELECT * FROM csServiceExtAgentID

    UNION ALL

    SELECT * FROM csServiceExtAgentID2

    If that doesn't do it you need to provide some details. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I just did this..

    select distinct

    extension

    from tblEmpData

    where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'

    union all

    select distinct

    case

    when agent_id is not null then agent_id

    end as extension

    from tblEmpData

    where LEN(agent_id) > 3 and agent_id not like '6%' and DEPARTMENT like 'Service%'

    UNION ALL

    select distinct

    case

    when agent_id2 is not null then agent_id2

    end as extension

    from tblEmpData

    where LEN(agent_id2) > 3 and agent_id2 not like '6%' and DEPARTMENT like 'Service%'

    ORDER BY EXTENSION

  • cbrammer1219 (10/6/2014)


    I just did this..

    select distinct

    extension

    from tblEmpData

    where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'

    union all

    select distinct

    case

    when agent_id is not null then agent_id

    end as extension

    from tblEmpData

    where LEN(agent_id) > 3 and agent_id not like '6%' and DEPARTMENT like 'Service%'

    UNION ALL

    select distinct

    case

    when agent_id2 is not null then agent_id2

    end as extension

    from tblEmpData

    where LEN(agent_id2) > 3 and agent_id2 not like '6%' and DEPARTMENT like 'Service%'

    ORDER BY EXTENSION

    So aside from continuing to use pointless case expressions did you get it figured out or do you still need help?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Yes I did, and the example you provided did not help, but thanks for your attempt.

  • cbrammer1219 (10/6/2014)


    Yes I did, and the example you provided did not help, but thanks for your attempt.

    Cool. Glad that you figured out a solution. You could simplify your query to this.

    select distinct

    extension

    from tblEmpData

    where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'

    union all

    select distinct

    agent_id

    from tblEmpData

    where LEN(agent_id) > 3 and agent_id not like '6%' and DEPARTMENT like 'Service%'

    UNION ALL

    select distinct

    agent_id2

    from tblEmpData

    where LEN(agent_id2) > 3 and agent_id2 not like '6%' and DEPARTMENT like 'Service%'

    ORDER BY EXTENSION

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Maybe you're looking to unpivot your data?

    CREATE TABLE tblEmpData(

    Agent_ID int,

    Agent_ID2 int)

    INSERT INTO tblEmpData VALUES

    (11111, 22222),

    (33333, NULL),

    (NULL, 44444),

    (NULL, NULL)

    SELECT x.extension

    FROM tblEmpData

    CROSS APPLY (VALUES(Agent_ID), (Agent_ID2)) x(extension)

    WHERE extension IS NOT NULL

    SELECT x.extension

    FROM tblEmpData t

    UNPIVOT (extension FOR agent IN( Agent_ID, Agent_ID2)) x

    GO

    DROP TABLE tblEmpData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply