ColdFusion How can I update my existing SQL statement to detect dupes from a remarks field?

The SQL below detects dupes or records with the same serial & lian numbers.

How can I add a remarks field - the serial & lian number would be embedded with other comments:
Remarks field example: 13 HP motor 2 week for delivery 45515-6656 (serial-lian)

Logic: If the serial & lian number match another record in the remarks field add to the dupecount

SELECT * , (select count(*) 
FROM    materials dupe
where  dupe.Serial = t.Serial
and    dupe.lian = t.lian
		   
		) AS DupeCount
from   materials t

Open in new window

DJPr0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What database will you be running this against?

To clarify the requirement:  You need to search a text field to extract a number then look in the same text field in other rows to see if it exists in the serial and lain columns or in other remark fields?

Sample data and expected results would help A LOT.
0
DJPr0Author Commented:
What database will you be running this against?
MS Access
To clarify the requirement:  You need to search a text field to extract a number then look in the same text field in other rows to see if it exists in the serial and lain columns or in other remark fields?
Yes, except for the Remarks field (don't need to search Remarks to other Remarks, just Iian, Serial to remarks).

Record Field 1:
Serial
45515
Iian
6656
Remarks
2 week delivery

Record Field 2:
Serial
22112
Iian
1112
Remarks
45515-6656 Motor 3 phase

Record 1 Lian & Serial match the Lian & Serial in the Remarks field in Record 2 - add 1 to the dupe count
How can I add this feature to my existing SQL statement?

SELECT * , (select count(*) 
FROM    materials dupe
where  dupe.Serial = t.Serial
and    dupe.lian = t.lian
		   
		) AS DupeCount
from   materials t

Open in new window

0
_agx_Commented:
UPDATE:

It's difficult to match an exact "phrase" without regexes, full text searching or a really bulky query. Assuming those two columns are never null, something like this would get you close. Using your example, it translates to either matching both Serial and Lian OR contains the "Serial-Lian" string or '45515-6656'

SELECT * 
       , ( SELECT  count(*) 
           FROM    materials dupe
           WHERE   ( dupe.Serial = t.Serial AND dupe.lian = t.lian  )
           OR      dupe.Remarks LIKE '%'+ t.Serial +'-'+ t.lian +'%' 
		   
	) AS DupeCount
FROM    materials t

Open in new window


Keep in mind it also includes partial matches, like:

Serial: 45515  / Lian: 6656  

... would match ....

13 HP motor 2 week for delivery 45515-6656 something else (exact)
13 HP motor 2 week for delivery 45515-66569999 (starts with)
13 HP motor 2 week for delivery 11145515-6656222 (contains)
13 HP motor 2 week for delivery 999945515-6656 (ends with)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DJPr0Author Commented:
Thanks _agx_,
I'm receiving errors:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
 
It may be that I have null vales in the remarks field (none in the Serial or Lian field).

How can I alter your SQL statement to handle null values in the Remarks field?
0
_agx_Commented:
DJPr0  - Sorry, I never saw your last comment until now.

Usually "too few parameters" means a wrong column name.  Not sure if I tested the query, maybe there's a typo in the column names? Especially the new column "Remarks"...

Edit:  Just tested it. Works in SQL Server, so maybe your query or column names are different than in my example? Or it's Access being picky?
0
DJPr0Author Commented:
Thanks _agx_!
0
_agx_Commented:
You're welcome. Sorry again for the delayed response!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.