Solved

TSQL Syntax

Posted on 2012-03-14
2
276 Views
Last Modified: 2012-03-14
I need to delete row in table A (Price Code File, OEPRCFIL)when there is not a match in Table B (Item Master file, IMItmIdx). The field in OEPrcFil that contains the item number is Filler_0001. If Filler_0001 ONLY contained the item number, the TSL would be:

(I will first build a Select rather than a Delete and verify the records)

Select Filler_0001 From OEPrcFil
WHere Filler_0001 Not in (Select Item_No from IMItmIdx)

The above works with the provision that Filler_0001 contains only the Item number. The problem I have is that the Filler_0001 contains data in addition to the Item Number. Specifically, Filler_0001 contains the Vendor Number with the Item Number concatonated onto the end, e.g.  VENDORNOITEMNO = ACMEPRODUCTA or GIANTPRODUCTA.

As you can see the Vendor Number that prepends the Item Number is not a fixed length. Since it is not a fixed length, that preludes identifying the Item Number with Substring.

I need some sort of pattern matching. I have looked at CHARINDEX and PATINDEX but I was not successful in using a SELECT inside those functions.

I am using SQL 2000.

What TSQL syntax would I use to select all the OEPrdFil Records that do not have an item number match in the Item Master table?

Thanks,
pat
0
Comment
Question by:mpdillon
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
Comment Utility
This is not 100% logically correct, but perhaps it will steer you in a different direction.

Select Filler_0001 From OEPrcFil o, IMItmIdx m
where m.item_no as varchar(10) =   right(o.filler_0001,len(m.item_no)


What is the datatype of item_no?
do you also have a vendor table that would help construct the vendorproduct key.
0
 

Author Closing Comment

by:mpdillon
Comment Utility
dqmq,

Great. With some modifications plus information I didn't share (didn't want to confuse you), I was able to solve the problem.

Yes, there is a Customer Master table. It was the "From OEPrcFil o, IMItmIdx M" that I was not familiar with.

Here is my solution

Select * from OEPrcFil_SQL
Where Cd_Tp = 1
and Ltrim(Rtrim(Filler_0001))
not in (Select ltrim(Rtrim(C.Cus_No))+ltrim(Rtrim(I.Item_No))
From IMItmIDX_SQL I, ARCusFil_SQL C)

The Delete query looks like:

Delete from OEPrcFil_SQL
Where Cd_Tp = 1
and Ltrim(Rtrim(Filler_0001))
not in (Select ltrim(Rtrim(C.Cus_No))+ltrim(Rtrim(I.Item_No))
From IMItmIDX_SQL I, ARCusFil_SQL C)


The SELECT is very slow (execution time > 1min) but I only have to do this twice so we will just live with it.

Thank you for your assistance.
pat
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now