Removing UNION and getting the same result

  • Hi,

    I need to update my sql script and remove the UNION but get the same result -

    Current result from script using Union -

    Screenshot 2022-07-04 122145

    DDL- For Accounts and bp_table1

    /****** Object:  Table [dbo].[bp_Account]    Script Date: 04/07/2022 10:48:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[bp_Account](
    [bp_id] [int] NOT NULL,
    [bp_sym] [varchar](8) NULL,
    [Account_type] [varchar](50) NULL,
    [customer_account_stru_code] [varchar](50) NULL,
    [customer_subtype_code] [varchar](50) NULL,
    [customer_type_code] [varchar](50) NULL,
    CONSTRAINT [PK_bp_account] PRIMARY KEY CLUSTERED
    (
    [bp_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (121, N'AQ.1111', N'Customer', N'SIPP_ACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (122, N'AA.1134', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (123, N'AB.1654', N'Customer', N'PENSION', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (124, N'AC.5464', N'Customer', N'JACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (125, N'AV.4566', N'Customer', NULL, NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (126, N'BC.3456', N'Customer', N'JACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (127, N'AS.5785', N'Customer', N'JACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (128, N'AF.2222', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (129, N'AK.9999', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (130, N'AH.8888', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (131, N'AJ.0000', N'Customer', N'SIPP_ACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (132, N'AG.0102', N'AUX', NULL, NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (133, N'AQ.0909', N'Customer', N'SIPP_ACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (134, N'AS.7654', N'Customer', NULL, NULL, NULL)
    GOGO
    /****** Object: Table [dbo].[bp_table1] Script Date: 01/07/2022 16:23:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[bp_table1](
    [bp_id] [int] NOT NULL,
    [customerId] [int] NOT NULL,
    [IsOwner] [bit] NOT NULL,
    [IsAuth] [bit] NOT NULL,
    [IsBenOwner] [bit] NULL,
    [Account_type] [varchar](50) NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (121, N'AQ.1111', N'Customer', N'SIPP_ACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (122, N'AA.1134', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (123, N'AB.1654', N'Customer', N'PENSION', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (124, N'AC.5464', N'Customer', N'JACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (125, N'AV.4566', N'Customer', NULL, NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (126, N'BC.3456', N'Customer', N'JACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (127, N'AS.5785', N'Customer', N'JACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (128, N'AF.2222', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (129, N'AK.9999', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (130, N'AH.8888', N'Customer', N'INDIVI', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (131, N'AJ.0000', N'Customer', N'SIPP_ACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (132, N'AG.0102', N'AUX', NULL, NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (133, N'AQ.0909', N'Customer', N'SIPP_ACC', NULL, NULL)
    GO
    INSERT [dbo].[bp_Account] ([bp_id], [bp_sym], [Account_type], [customer_account_stru_code], [customer_subtype_code], [customer_type_code]) VALUES (134, N'AS.7654', N'Customer', NULL, NULL, NULL)
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (121, 55555, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (122, 444444, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (123, 8766, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (123, 8766, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (123, 8766, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (124, 57483, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (125, 9999, 0, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (125, 9999, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (126, 45747, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (127, 92344, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (128, 53453, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (128, 53453, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (128, 637373, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (128, 53453, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (128, 737373, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (128, 53453, 1, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (129, 5557, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (129, 885444, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (129, 5557, 0, 1, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (129, 5557, 0, 0, 0, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (130, 677777, 0, 0, 1, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (131, 6579, 0, 0, 1, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (132, 45676, 1, 0, 0, N'AUX')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (133, 88888, 0, 0, 1, N'customer')
    GO
    INSERT [dbo].[bp_table1] ([bp_id], [customerId], [IsOwner], [IsAuth], [IsBenOwner], [Account_type]) VALUES (134, 77799, 0, 0, 1, N'customer')
    GO

    Code to Update - Remove UNION but keep the results from script the same.

    ;with cte_mas as (
    select *,
    case
    when IsOwner=1 then 'Keep Row'
    else ''
    end as 'Action',
    case
    when IsOwner=1 then 1
    else 2
    end as 'RowPriority'
    from bp_table1
    where IsOwner<>0 or IsAuth<>0),

    cte_temp as (
    select bp_id,
    customerid,
    IsOwner,
    IsAuth,
    IsBenOwner,
    Account_type,
    [Action],
    ROW_NUMBER() OVER (PARTITION BY bp_id,customerid ORDER BY RowPriority) row_num
    from cte_mas)


    select ct.bp_id,
    customerid,
    IsOwner,
    IsAuth,
    IsBenOwner,
    CASE WHEN IsOwner =1 THEN 'Owner'
    when isAuth =1 then 'Authoirty'
    else 'Business' end as Role
    from cte_temp ct
    left join bp_Account bpa ON bpa.bp_id=ct.bp_id
    where row_num=1
    and CT.Account_type='customer'
    and [customer_account_stru_code] NOT IN ('SIPP_ACC','PENSION')

    union

    select ct.bp_id,
    customerid,
    IsOwner,
    IsAuth,
    IsBenOwner,
    CASE WHEN IsOwner =1 THEN 'Owner'
    when isAuth =1 then 'Authoirty'
    else 'Business' end as Role
    from bp_table1 ct
    left join bp_Account bpa ON bpa.bp_id=ct.bp_id
    WHERE CT.Account_type='customer'
    and [customer_account_stru_code] IN ('SIPP_ACC','PENSION')
    and CT.IsBenOwner=1

    Thanks

  • OK ... but what is your question?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil,

    I would like to create the same script without using a UNION, but I'm not sure how to do that.

  • azdeji wrote:

    Hi Phil,

    I would like to create the same script without using a UNION, but I'm not sure how to do that.

    It is impossible. Remove UNION from the script and it is no longer the same.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It might be possible but I'm not going through the code to figure out which columns are coming from which tables.  Using table aliases in all columns when joins are present would have helped a whole lot.

    --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)
    Intro to Tally Tables and Functions

  • This SQL is functionally equivalent and there is no UNION:

    drop table if exists #t;

    WITH cte_mas AS
    (
    SELECT *,
    CASE WHEN IsOwner = 1 THEN 'Keep Row'
    ELSE ''
    END AS 'Action',
    CASE WHEN IsOwner = 1 THEN 1
    ELSE 2
    END AS 'RowPriority'
    FROM bp_table1
    WHERE IsOwner <> 0
    OR IsAuth <> 0
    ),
    cte_temp AS
    (
    SELECT bp_id,
    customerid,
    IsOwner,
    IsAuth,
    IsBenOwner,
    Account_type,
    [Action],
    ROW_NUMBER() OVER(PARTITION BY bp_id, customerid ORDER BY RowPriority) row_num
    FROM cte_mas
    )
    SELECT distinct
    ct.bp_id,
    customerid,
    IsOwner,
    IsAuth,
    IsBenOwner,
    CASE WHEN IsOwner = 1 THEN 'Owner'
    WHEN isAuth = 1 THEN 'Authoirty'
    ELSE 'Business'
    END AS Role
    INTO #t
    FROM cte_temp ct
    LEFT JOIN bp_Account bpa ON bpa.bp_id = ct.bp_id
    WHERE row_num = 1
    AND CT.Account_type = 'customer'
    AND [customer_account_stru_code] NOT IN('SIPP_ACC', 'PENSION');


    INSERT INTO #t
    SELECT distinct
    ct.bp_id,
    customerid,
    IsOwner,
    IsAuth,
    IsBenOwner,
    CASE WHEN IsOwner = 1 THEN 'Owner'
    WHEN isAuth = 1 THEN 'Authoirty'
    ELSE 'Business'
    END AS Role
    FROM bp_table1 ct
    LEFT JOIN bp_Account bpa ON bpa.bp_id = ct.bp_id
    WHERE CT.Account_type = 'customer'
    AND [customer_account_stru_code] IN('SIPP_ACC', 'PENSION')
    AND CT.IsBenOwner = 1
    EXCEPT
    select t.bp_id,
    t.customerid,
    t.IsOwner,
    t.IsAuth,
    t.IsBenOwner,
    t.Role
    from #t t
    ;

    select * from #t;

    drop table #t;

     

  • Heh... there is an EXCEPT, though... same "class" of method.

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Heh... there is an EXCEPT, though... same "class" of method.

    Yes, but it doesn't have a union which is all that was asked.

    Why do you think the OP wants to remove union?

  • Probably so he can get away from having to run two separate queries and then merge the results with a distinct UNION.  That may or may not be faster, though.  Divide'n'Conquer can be a really good thing.

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Probably so he can get away from having to run two separate queries and then merge the results with a distinct UNION.  That may or may not be faster, though.  Divide'n'Conquer can be a really good thing.

    Yes, I wasn't really thinking about performance either as that wasn't mentioned in the question.

  • Jonathan AC Roberts wrote:

    Yes, I wasn't really thinking about performance either as that wasn't mentioned in the question.

    There is no question!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Jonathan AC Roberts wrote:

    Yes, I wasn't really thinking about performance either as that wasn't mentioned in the question.

    There is no question!

    I took "I need to update my sql script and remove the UNION but get the same result" as the question.

  • Jonathan AC Roberts wrote:

    Phil Parkin wrote:

    Jonathan AC Roberts wrote:

    Yes, I wasn't really thinking about performance either as that wasn't mentioned in the question.

    There is no question!

    I took "I need to update my sql script and remove the UNION but get the same result" as the question.

    You've used quotation marks, yet your text does not appear prior to it in the thread. It's probably the right question, I agree, but I've failed at 'guess the question' so many times in the past that I've stopped doing it. How hard would it have been for the OP just to write that?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Jonathan AC Roberts wrote:

    Phil Parkin wrote:

    Jonathan AC Roberts wrote:

    Yes, I wasn't really thinking about performance either as that wasn't mentioned in the question.

    There is no question!

    I took "I need to update my sql script and remove the UNION but get the same result" as the question.

    You've used quotation marks, yet your text does not appear prior to it in the thread. It's probably the right question, I agree, but I've failed at 'guess the question' so many times in the past that I've stopped doing it. How hard would it have been for the OP just to write that?

    Look at the second line of the original post.

  • Oops, time for more coffee.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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