Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

TSQL Syntax

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
mpdillon
Asked:
mpdillon
1 Solution
 
dqmqCommented:
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
 
mpdillonAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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