August 14, 2018 at 8:45 am
Hi,
I have a master table which looks like this
| Terminal_Prefix | PINPad_Prefix | Terminal_Protocol | LOC_SFTWR_VRSN |
| 30 | 30 | 0 | K0737 |
| 30 | 30 | 0 | K0737 |
| 30 | 30 | 1 | K0737 |
| 30 | 30 | 1 | K0737 |
| 30 | 30 | 1 | K0737 |
| 84 | 84 | 8 | K0373 |
| 84 | 84 | 8 | K0373 |
| 84 | 84 | 8 | K0373 |
| 84 | 84 | 8 | K0373 |
| 84 | 84 | 8 | K0373 |
and i would like to create a set of new fields based on the conditions outlined in a sheet( which is required to go into a table and i should be using this table as a lookup table )
The conditions are as follows
| Terminal Prefix | PINPad Prefix | Base 1 or 2 (excludes readers) | Software Version Criteria | Device Subtype | Terminal Protocol | Terminal serial different from PINpad serial | Hardware Wired Wireless | Software Solution Standalone Integrated mPOS Unattended | Integration Variant Standalone Semi-integrated Pay @ table Pospad Vendor Extended PINPad | Communication Method Cloud Direct to host Secure-Pass-Through Hybrid Integrated |
| 61 | 61 | N/A | Begins with U01, U02, U04, U10, U11, U14 and ends with S | N/A | N/A | N/A | Wired | Integrated | POSPad | Secure-Pass-Through |
| 61 | 61 | N/A | Begins with U01, U02, U04, U10, U11, U14 and ends with P | N/A | N/A | N/A | Wired | Integrated | POSPad | Direct to Host |
The lookup table should have the following field
Terminal Prefix, PinPad_Prefix,Software version Criteria and so on.
A sample condition read as follows .
If the terminal prefix = 61 and pinpadprefix = 61 and the software version begins with U01,U02,U04,U10,U11,U14 and ends with S then Hardware=Wired,Software Solution = Integrated, Integration Variant = POSPad and Communication Method = Secure-Pass-Through
My query that i am expecting to achieve is as follows .
select A.* from
mastertable A
join lookuptable B
on a.teminalprefix = b.terminal prefix and a.pinpadprefix = b.pinpadprefix and a.sotwareversion = b. softwareversion
( B.software version should include these conditions )
i hope i have explained it so that everybody could understand . please do let me know otherwise and i can explain with more examples.
August 14, 2018 at 8:57 am
Hate to burst your bubble, but implementing this kind of table lookup is fraught with potential problems. You are hoping to put some kind of criteria into one of the columns, and while at the moment, you might be able to use LIKE in a query and put entries into this "new table" that can work with it, you might mislead yourself into believing that such a table can satisfy every possible future requirement, and having already invested the considerable time and effort in creating AND maintaining this potential nightmare, you suddenly discover a new requirement for which this table just can't work, and build a new and different one for that purpose. I'd suggest getting off of that train before it leaves the station and you are stuck with it. Criteria tables are notoriously bad when they involve having to use LIKE. Solve this problem by tying appropriate categorization to the source data where it starts, and force someone on the product side to maintain it, and be sure the database design is good so you don't end up with more and more of this kind of issue.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 9:06 am
sgmunson - Tuesday, August 14, 2018 8:57 AMHate to burst your bubble, but implementing this kind of table lookup is fraught with potential problems. You are hoping to put some kind of criteria into one of the columns, and while at the moment, you might be able to use LIKE in a query and put entries into this "new table" that can work with it, you might mislead yourself into believing that such a table can satisfy every possible future requirement, and having already invested the considerable time and effort in creating AND maintaining this potential nightmare, you suddenly discover a new requirement for which this table just can't work, and build a new and different one for that purpose. I'd suggest getting off of that train before it leaves the station and you are stuck with it. Criteria tables are notoriously bad when they involve having to use LIKE. Solve this problem by tying appropriate categorization to the source data where it starts, and force someone on the product side to maintain it, and be sure the database design is good so you don't end up with more and more of this kind of issue.
Thanks a lot. I was thinking the same . I would like to know how this is being done before evaluating the negative part of it. The business wants to maintain the lookup table and i am not in a position to advice otherwise.
August 14, 2018 at 9:14 am
Show them the nature of the problem. They'll very quickly learn that with a LIKE capability in such a lookup table, they will inherently start using % and/or _ at the beginning of a criteria column and then watch them complain about performance. That's not something you want to own. At the very least, show your boss what can happen, and why he/she will NOT want that kind of nightmare. If the business is your boss, you'll have a much harder time. They may not be able to understand that the database just is NOT going to have a good way to do what they want in a performant way. When you give the user an easy path to avoiding index usage, you are in trouble.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 10:11 am
You're likely going to need dynamic SQL to fully implement essentially free-form conditions, but to do what you've specified so far, you can do it with just standard LIKEs:
INSERT INTO lookuptable ( terminalprefix, pinpadprefix, softwareversion, ...)
SELECT '61%' AS terminalprefix, '61%' AS pinpadprefix, 'U0[124]%S' AS softwareversion
UNION ALL
SELECT '61%' AS terminalprefix, '61%' AS pinpadprefix, 'U1[014]%S' AS softwareversion
SELECT ...
FROM mastertable mt
INNER JOIN lookuptable lkup ON mt.terminalprefix LIKE lkup.terminal prefix AND
mt.pinpadprefix LIKE lkup.pinpadprefix AND
mt.softwareversion LIKE lkup.softwareversion
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 14, 2018 at 10:49 am
You can do it with an AND/OR table. Each rule has an id shared by multiple rows, values in the columns specify a bunch of ANDed conditions that must be met for the rule to be satisfied. OR criteria are specified as rows, each containing the alternate criteria. Joining on this table will therefore satisfy an ANY= check if the rule applies.
You will ideally want to handle breaking the prefix/suffix out prior to joining although it's possible not to if you really want to keep it (since it essentially just forms part of your join conditionals).
August 14, 2018 at 11:07 am
Or, someone could recognize that data that has to have pieces of it broken out and examined has failed to enforce good database design by allowing data elements in the database that are not properly containing these as separate attributes... correct this, and most of the rest of this problem disappears...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 11:34 am
sgmunson - Tuesday, August 14, 2018 11:07 AMOr, someone could recognize that data that has to have pieces of it broken out and examined has failed to enforce good database design by allowing data elements in the database that are not properly containing these as separate attributes... correct this, and most of the rest of this problem disappears...
But that won't happen, because developers still "design"(*) tables, and they don't design for data, they design for code/coding. And they don't have any experience in logical design at all, so they completely skip that step. Thus one repeatedly ends up with horrible physical table "designs".
But, very sadly, that's "standard" practice at far too many places, and I don't see that changing in this case either. Should they go back and do a proper design of this data? Sure. Will they? Almost certainly no chance.
(*) "design" = slap an identity column on the table, then add all the columns needed for the main code module(s). No need to do a true logical design, no need to step through any formal normalization: they don't need to do that, they're experienced developers after all!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 14, 2018 at 2:36 pm
ScottPletcher - Tuesday, August 14, 2018 10:11 AMYou're likely going to need dynamic SQL to fully implement essentially free-form conditions, but to do what you've specified so far, you can do it with just standard LIKEs:
INSERT INTO lookuptable ( terminalprefix, pinpadprefix, softwareversion, ...)
SELECT '61%' AS terminalprefix, '61%' AS pinpadprefix, 'U0[124]%S' AS softwareversion
UNION ALL
SELECT '61%' AS terminalprefix, '61%' AS pinpadprefix, 'U1[014]%S' AS softwareversionSELECT ...
FROM mastertable mt
INNER JOIN lookuptable lkup ON mt.terminalprefix LIKE lkup.terminal prefix AND
mt.pinpadprefix LIKE lkup.pinpadprefix AND
mt.softwareversion LIKE lkup.softwareversion
Thanks. This works.
Could someone give me a pattern matching query for something like below
if the software version criteria does not begin with U0[124] or U1[014] or '' then Test . what i have achieved so far is this
a field which has [^U]% ( meaning doe snot start with U) i am not able to figure out the second character and other characters in this pattern matching . any help is much appreciated.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply