• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 40
  • Last Modified:

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

0
DJPr0
Asked:
DJPr0
  • 3
  • 3
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now