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 -
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')
GOCode 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=1Thanks
-
OK ... but what is your question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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) -
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) - 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) - 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!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - 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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply