RE: SQL Query Scalar Function Versus Second Table

Pillai,

If it helps, I don't know of any way to build an index on replace(part,'-',''). 
 Unless that value is stored in another file.

I'd recommend a trigger on the original file that maintains the new 
'dictionary' file.

That's how I like to do these things.

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of 
> Narayanan R Pillai
> Sent: Friday, August 19, 2005 2:16 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL Query Scalar Function Versus Second Table
> 
> 
> Wilt,
> 
> That is correct, There is only one part ( A-123-300 ). The 
> users enter 
> that as A-123300 or A123300 and we need to be able to match it to 
> A-123-300.
> 
> My concern really is that building an index on replace( part, 
> '-', '' ) 
> might impact performance, and not building it might impact the query. 
> D**d if I do, D**d if I don't :/
> 
> Pillai
> Wilt, Charles wrote:
> 
> >No,
> >
> >He said there are _NOT_ any parts that different only by 
> where/how many dashes.
> >
> >The user will sometimes enter part A-123-300 as A-123300 or 
> even A123300.
> >
> >But there's only one part.
> >
> >Charles Wilt
> >--
> >iSeries Systems Administrator / Developer
> >Mitsubishi Electric Automotive America
> >ph: 513-573-4343
> >fax: 513-398-1121
> > 
> >
> >  
> >
> >>-----Original Message-----
> >>From: midrange-l-bounces@xxxxxxxxxxxx
> >>[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
> >>Sent: Friday, August 19, 2005 1:42 PM
> >>To: Midrange Systems Technical Discussion
> >>Subject: Re: SQL Query Scalar Function Versus Second Table
> >>
> >>
> >>Let me get this right.
> >>
> >>You have two items in your item master.  One is A-123300 and one is 
> >>A-123-300.
> >>Now the user enters a transaction for A-12-33-00.  It should 
> >>strip the 
> >>dashes off of A-12-33-00 and post it to the first item in the 
> >>item master 
> >>that (when stripped of it's dashes) matches?  So we don't 
> >>care about the 
> >>balance of A-123-300?  We can always sum up the balance for 
> >>A-123300 and 
> >>A-123-300 to get the total balance?
> >>
> >>Rob Berendt
> >>    
> >>
> >
> >  
> >
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>