Ok so I'm gonna attempt to explain my problem as easy as I can. I have
a database of contacts (they are actually vendors). I am writing a
procedure that will query the vendors and generate a tab delimited file
that will be used an accounting program (MYOB). In the vendors database
I am exporting from, I have a field for "First Name", a field for "Last
Name" and a field (looked up by ID) for "Company Name". Now here is the
problem. In the usual case there will be a company name, a first name
and a last name for example:
Company Name: ACME Corporation
First Name: John
Last Name: Smith
HOWEVER, sometimes the vendor is not part of a company and the users of
the database have just been entering in the vendors name as the name of
the company. So it would end up looking like this:
Company Name: John Smith
First Name: John
Last Name: Smith
So the problem is that the accounting program that I am importing these
contacts into uses a weird schema. Instead of separate fields for first
name, last name and company, they have a combined field for "Company
Name/Last Name" and then a separate field for first name. So if the
first name field is blank then we know this contact is a company and
not an individual. As soon as we provide a first name, the record
becomes an individual as opposed to a company.
SO the MYOB import procedure is expecting two fields. One called
"[Co./Last Name]" and one called "[First Name]". So when I query my
current vendor database, I need to figure out if the company name
contains both the first and last name of the vendor and if so then
return only the vendors "last name" for the "[Co./Last Name]" field.
However if the company name is completely different then the first and
last name of the vendor, then I need to return the company name for
the "[Co./Last Name]" field and make the "[First Name]" field blank.
Here is what I thought would work but it doesn't:
SELECT
CASE
WHEN cu.vchrFirstNam e LIKE '%' + co.vchrCompanyN ame
+ '%'
AND cu.vchrLastName LIKE '%' + co.vchrCompanyN ame
+ '%'
THEN cu.vchrLastName
ELSE co.vchrCompanyN ame
END AS [Co./Last Name],
CASE
WHEN cu.vchrFirstNam e LIKE '%' + co.vchrCompanyN ame
+ '%'
AND cu.vchrLastName LIKE '%' + co.vchrCompanyN ame
+ '%'
THEN cu.vchrFirstNam e
ELSE ''
END AS [First Name],
I don't get an error when I use this code, but it just always fails and
ends up using the company name no matter what even though I know for
sure that the first and last names ARE in the company name.
Sorry for my long winded explanation.
THANKS
a database of contacts (they are actually vendors). I am writing a
procedure that will query the vendors and generate a tab delimited file
that will be used an accounting program (MYOB). In the vendors database
I am exporting from, I have a field for "First Name", a field for "Last
Name" and a field (looked up by ID) for "Company Name". Now here is the
problem. In the usual case there will be a company name, a first name
and a last name for example:
Company Name: ACME Corporation
First Name: John
Last Name: Smith
HOWEVER, sometimes the vendor is not part of a company and the users of
the database have just been entering in the vendors name as the name of
the company. So it would end up looking like this:
Company Name: John Smith
First Name: John
Last Name: Smith
So the problem is that the accounting program that I am importing these
contacts into uses a weird schema. Instead of separate fields for first
name, last name and company, they have a combined field for "Company
Name/Last Name" and then a separate field for first name. So if the
first name field is blank then we know this contact is a company and
not an individual. As soon as we provide a first name, the record
becomes an individual as opposed to a company.
SO the MYOB import procedure is expecting two fields. One called
"[Co./Last Name]" and one called "[First Name]". So when I query my
current vendor database, I need to figure out if the company name
contains both the first and last name of the vendor and if so then
return only the vendors "last name" for the "[Co./Last Name]" field.
However if the company name is completely different then the first and
last name of the vendor, then I need to return the company name for
the "[Co./Last Name]" field and make the "[First Name]" field blank.
Here is what I thought would work but it doesn't:
SELECT
CASE
WHEN cu.vchrFirstNam e LIKE '%' + co.vchrCompanyN ame
+ '%'
AND cu.vchrLastName LIKE '%' + co.vchrCompanyN ame
+ '%'
THEN cu.vchrLastName
ELSE co.vchrCompanyN ame
END AS [Co./Last Name],
CASE
WHEN cu.vchrFirstNam e LIKE '%' + co.vchrCompanyN ame
+ '%'
AND cu.vchrLastName LIKE '%' + co.vchrCompanyN ame
+ '%'
THEN cu.vchrFirstNam e
ELSE ''
END AS [First Name],
I don't get an error when I use this code, but it just always fails and
ends up using the company name no matter what even though I know for
sure that the first and last names ARE in the company name.
Sorry for my long winded explanation.
THANKS
Comment