Changing this SQL to search for Job no in text? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing this SQL to search for Job no in text?

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2012-01-30 : 07:16:34

Hi all

I wrote this SQL that searches Invoice numbers that is different to Job numbers.

We wrote this assuming the Invoice number always begins with the Job number (LEFT) but its not the case as it can be a part of text anywhere inside the invoice code.

This is the original SQL.

SELECT
claim_header.contract_code,
claim_header.claim_code,
'0300' as Status_Code,
'Checked - Suspended: Request Reference does not match Job Number' as Comment
FROM claim_header
WHERE EXISTS ( SELECT 1
FROM claim_job cj
WHERE claim_header.contract_code = cj.contract_code AND
claim_header.claim_code = cj.claim_code AND
cj.order_job_number <> LEFT(cj.claim_code,LEN(cj.order_job_number)))

-------

This SQL works for example for the following scenarios:

Consider the Job numbers to be:
2000105
2000106
2000104

When I run the SQL it works in the following scenarios
Invoice no:
2000105(R) - it works
2000108(R) - it works (message appears telling me what I have in COMMENT field above)
ABC2000104 - it doesn't work as it comes back with "Error converting data type varchar to numeric."

--

How do I change the SQL so that it searches for the Job number inside the Invoice number anywhere inside its text?

I am not sure what function to use or how to format it so any help would be appreciated.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-30 : 07:31:15

try something like this:
SELECT
claim_header.contract_code,
claim_header.claim_code,
'0300' as Status_Code,
'Checked - Suspended: Request Reference does not match Job Number' as Comment
FROM claim_header
WHERE EXISTS ( SELECT 1
FROM claim_job cj
WHERE claim_header.contract_code = cj.contract_code AND
claim_header.claim_code = cj.claim_code AND
CHARINDEX(STR(cj.order_job_number, LEN(cj.order_job_number)), cj.claim_code) < 1

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 11:03:22

change
cj.order_job_number <> LEFT(cj.claim_code,LEN(cj.order_job_number)))

to

PATINDEX('%'+ CAST(cj.order_job_number AS varchar(30)) + '%',cj.claim_code)>0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources