how to get GPNSignature that first part on it start by portion key ?
-
I work on sql server 2012 I face issue i can't get
GPNSignature that have portionkey exactly
so if portion key is 100 then GPNSignature must start by 100
if portion key is 10 then GPNSignature must start by 10
so i need to get correct value
CREATE TABLE [dbo].[checkportions](
[GPNSignatureID] [bigint] NOT NULL,
[GroupId] [int] NULL,
[PortionKey] [nvarchar](50) NOT NULL,
[PortionId] [int] NOT NULL,
[GpnSignatureOther] [bigint] NOT NULL,
[GPNSignature] [nvarchar](38) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[checkportions] ([GPNSignatureID], [GroupId], [PortionKey], [PortionId], [GpnSignatureOther], [GPNSignature]) VALUES (2313151, 1, N'10', 5384866, 2313151, N'100%N%J%')
INSERT [dbo].[checkportions] ([GPNSignatureID], [GroupId], [PortionKey], [PortionId], [GpnSignatureOther], [GPNSignature]) VALUES (2313151, 1, N'100', 5396955, 2313151, N'100%N%J%')as image above this is correct
because portion key is 100 and gpnsignature start by 100
and portion key 10 and gpnsignature start by 100 so it is wrong
so How to do that please
-
Do you understand how the following works?
SELECT *
FROM [dbo].[checkportions]
WHERE GPNSignature LIKE PortionKey+'[^0-9]%'
;I'll also state that if this type of thing has to be done on a regular basis, you might want to add a persisted computed column to try to prevent the table scan this will always cause.
--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) -
Something I'd like to add to what Jeff said - where do you want to handle this? Should this be handled at the INSERT level or at the SELECT level?
Also, knowing how the GPNSignature is being created would be helpful so we know what format it must be in. What I mean is you say "portion key 10 and gpnsignature start by 100 so it is wrong", but 100 starts with "10", so, depending on how that GPNSignature is created, the result MAY be valid.
A scenario where it may be valid - GPNSignature is auto-generated from a tool with a fixed length and the first digits are based on the portion ID. The portion ID is 10, so the first 2 digits of the GPNSignature are based on portion ID, the remaining digits are randomly generated. In this case, if the length MUST be 8 characters and the first 2 are "10", and the remaining 6 can be anything, it is not impossible for the 1st randomly generated character to be a 0 resulting in "100" as the start of the string.
Now, the question I had at the start about INSERT vs SELECT is because if the restriction is on the INSERT side, then you would need to modify your stored procedure and could do input validation prior to INSERT. Make sure that the portion ID was equal to the first N characters of the GPNSignature based on whatever requirements you have that make sense such as number of characters before the first occurrence of a %. If it is on the SELECT side, then Jeff's approach would work, although I like his persisted computed column approach as you could just have that as a BIT field to indicate if the row was valid based on PortionID and the first few characters of the GPNSignature.
Just my 2 cents.
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. - Jeff Moden wrote:
Do you understand how the following works?
SELECT *
FROM [dbo].[checkportions]
WHERE GPNSignature LIKE PortionKey+'[^0-9]%'
;Well do ya? Enquiring minds want to know! 😀
--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 isn't the first time the OP has posted requests for help on different forums The same post is on MSDocs: https://docs.microsoft.com/en-us/answers/questions/444629/how-to-get-gpnsignature-that-first-part-on-it-star.html. The OP in that case is https://docs.microsoft.com/answers/users/3345424/ahmedsalah-1628.html. I'd suspect that, given the wide range of subjects posted, the OP is either acting as a help desk agency (121 posts here and 296 on MS Docs) for others or they are in a job where they are completely out of their depth. I can sympathise with the latter but I'd suggest any help offered should encourage some learning, and maybe some effort, rather than giving the complete solution
-
To be honest, I had come to the same conclusion and wanted to see what the reaction would be with a mostly code only response rather than the uncoded suggestion I made on a different thread he started. Based on the number of posts on this same table, I suspect you may be correct on way or the other but suspect the latter may be true.
The hard part is that forums are meant to help others and at least he's been good about supplying readily consumable data to make helping him easier.
--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) -
thank you for support
according post on multi forum
it will not repeated
sorry
- ahmed_elbarbary.2010 wrote:
thank you for support
according post on multi forum
it will not repeated
sorry
To be honest, I don't really have a problem with people posting the same problem on multiple forums. After all, the second of "Peter's Laws" is "When given a choice, take both".
What some are really concerned about is you've been hammering about this same table for a few posts now. I think what people want to be assured of is that you're learning from the posts as well as getting coded help. For example, I posted an answer above with the question "Do you understand how the following works?"
All I got in return was crickets and that's a concern (at least for me). That's kind of expected on other forums but not here.
--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)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply