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 <tedro...@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 <p...@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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/cajezx3m0ei-vhplfq0mfbw9h6zv-c8mmmmvcdtwv5phgzho...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
