How to get mask related with every part on same row ?
-
I work on SQL server 2012 I face issue I can't display Part Number with Mask related for family
CREATE TABLE [dbo].[Partspc](
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)
CREATE TABLE [dbo].[Masksspc](
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'_', 4, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES ('blank', 4, 7524090)
DECLARE @GetFinalResultParts as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
)
insert into @GetFinalResultParts
SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
FROM dbo.Partspc r with(nolock)
;WITH cte AS (
SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber
FROM @GetFinalResultParts t1
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber
FROM @GetFinalResultParts t INNER JOIN
cte s ON T.GroupID = s.GroupID + 1
)
SELECT familyid,PartNumber
into getfinaldatapc
from cte
where groupid =(select max(GroupID) from dbo.Partspc with(nolock))
group by familyid,PartNumber
DECLARE @GetFinalMasks as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[MaskNumber] [nvarchar](200) NOT NULL INDEX IXkmask NONCLUSTERED)
insert into @GetFinalMasks
SELECT distinct m.familyid,m.GroupID,IIF(m.PortionKey='blank','',m.PortionKey)
FROM dbo.Masksspc m with(nolock)
;WITH cte AS (
SELECT t1.familyid,t1.GroupID,cast((t1.MaskNumber) as nvarchar(200)) MaskNumber
FROM @GetFinalMasks t1
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.MaskNumber+t.MaskNumber) as nvarchar(200)) MaskNumber
FROM @GetFinalMasks t INNER JOIN
cte s ON T.GroupID = s.GroupID + 1
)
SELECT familyid,MaskNumber
into getfinaldatapcmask
from cte
where groupid =(select max(GroupID) from dbo.Masksspc with(nolock))
group by familyid,MaskNumber
SELECT * FROM getfinaldatapc
select * from getfinaldatapcmaskexpected result as below :
familyid PartNumber MaskNumber
7524090 T496B754 T496B754
7524090 T496B754K T496B754_
7524090 T496B754M T496B754_
7524090 T496B755 T496B755
7524090 T496B755K T496B755_
7524090 T496B755M T496B755_
7524090 T496X754 T496X754
7524090 T496X754K T496X754_
7524090 T496X754M T496X754_
7524090 T496X755 T496X755
7524090 T496X755K T496X755_
7524090 T496X755M T496X755_mask and part must be equal on length
blank converted to space
on group 4 for part have one character will be underscore _ on mask
so k on part will be underscore _ on mask
m on part will be underscore _ on mask
so please how to display part number with mask related
-
Does this work:
SELECT
[finalDatapc].[familyid]
, [finalDatapc].[PartNumber]
, [finalDatapcMask].[MaskNumber]
FROM[getfinaldatapc] AS [finalDatapc]
JOIN[getfinaldatapcmask] AS [finalDatapcMask]
ON [finalDatapcMask].[familyid] = [finalDatapc].[familyid]
AND
(
LEFT([finalDatapc].[PartNumber], LEN([finalDatapc].[PartNumber]) - 1) = LEFT([finalDatapcMask].[MaskNumber], LEN([finalDatapcMask].[MaskNumber]) - 1)
AND RIGHT([finalDatapc].[PartNumber], 1)NOT LIKE '[0-9]'
)
OR([finalDatapc].[PartNumber] = [finalDatapcMask].[MaskNumber]);Basically JOINing the 2 tables on the Family ID matching and then IF you remove the last character from both strings, the strings match AND the last character must not be numeric (ie ends in a K or M) OR if the 2 values match.
I see 12 results in your data set above and 12 in mine, so I think this approach works given the sample data, but will break as soon as the K or M becomes a number OR if the K or M is more than 1 character in length. There are limitations on what I provided but it works with the sample data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
thank you for reply
are there are another solution dynamic for that
on my case there are partnumber and masknumber like below
familyid PartNumber MaskNumber
7524090 T496B754Kgfd T496B754____also I ask if there are another solution join part and mask by id
or that is difficult
-
can i generate unique id for table getfinaldatapc by checksum or what every
and generate unique id for table getfinaldatapcmask also by checksum
then join between two tables by id generated
if this possible can you help me applying
-
It is definitely possible, but does become more challenging based on what you have in your original query. What would probably be easier would be something more like this:
DECLARE @MaskChar CHAR(1) = '_'
CREATE TABLE #partspc(
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)
DECLARE @GetFinalResultParts as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
)
insert into @GetFinalResultParts
SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
FROM #Partspc r with(nolock)
DECLARE @FinalData TABLE (familyID FLOAT, PartNumber NVARCHAR(255), lenLast INT)
;WITH cte AS (
SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber, 1 AS lenLast
FROM @GetFinalResultParts t1
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber, LEN(.[PartNumber]) AS lenLast
FROM @GetFinalResultParts t INNER JOIN
cte s ON T.GroupID = s.GroupID + 1
)
INSERT INTO @FinalData ([familyID], [PartNumber], [lenLast])
SELECT familyid,PartNumber, lenLast
from cte
where groupid =(select max(GroupID) from #Partspc)
SELECT familyID, PArtNumber, CASE WHEN lenLast = LEN(partnumber) THEN partnumber ELSE LEFT(partnumber,lenLast) + REPLICATE(@MaskChar,LEN(partnumber)-lenlast) end
FROM @finalData
DROP TABLE #partspcNOW, the above works by assigning that mask character to a variable at the start rather than building things up like you were. If building it up is a requirement (such as that the mask MAY occur on characters other than the last set OR that multiple masks may need to be applied in some cases), the above would need to be tweaked.
Basically what I ended up doing was grabbing the last length of partnumber which would be the length of it PRIOR to it having the last chunk added on. At this point, we have all of the data we need to mask things as you indicated.
Now, does the above work OR do you need it to work with the tables you had? If so, it is possible, and isn't that much more challenging UNLESS you have multiple mask values you need to apply (such as _ and *). Again, not impossible, but definitely more challenging.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
thank you for reply
what benefit from above
familyIDPArtNumber(No column name)
7524090T496X755________
7524090T496X755KT________
7524090T496X755MT________
7524090T496X754________
7524090T496X754KT________
7524090T496X754MT________
7524090T496B755________
7524090T496B755KT________
7524090T496B755MT________
7524090T496B754________
7524090T496B754KT________
7524090T496B754MT________why result ended with underscore
can you please tell me what idea from that
-
Sorry, my query had a typo in it - I missed giving the quantifier for the part number in the CTE.
This one should work better:
DECLARE @MaskChar CHAR(1) = '_'
CREATE TABLE #partspc(
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)
DECLARE @GetFinalResultParts as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
)
insert into @GetFinalResultParts
SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
FROM #Partspc r with(nolock)
DECLARE @FinalData TABLE (familyID FLOAT, PartNumber NVARCHAR(255), lenLast INT)
;WITH cte AS (
SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber, 1 AS lenLast
FROM @GetFinalResultParts t1
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber, LEN(.[PartNumber]) AS lenLast
FROM @GetFinalResultParts t INNER JOIN
cte s ON T.GroupID = s.GroupID + 1
)
INSERT INTO @FinalData ([familyID], [PartNumber], [lenLast])
SELECT familyid,PartNumber, lenLast
from cte
where groupid =(select max(GroupID) from #Partspc)
SELECT familyID, PartNumber, CASE WHEN lenLast = LEN(partnumber) THEN partnumber ELSE LEFT(partnumber,lenLast) + REPLICATE(@MaskChar,LEN(partnumber)-lenlast) END AS [MaskedPartNumber]
FROM @finalData
DROP TABLE #partspcI also did a mistake that I hate - I didn't give the last column a name. I fixed that too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
thank you for reply
what i need to get part with related mask
so until now i not give my part number any unique number
and give mask number same unique number i give to part number
then join it by that unique number
-
As for generating a checksum, this is entirely possible, but it depends on how you want to handle it and what you plan to do with it.
If I understand the requirement correctly, you are wanting to go from a masked value back to a real value. This is definitely possible, but you will run into a snag. Using the original data set, lets say the end user sees:
T496B754_
When you go back to the database to try to determine what ID to use for that, you will have 2 values - one for T496B754M and one for T496B754K. Which one will you choose? If end users need to pick one based on a checksum, they will have no idea which one to pick as it'll just be a string of letters and numbers.
As for how you would go about calculating that, my approach would be to use some form of repeatable checksum generation such as MD5. Build the MD5 on the part number and as long as all part numbers are unique, then the MD5 should be unique too. Then present the MD5 along with the masked part number and you can always go back to the original part number by re-calculating the MD5 and matching it up. You don't even need to store that MD5 unless you have a LOT of data as calculating an MD5 is pretty quick, but will be slow if you have millions or billions of rows of data.
Alternately, you could use an ID value that is an INT (or bigint) and set it up as an IDENTITY(1,1) so it is ever increasing and then present the ID along with the masked value to the end users.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
can you help me
applying that please
-
It depends entirely on the approach you want to take and how you are planning on storing the data permanently.
Is this data going into a table or is it generated on the fly for the end user?
If it is going into a table, I'd just add a column onto the table with a name like PartNumberID of type INT IDENTITY(1,1). That'll be the easiest solution that will ensure row uniqueness. But you will still run into the problem that part number T496B754_ matches up with both T496B754M and T496B754K, although those SHOULD have a unique ID.
But it depends heavily on what you plan to use that value for and how you are planning on storing your data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
i need to use that below
Alternately, you could use an ID value that is an INT (or bigint) and set it up as an IDENTITY(1,1) so it is ever increasing and then present the ID along with the masked value to the end users.
but i don't know how to apply it
-
That approach works if you are storing it in a table. Just add that as a column to the table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply