How to retrieve all the data group types at the same time in sql server?

  • I'm running the code in sql server. I'm trying to obtain datagroups A, B, and C at the same time by passing 0 as a parameter for DataNumbers.

    Following is the code snippet of my query,

    with group1 as

    (   select

    1 DataNumber, 1 Data_Sort

    union select 2 DataNumber, 2 Data_Sort

    union select 3 Data_Number, 3 Data_Sort

    ),

    group2 as

    (select

    Case

    When grp.DataNumber = 1 Then 'A'

    When grp.DataNumber = 2 Then 'B'

    When grp.DataNumber = 3 Then 'C'

    End datagroup,

    grp.DataNumber,

    grp.Data_Sort                                              

    from    group1 grp

    where  

    (cast(grp.Data_Group_Number AS char(1)) in (@Data_Group_Number) or '0' in (@Data_Group_Number))

    I applied the same logic in oracle and it works. The code I used in oracle was as follows;

    to_char(grp.Data_Group_Number) in :Data_Group_Number or '0' in :Data_Group_Number
    Please let me know what I need to do inorder to retrieve all the three data groups at the same time by passing the value 0 as a parameter.
    Thanks in advance!

  • Try this: WITH group1 AS (

        select 1 DataNumber, 1 Data_Sort
        union
        select 2 DataNumber, 2 Data_Sort
        union
        select 3 Data_Number, 3 Data_Sort
    ),
        group2 AS (

            SELECT
                CASE grp.DataNumber
                    WHEN 1 THEN 'A'
                    WHEN 2 Then 'B'
                    WHEN 3 Then 'C'
                END AS datagroup,
                grp.DataNumber,
                grp.Data_Sort
            FROM group1 AS grp
            WHERE CAST(grp.Data_Group_Number AS char(1)) = @Data_Group_Number
                OR @Data_Group_Number = '0'
    )

    Oracle and SQL Server are rather different animals.  Logic and Oracle don't always go together...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I modified Steve's code: This will return all 3 rows if you send the value of '0'.
    DECLARE @Data_Group_Number CHAR(1);

    SET @Data_Group_Number = '0';

    WITH group1 AS (

      select 1 DataNumber, 1 Data_Sort
      union
      select 2 DataNumber, 2 Data_Sort
      union
      select 3 DataNumber, 3 Data_Sort
    )
    --,
    --  group2 AS (

       SELECT
        CASE grp.DataNumber
          WHEN 1 THEN 'A'
          WHEN 2 Then 'B'
          WHEN 3 Then 'C'
        END AS datagroup,
        grp.DataNumber,
        grp.Data_Sort
       FROM group1 AS grp
       WHERE CAST(grp.DataNumber AS char(1)) = @Data_Group_Number
        OR @Data_Group_Number = '0'
    --)

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • below86 - Friday, March 30, 2018 1:43 PM

    I modified Steve's code: This will return all 3 rows if you send the value of '0'.
    DECLARE @Data_Group_Number CHAR(1);

    SET @Data_Group_Number = '0';

    WITH group1 AS (

      select 1 DataNumber, 1 Data_Sort
      union
      select 2 DataNumber, 2 Data_Sort
      union
      select 3 DataNumber, 3 Data_Sort
    )
    --,
    --  group2 AS (

       SELECT
        CASE grp.DataNumber
          WHEN 1 THEN 'A'
          WHEN 2 Then 'B'
          WHEN 3 Then 'C'
        END AS datagroup,
        grp.DataNumber,
        grp.Data_Sort
       FROM group1 AS grp
       WHERE CAST(grp.DataNumber AS char(1)) = @Data_Group_Number
        OR @Data_Group_Number = '0'
    --)

    Both encapsulation of a column name in a formula and adding an OR is going to lead to some nasty performance problems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, If you have a better solution please include it.  I would really like to understand how it could be done without the OR.
    DECLARE @Data_Group_Number CHAR(1);

    SET @Data_Group_Number = '0';

    WITH group1 AS
    (
        SELECT 1 DataNumber, 1 Data_Sort
        UNION
        SELECT 2 DataNumber, 2 Data_Sort
        UNION
        SELECT 3 DataNumber, 3 Data_Sort
    )
    ,
    group2 AS
    (
        SELECT CAST(grp.DataNumber AS CHAR(1)) AS DataNumber,
            Data_Sort
        FROM group1 AS grp
    )
    ,
    Data_Group AS
    (
        SELECT DataNumber
        FROM group2
        WHERE DataNumber = @Data_Group_Number
            OR @Data_Group_Number = '0'
        GROUP BY DataNumber
    )
    SELECT
        CASE grp.DataNumber
            WHEN '1' THEN 'A'
            WHEN '2' Then 'B'
            WHEN '3' Then 'C'
        END AS datagroup,
        grp.DataNumber,
        grp.Data_Sort
    FROM group2 AS grp
    WHERE grp.DataNumber IN (SELECT DataNumber
                                FROM Data_Group)
    ;

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • below86 - Monday, April 2, 2018 1:01 PM

    Jeff, If you have a better solution please include it.  I would really like to understand how it could be done without the OR.
    DECLARE @Data_Group_Number CHAR(1);

    SET @Data_Group_Number = '0';

    WITH group1 AS
    (
        SELECT 1 DataNumber, 1 Data_Sort
        UNION
        SELECT 2 DataNumber, 2 Data_Sort
        UNION
        SELECT 3 DataNumber, 3 Data_Sort
    )
    ,
    group2 AS
    (
        SELECT CAST(grp.DataNumber AS CHAR(1)) AS DataNumber,
            Data_Sort
        FROM group1 AS grp
    )
    ,
    Data_Group AS
    (
        SELECT DataNumber
        FROM group2
        WHERE DataNumber = @Data_Group_Number
            OR @Data_Group_Number = '0'
        GROUP BY DataNumber
    )
    SELECT
        CASE grp.DataNumber
            WHEN '1' THEN 'A'
            WHEN '2' Then 'B'
            WHEN '3' Then 'C'
        END AS datagroup,
        grp.DataNumber,
        grp.Data_Sort
    FROM group2 AS grp
    WHERE grp.DataNumber IN (SELECT DataNumber
                                FROM Data_Group)
    ;

    Here's the link that I consider to be the definitive article on the subject especially since it's saved my hinny on more than one occasion.
    https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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