October 23, 2014 at 7:30 am
I have a problem with trying to pull postcodes from a table when I have a lookup table which provides me with a StartPostCode and an EndPostCode.
For instance if, in the input table I have a postcode BETWEEN two values eg: CV1 and CV10 I want to get a third value from the look up table.
My problem is my query seems to bring two values back even if the postcode is between the ranges specified.
To reproduce the problem first create the tables and populate with the data.
USE [CTSStaging]
GO
/****** Object: Table [dbo].[st_StobartPostCode] Script Date: 10/23/2014 12:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[st_StobartPostCode](
[StartPostCode] [nvarchar](255) NULL,
[EndPostCode] [nvarchar](255) NULL,
[TrfZn] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'AB0 0AA ', N'AB99 9ZZ ', N'AB ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'AL0 0AA ', N'AL99 9ZZ ', N'AL ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B0 0AA ', N'B9 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B10 0AA ', N'B19 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B20 0AA ', N'B29 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B30 0AA ', N'B36 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B37 0AA ', N'B37 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B38 0AA ', N'B38 9ZZ ', N'B2 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B39 0AA ', N'B39 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B40 0AA ', N'B40 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B41 0AA ', N'B41 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B42 0AA ', N'B42 9ZZ ', N'B2 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B43 0AA ', N'B43 9ZZ ', N'B2 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B44 0AA ', N'B44 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B45 0AA ', N'B45 9ZZ ', N'B2 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B46 0AA ', N'B46 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B47 0AA ', N'B47 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B48 0AA ', N'B48 9ZZ ', N'B1 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B49 0AA ', N'B49 9ZZ ', N'B2 ')
INSERT [dbo].[st_StobartPostCode] ([StartPostCode], [EndPostCode], [TrfZn]) VALUES (N'B60 0AA ', N'B70 9ZZ ', N'B2 ')
/****** Object: Table [dbo].[st_Customer] Script Date: 10/23/2014 12:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[st_Customer](
[SHIP_TO] [varchar](50) NULL,
[SHIP_TO_NAME] [varchar](255) NULL,
[SHIP_TO_NAME2] [varchar](50) NULL,
[SHIP_TO_CITY] [varchar](50) NULL,
[SHIP_TO_ADDRESS] [varchar](255) NULL,
[SHIP_TO_POST_CODE] [char](10) NULL,
[SHORT_POSTCODE] [varchar](255) NULL,
[SOLD_TO] [varchar](255) NULL,
[SOLD_TO_NAME] [varchar](255) NULL,
[SOLD_TO_ADDRESS] [char](1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[h_Customer](
[SHIP_TO] [varchar](50) NULL,
[SHIP_TO_NAME] [varchar](50) NULL,
[SHIP_TO_NAME2] [varchar](50) NULL,
[SHIP_TO_CITY] [varchar](50) NULL,
[SHIP_TO_ADDRESS] [varchar](50) NULL,
[SHORT_POSTCODE] [varchar](50) NULL,
[SOLD_TO] [varchar](255) NULL,
[SOLD_TO_NAME] [varchar](255) NULL,
[SOLD_TO_ADDRESS] [varchar](2000) NULL,
[SHIP_TO_POSTCODE] [char](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
go
INSERT [dbo].[h_Customer] ([SHIP_TO], [SHIP_TO_NAME], [SHIP_TO_NAME2], [SHIP_TO_CITY], [SHIP_TO_ADDRESS], [SHORT_POSTCODE], [SOLD_TO], [SOLD_TO_NAME], [SOLD_TO_ADDRESS], [SHIP_TO_POSTCODE]) VALUES (N'0000793064', N'ASPENS SERVICES LTD', N'', N'KINGS NORTON', N'KINGS NORTON HIGH SCHOOL', N'B38 9DE', N'0000793064', N'ASPENS SERVICES LTD', N'A', NULL)
GO
/****** Object: Default [c_address] Script Date: 10/23/2014 14:03:22 ******/
ALTER TABLE [dbo].[h_Customer] ADD CONSTRAINT [c_address] DEFAULT ('A') FOR [SOLD_TO_ADDRESS]
GO
--Then when I run this query the codes have odd spacing issues...
TRUNCATE TABLE st_Customer
DECLARE @StartPostCode VARCHAR(10)
DECLARE @EndPostCode VARCHAR(10)
DECLARE @trfzn CHAR(3)
DECLARE @getpostcode CURSOR
SET @getpostcode = CURSOR FOR
SELECT REPLACE(StartPostCode,' ',''),REPLACE(EndPostCode,' ',''),trfzn from st_StobartPostCode
OPEN @getpostcode
FETCH NEXT
FROM @getpostcode into @StartPostCode,@EndPostCode,@trfzn
WHILE @@FETCH_STATUS = 0
BEGIN
--print @StartPostCOde + @EndPostCode + @trfzn
INSERT into st_Customer
(SHIP_TO
,SHIP_To_NAME
,SHIP_TO_NAME2
,SHIP_TO_CITY
,ship_to_Address
,Ship_to_post_code
,SHORT_POSTCODE
,SOLD_TO
,SOLD_TO_NAME
--,SOLD_TO_ADDRESS
)
select
SHIP_TO
,SHIP_TO_NAME
,SHIP_TO_NAME2
,SHIP_To_CITY
,SHIP_TO_Address
,@trfzn
,SHORT_POSTCODE
,SOLD_TO
,SOLD_TO_NAME
from h_Customer
where REPLACE(SHORT_POSTCODE,' ','') BETWEEN @StartPostCode and @EndPostCode
FETCH NEXT
FROM @getpostcode into @StartPostCode,@EndPostCode,@trfzn
END
CLOSE @getpostcode
DEALLOCATE @getpostcode
--Finally when I do my checks using this query:
select * from st_Customer
--I get the following duplicate rows when clearly the postcode was between the B2 values
-- Anyone have any ideas what I could do to prevent the duplicate?
--0000793064ASPENS SERVICES LTDKINGS NORTONKINGS NORTON HIGH SCHOOL
--B1 B38 9DE0000793064ASPENS SERVICES LTDA
--0000793064ASPENS SERVICES LTDKINGS NORTONKINGS NORTON HIGH SCHOOL--B2 B38 9DE0000793064ASPENS SERVICES LTDA
October 23, 2014 at 7:52 am
pnr8uk (10/23/2014)
...in the input table I have a postcode BETWEEN two values eg: CV1 and CV10 I want to get a third value from the look up table. ...
There won't be many rows between 'CV1' and 'CV10'. Try 'CV01' and 'CV10'. If you're only working with UK postcodes, the extra coding isn't much.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 23, 2014 at 7:59 am
Thanks Chris but the CV .... blah blah was just an example. The table I created with code has a subset of the postcodes I have ranges for.
Thanks anyway
October 23, 2014 at 9:49 am
The sample customer matches two rows in the postcodes table:
SELECT
c.SHORT_POSTCODE,
c1.SHORT_POSTCODESquish,
x.StartPostCodeSquish,
x.EndPostCodeSquish,
CASE WHEN c1.SHORT_POSTCODESquish BETWEEN x.StartPostCodeSquish AND x.EndPostCodeSquish THEN 'Match' ELSE '' END
FROM h_Customer c
CROSS APPLY (SELECT SHORT_POSTCODESquish = REPLACE(c.SHORT_POSTCODE,' ','')) c1
CROSS APPLY (
SELECT
StartPostCodeSquish = REPLACE(StartPostCode,' ',''),
EndPostCodeSquish = REPLACE(EndPostCode,' ',''),
trfzn
from st_StobartPostCode
) x
ORDER BY x.StartPostCodeSquish
Does this help any?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 3:37 am
Hi Chris
This does indeed help me a lot, however I can see that the code checks every single combination of the short postcodes against the squished post code until it finds a match, this causes the server to run out of memory is there anyway to change the query so it (the query) only use one range value?
I am also of course working on this .
Cheers
October 24, 2014 at 3:47 am
Can you post the actual code you are using?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2014 at 3:54 am
It's as yours above only the code is running against the real tables with the full content, yesterday I made smaller tables to help find a solution, which you did thank you so much for that. I haven't changed any of the code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply