Replacement for my LIKE Clause

Dear all,
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID = (SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
>Dear all,
>I need some help from all my Transact SQL Guru friends out there..
>Here is the scenario in its most simplified form.. ..
>I have two tables.. A(Lookup table) and B(Transaction Table)
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>TableB Fields
> EmployeeID
> EmployeeName......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>Therefore, this is what I had wrote initially..
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>Therefore this is the expected match for my examples based on
>requirement..
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>Looking forward to hearing from someone ASAP.. Please help..
>Best regards,
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>> A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN '
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>> I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> >> A(Lookup table) and B(Transaction Table) <<
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN '
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
> >> I hope you get where I am leading this to, from my examples.. <<
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.