SQL Server is the best way to join VARCHARs and INTs
I am looking for an efficient way to speed up my request. Let me describe the situation.
Every night we send reports from different suppliers to a spreadsheet. It's a lot of columns, but these columns below are the main columns I am using to join the query.
tblVendorShippingInfo (note that all theses below are VARCHAR as sometimes reports contain Varchar even in QtyShipped)
PONum vSKU vSKUDesc ShipQuantity
P111111 A Test1 5
P111111 A Test1 2
P111111 B Test3 5
P100002 C Test4 6
P222222 D Test5 7
P222222 D Test5 6
P222222 E Test6 7
PABC123 F Test7 8
XYZ123 G Test8 8
GHEHEH H Test9 8
tblPODetail - Our table for the purchase order. Please note that POID and DistyShippedQty are Integer. POID only has a 6-digit number.
POID SKU SKUDesc DistyShippedQty
111111 A Test1
111111 B Test3
100002 C Test4
222222 D Test5
222222 E Test6
If I use the following query to get and update the DistyShippedQty it works, but slow because the conversion ... VARCHAR match ... etc.
select POID, SKU
, (
SELECT SUM(ShipQuantity) AS ShipQuantity
FROM tblVendorShippingInfo
WHERE substring(PONum, 2, 6) = Convert(varchar(10), pod.POID)
AND vSKU = pod.SKU
) AS QtyCount
FROM tblPODetail pod
So I'm looking for a better way to just filter the entries in the tblVendorShippingInfo table that only has a PONum with 6 digits starting from the second position (after P or any char), ignore all entries with characters (e.g. PABC123, XYZ123, GHEHEH), then index its so that it can be used to connect to the tblPODetail table.
I tried to create an indexed view with a filter, but when I run a query with that view, it failed.
Alter VIEW vw_tblVendorShippingInfo WITH SCHEMABINDING AS
select dfID, substring(PONum,2, 6) AS POID
, vSKU, ShipQuantity
FROM dbo.tblVendorShippingInfo
WHERE 1 = 1
AND PONum like '%[^0-9]%'
Any guidance would be appreciated.
source to share
I think you are on the right track. You just need the correct expression for like
:
SELECT POID, SKU,
(SELECT SUM(ShipQuantity) AS ShipQuantity
FROM tblVendorShippingInfo vsi
WHERE substring(vsi.PONum, 2, 6) = Convert(varchar(10), pod.POID) AND
vSKU = pod.SKU
) as QtyCount
FROM tblPODetail pod
WHERE PONum like '[A-Za-z][0-9][0-9][0-9][0-9][0-9][0-9]';
If you want to index this as an integer use a calculated column:
alter table tblVendorShippingInfo add column POID_num as try_convert(int, substring(PONum, 2, 6);
create index idx_tblVendorShippingInfo_POID on tblVendorShippingInfo(POID_num);
try_convert()
assumes SQL Server 2012+. You can do something similar with earlier versions of SQL Server.
source to share