December 15, 2010 at 1:32 pm
Greetings.
My goal is to return a dataset that is validated against authorized transactions. Here's what I have.
I have a table (table 1) containing data along with 5 fields of transaction codes (code, sub-code, sub-sub-code, etc). Trx codes 1-3 almost always have values. Codes 4-5 only have values occasionally. The rest of the time they are NULL.
I have 5 tables (tables 2-6) corresponding to the 5 trx codes, which I then link to a master trxcode table (table 7) where I enter valid transactions. This should enable me to take the 5 trx codes and return a single ID from table 7 that can be used to trace for further processing.
I run into problems joining table 1 and table 7 when any nulls are present in the trx codes. The dataset returns 0 rows.
My questions are many, but let me get some focus:
1. What other (better) ways exist to link these trx codes to a master validation table?
2. Is what I'm trying to do even possible?
This is part of a larger transactional processing solution but I think this step solves a lot of my problems down the road as well.
December 15, 2010 at 1:41 pm
I'd have to see table designs and sample data before I could even begin to critique this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2010 at 1:59 pm
Sample:
Table1 (source data)
Branch,Amount,Code1,Code2,Code3,Code4,Code5
11, $10, 1, C, 1, NULL, NULL
11, $1, 1, C, 2, NULL, NULL
11, $15, 4, C, 1, NULL, 3
11, $2, 4, C, 3, S, 3
11, $5, 4, C, 2, NULL, NULL
Table2 (Code1 reference)
ID,Code1,Description
1, 1, blah
2, 4, blah
Table3 (Code2 reference)
ID,Code3,Description
1, A, blah
2, C, blah
Table4 (Code3 reference)
ID,Code3,Description
1, 1, blah
2, 2, blah
3, 3, blah
Table5 (Code4 reference)
ID,Code1,Description
1, R, blah
2, S, blah
3, 9, blah
Table6 (Code5 reference)
ID,Code1,Description
1, 3, blah
2, 7, blah
3, 8, blah
Table7 (master trx)
ID,Code1,Code2,Code3,Code4,Code5
1, 1, 2, 1, NULL, NULL
2, 2, 2, 3, NULL, 1
3, 2, 2, 2, NULL, NULL
4, 2, 2, 1, NULL, NULL
If I validate the transactions in Table1 against Table7 (joined to Tables 2-6), the query should return all the rows from Table1 except the ones with values $1 and $2.
I hope that helps.
December 15, 2010 at 9:46 pm
*bump*
I'm new to the forums and still getting my bearings. Is this something that can be asked in the 'Ask SSC' area?
December 16, 2010 at 7:08 am
Why separate tables for each of the code types? Why not one table, with a column that indicates which type of code it is?
Also, why not a table for transaction types, with the transaction ID and the type ID, one entry per row, instead of 7 columns for types? (This would be a more "normal" design.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2010 at 8:41 am
Thanks for the response.
I thought using the separate tables for transaction codes could help me avoid issues with NULL joins. Such is not the case.
Also, why not a table for transaction types, with the transaction ID and the type ID, one entry per row, instead of 7 columns for types? (This would be a more "normal" design.)
I'm not sure I follow you.
Are you suggesting 1 table for all transaction codes and columns for type?
Or is this something to do with the master transaction table, which I hope to use as a way to grab only authorized transactions from the source dataset?
Either way, I'm not clear as to how to join the source dataset with a table of acceptable transactions codes when some of those codes in the dataset may be NULL.
December 17, 2010 at 6:38 am
I think I'd need to know more about the business rules and the data being modeled before I could suggest more on either design or coding against it.
For one thing, "null" means "unknown value", not "no value". "No value" would be indicated by a code for "not authorized" or something like that. "Null" would mean "we don't know what code goes here", indicating incomplete data. That would be usual, but I don't know enough about your business rules to really suggest anything coherent on that point.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2010 at 8:33 am
I understand. The big picture often dictates the way some of the minutiae are configured.
Thanks for the follow up anyway.
Dropping out all other details, if I have a dataset containing fields with NULLs (SQL NULLs - not conceptually empty fields) and I want to link it to another table in order to generate a dataset of 'authorized' or valid records. Is this possible? Or do I have to insert a value for NULL fields in order to link the tables?
I'll have one more shot at this:
Table1
ID Value Code1 Code2 Code3
1 12 A NULL NULL
2 10 3 2 NULL
3 5 1 1 1
4 8 NULL C NULL
5 3 A NULL 1
I want to create a table to pull 'authorized' codes from the above table. How would I set that up?
Both Code fields are alphanumeric. I only want records where Code1 is A,1,3, Code2 is 1,2, and Code3 is 1. It should be noted that Code2 and Code3 are subsets of Code1. So record 5 is valid because it has a Code1 of A and Code3 of 1.
With the data above, I should return four rows.
When I tried this out, the NULL in the Code2 field resulted in no rows returned. My specific issue has five fields with values like this but this example should suffice and the rest would be building on more joins, I think?
December 17, 2010 at 8:45 am
You would need to generate either a real or virtual table of valid combinations, and join to that.
If, for example, "1,2,null" is valid, you need a row in your "valid combos" set that has that combination.
You might be able to achieve that by querying your seven code tables into a CTE, and then joining to that. Do the tables clearly define what codes can go with what other codes in what combinations?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2010 at 9:59 am
GSquared (12/17/2010)
You would need to generate either a real or virtual table of valid combinations, and join to that.If, for example, "1,2,null" is valid, you need a row in your "valid combos" set that has that combination.
You might be able to achieve that by querying your seven code tables into a CTE, and then joining to that. Do the tables clearly define what codes can go with what other codes in what combinations?
I had not gone so far as linking the codes in parent/child format but now that you mention it, I should.
I'm under some time constraints so I can't do this as elegantly as I'd like. But your responses have shown me where I was stumbling and how to approach things in the future.
Thanks.
December 22, 2010 at 8:14 am
Glad I could help, even if it was in a limited fashion.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 4:40 pm
If you removed the surrogate keys on tables 2-6 you could make table 7 a list of the valid code combinations rather than the surrogate keys for the code. That would make it easier to match back to table 1.
If you can't do that then it may be worth using a CTE to build a list of the "valid" combinations.
Finally - to resolve the NULLs in this I would resort to an ISNULL construction. No, its not pretty and no, its not optimal. If there is a massive amount of traffic on this then it may cause issues but for 99% of the time it will do the job and you won't notice.
January 10, 2011 at 9:00 am
Funny you should mention that....I ended up dropping Tables 2-6.
Table 7 now has 6 columns that can be joined back to the source table to return only valid transactions. These transactions come in for several different sources, with different routing details so I added a storeID to Table 7 as well. This results in each of the valid 6-field codes to be copied for each storeID in the table. Normalized? Definitely not. But given the time constraints I needed to get this done and done fast.
I still like the idea of a parent/child link between the transaction code types but it is non-linear and would take some time to hash out. A good idea if this I ever get support for an optimization project. Like that's going to happen lol
As for the NULLs, I wrote a sanitization script to take any NULL or zero length string and replace it with a dash '-'. This made it possible to join the source transaction table to the transaction routing table without having to add a bunch of string processing each time I wanted to join to the table.
Thanks for responses and letting me work things through. I had forgotten how helpful forums can be, even for just bouncing ideas off of other professionals.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply