Ok, I need a little help and here's the scenario:
I have an x-ref table that looks like this:
DeptNum, Description, Value
1??, Dept 1, 99930
2??, Dept 2, 92382
3??, Dept 3, 83728
?4?, Dept 4, 27377
Next, I have a data table with thousands of employees with department numbers I want to scan through. If the employee's dept # is 101, then in the fastest way possible, I want to compare 101 to the DeptNum in the x-ref table and return the value 99930. If the employee's dept # is 141, then the search should return both 99930 and 27377 and I'll perform some logic to determine which value is most important.
I know I can do SELECT queries using the LIKE statement such as Select * from x-ref where x-ref.DeptNum LIKE "1%", but in this case, I know the explicit value and I need to compare it to the masked values in the x-ref table. So I really need to the reverse which would be Select * from x-ref where "1%" LIKE x-ref.DeptNum (which doesn' t work, I know.). FYI, The DeptNum field can be up to 20 characters long and I am guessing the masked values could be as well.
Unfortunately, I think I'm stuck mentally on doing this with a SQL Query and I'm not able to think outside of this box right now, so any suggestions will help. Hell, at this point, I'll take snarky comments, but only if they contain clues to how I need to proceed. LOL
Thanks in advance
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Requisite snarky comment: if a join depends on only _part_ of the value of another field, the data is not normalized.
Are the DeptNum fields really literally question marks and digits, as if they were wildcards?
If so, you could write some UDF to convert DOS wildcards of * and? to SQL wildcards of % and _
It's a bit unholy but it ought to get the job done:
SELECT Employee.*, Dept.* from Employee JOIN Dept ON Employee.Dept LIKE MyUDF(Dept.DeptNum)
On Tue, Jul 2, 2019 at 12:57 PM Paul H. Tarver paul@tpcqpc.com wrote:
Ok, I need a little help and here's the scenario:
I have an x-ref table that looks like this:
DeptNum, Description, Value
1??, Dept 1, 99930
2??, Dept 2, 92382
3??, Dept 3, 83728
?4?, Dept 4, 27377
Next, I have a data table with thousands of employees with department numbers I want to scan through. If the employee's dept # is 101, then in the fastest way possible, I want to compare 101 to the DeptNum in the x-ref table and return the value 99930. If the employee's dept # is 141, then the search should return both 99930 and 27377 and I'll perform some logic to determine which value is most important.
I know I can do SELECT queries using the LIKE statement such as Select * from x-ref where x-ref.DeptNum LIKE "1%", but in this case, I know the explicit value and I need to compare it to the masked values in the x-ref table. So I really need to the reverse which would be Select * from x-ref where "1%" LIKE x-ref.DeptNum (which doesn' t work, I know.). FYI, The DeptNum field can be up to 20 characters long and I am guessing the masked values could be as well.
Unfortunately, I think I'm stuck mentally on doing this with a SQL Query and I'm not able to think outside of this box right now, so any suggestions will help. Hell, at this point, I'll take snarky comments, but only if they contain clues to how I need to proceed. LOL
Thanks in advance
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
There's no need for a UDF, VFP has a built-in function LIKE().
Create Cursor depts ( deptnum c(3), Descript c(10), Value i) Insert Into depts ( deptnum, Descript, Value) Values ( "1??", "Dept 1", 99930) Insert Into depts ( deptnum, Descript, Value) Values ( "2??", "Dept 2", 92382) Insert Into depts ( deptnum, Descript, Value) Values ( "3??", "Dept 3", 83728) Insert Into depts ( deptnum, Descript, Value) Values ( "?4?", "Dept 4", 27377)
Create Cursor employees ( empref c(10), empname c(20), deptnum c(3)) Insert Into employees (empref, empname, deptnum) Values ("001", "Jack", "141")
Select employees.*, depts.Value From employees Left Join depts On Like(depts.deptnum, employees.deptnum)
Ricardo
On Tue, 2 Jul 2019 at 18:29, Ted Roche tedroche@gmail.com wrote:
Requisite snarky comment: if a join depends on only _part_ of the value of another field, the data is not normalized.
Are the DeptNum fields really literally question marks and digits, as if they were wildcards?
If so, you could write some UDF to convert DOS wildcards of * and? to SQL wildcards of % and _
It's a bit unholy but it ought to get the job done:
SELECT Employee.*, Dept.* from Employee JOIN Dept ON Employee.Dept LIKE MyUDF(Dept.DeptNum)
On Tue, Jul 2, 2019 at 12:57 PM Paul H. Tarver paul@tpcqpc.com wrote:
Ok, I need a little help and here's the scenario:
I have an x-ref table that looks like this:
DeptNum, Description, Value
1??, Dept 1, 99930
2??, Dept 2, 92382
3??, Dept 3, 83728
?4?, Dept 4, 27377
Next, I have a data table with thousands of employees with department numbers I want to scan through. If the employee's dept # is 101, then in the fastest way possible, I want to compare 101 to the DeptNum in the x-ref table and return the value 99930. If the employee's dept # is 141, then
the
search should return both 99930 and 27377 and I'll perform some logic to determine which value is most important.
I know I can do SELECT queries using the LIKE statement such as Select * from x-ref where x-ref.DeptNum LIKE "1%", but in this case, I know the explicit value and I need to compare it to the masked values in the x-ref table. So I really need to the reverse which would be Select * from x-ref where "1%" LIKE x-ref.DeptNum (which doesn' t work, I know.). FYI, The DeptNum field can be up to 20 characters long and I am guessing the
masked
values could be as well.
Unfortunately, I think I'm stuck mentally on doing this with a SQL Query and I'm not able to think outside of this box right now, so any suggestions will help. Hell, at this point, I'll take snarky comments, but only if they contain clues to how I need to proceed. LOL
Thanks in advance
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I've made the comment to this group before: Almost day I learn something new that Foxpro can do that changes the way I work. I use this tool everyday and I kid you not, the joke in my office is when I yell out, "I learned my new Foxpro trick for the day!"
Thank you Ricardo! Your tip put me exactly where I wanted to be and with a few modifications, I have a SQL Select statement that not only solves my problem, but it is screaming fast and will work going forward.
My final query looks like this:
SELECT dz.* ; FROM Xref_deptzips dz ; WHERE LIKE(dz.dept, NVL(tmpEmpWork.deptcode,SPACE(20))) ; INTO CURSOR tmpWork
The NVL is necessary because I'm scanning through a sub-query and the space(20) simply ensures that the NVL failure value is exactly the same length as the dz.dept field. Once I got all the fields to the same length it started working perfectly.
Thanks so much for giving me the clue I needed to get out of the corner I was in! I appreciate it!
Love this group!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ricardo Molina Sent: Tuesday, July 02, 2019 5:12 PM To: profoxtech@leafe.com Subject: Re: Reverse Wildcard String Comparison
There's no need for a UDF, VFP has a built-in function LIKE().
Create Cursor depts ( deptnum c(3), Descript c(10), Value i) Insert Into depts ( deptnum, Descript, Value) Values ( "1??", "Dept 1", 99930) Insert Into depts ( deptnum, Descript, Value) Values ( "2??", "Dept 2", 92382) Insert Into depts ( deptnum, Descript, Value) Values ( "3??", "Dept 3", 83728) Insert Into depts ( deptnum, Descript, Value) Values ( "?4?", "Dept 4", 27377)
Create Cursor employees ( empref c(10), empname c(20), deptnum c(3)) Insert Into employees (empref, empname, deptnum) Values ("001", "Jack", "141")
Select employees.*, depts.Value From employees Left Join depts On Like(depts.deptnum, employees.deptnum)
Ricardo
On Tue, 2 Jul 2019 at 18:29, Ted Roche tedroche@gmail.com wrote:
Requisite snarky comment: if a join depends on only _part_ of the value of another field, the data is not normalized.
Are the DeptNum fields really literally question marks and digits, as if they were wildcards?
If so, you could write some UDF to convert DOS wildcards of * and? to SQL wildcards of % and _
It's a bit unholy but it ought to get the job done:
SELECT Employee.*, Dept.* from Employee JOIN Dept ON Employee.Dept LIKE MyUDF(Dept.DeptNum)
On Tue, Jul 2, 2019 at 12:57 PM Paul H. Tarver paul@tpcqpc.com wrote:
Ok, I need a little help and here's the scenario:
I have an x-ref table that looks like this:
DeptNum, Description, Value
1??, Dept 1, 99930
2??, Dept 2, 92382
3??, Dept 3, 83728
?4?, Dept 4, 27377
Next, I have a data table with thousands of employees with department numbers I want to scan through. If the employee's dept # is 101, then in the fastest way possible, I want to compare 101 to the DeptNum in the x-ref table and return the value 99930. If the employee's dept # is 141, then
the
search should return both 99930 and 27377 and I'll perform some logic to determine which value is most important.
I know I can do SELECT queries using the LIKE statement such as Select * from x-ref where x-ref.DeptNum LIKE "1%", but in this case, I know the explicit value and I need to compare it to the masked values in the
x-ref
table. So I really need to the reverse which would be Select * from
x-ref
where "1%" LIKE x-ref.DeptNum (which doesn' t work, I know.). FYI, The DeptNum field can be up to 20 characters long and I am guessing the
masked
values could be as well.
Unfortunately, I think I'm stuck mentally on doing this with a SQL Query and I'm not able to think outside of this box right now, so any suggestions will help. Hell, at this point, I'll take snarky comments, but only if they contain clues to how I need to proceed. LOL
Thanks in advance
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
No snark implied or taken!
The reason for this isn't about the source data as much about the destination data. We are pulling the data for use in another system and we are trying to populate a new field with content based upon similar source data. It will be normalized in the destination system. Problem is that we'll be pushing data to the new system on an on-going basis. Therefore, I'm trying to build a process that will work in all of the situations that might come up. I also was intrigued by the prospect of playing around with some "fuzzy logic."
This process does not have to use a Select statement, I just thought that might be the fastest way rather than scanning through the x-ref table on every employee record. I did something similar with another client where they could enter a range of values into a field and then we expanded the range to an array to include every value in the range and did an ascan to test, but I was thinking there might be a better way.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: Tuesday, July 02, 2019 12:30 PM To: profoxtech@leafe.com Cc: profoxtech@leafe.com Subject: Re: Reverse Wildcard String Comparison
Requisite snarky comment: if a join depends on only _part_ of the value of another field, the data is not normalized.
Are the DeptNum fields really literally question marks and digits, as if they were wildcards?
If so, you could write some UDF to convert DOS wildcards of * and? to SQL wildcards of % and _
It's a bit unholy but it ought to get the job done:
SELECT Employee.*, Dept.* from Employee JOIN Dept ON Employee.Dept LIKE MyUDF(Dept.DeptNum)
On Tue, Jul 2, 2019 at 12:57 PM Paul H. Tarver paul@tpcqpc.com wrote:
Ok, I need a little help and here's the scenario:
I have an x-ref table that looks like this:
DeptNum, Description, Value
1??, Dept 1, 99930
2??, Dept 2, 92382
3??, Dept 3, 83728
?4?, Dept 4, 27377
Next, I have a data table with thousands of employees with department numbers I want to scan through. If the employee's dept # is 101, then in the fastest way possible, I want to compare 101 to the DeptNum in the x-ref table and return the value 99930. If the employee's dept # is 141, then
the
search should return both 99930 and 27377 and I'll perform some logic to determine which value is most important.
I know I can do SELECT queries using the LIKE statement such as Select * from x-ref where x-ref.DeptNum LIKE "1%", but in this case, I know the explicit value and I need to compare it to the masked values in the x-ref table. So I really need to the reverse which would be Select * from x-ref where "1%" LIKE x-ref.DeptNum (which doesn' t work, I know.). FYI, The DeptNum field can be up to 20 characters long and I am guessing the masked values could be as well.
Unfortunately, I think I'm stuck mentally on doing this with a SQL Query and I'm not able to think outside of this box right now, so any suggestions will help. Hell, at this point, I'll take snarky comments, but only if they contain clues to how I need to proceed. LOL
Thanks in advance
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
-
Paul H. Tarver -
Ricardo Molina -
Ted Roche