Wild Card/Generic retrieval

Wed, 10 Sep 2003 13:38:56 -0400

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C377C2.68BAA8D0
Content-Type: text/plain;
	charset="iso-8859-1"

Anyone remember the show "Name That Tune"? As in, I can name that tune in 3
notes... Well, this is beginning to look like that. I can perform that task
with 3 functions...

I'm not sure how efficient the REVERSE function is, but LOWER is an SQL
function. How about DOWNSHIFT or UPSHIFT? As well, if you're using REVERSE,
shouldn't you look for "ht"?

Bob

-----Original Message-----
From: Edis, Robert [mailto:REdis@blistex.com]
Sent: Wednesday, September 10, 2003 1:29 PM
To: Powerh-L (E-mail)
Subject: RE: Wild Card/Generic retrieval

What about SELECT IF 'th' = LOWER(REVERSE(TRUNCATE(partno)))[1:2]?

-----Original Message-----
From: Deskin, Bob [mailto:Bob.Deskin@cognos.com]
Sent: Wednesday, September 10, 2003 12:08 PM
To: 'John Hunter'; powerh-l@lists.swau.edu
Subject: RE: Wild Card/Generic retrieval

Actually, since the original requirement was to find "th" in the last two
character positions, excluding blanks, a substring would be a direct test as
follows:

SELECT IF "th" = partno[(SIZE(TRUNCATE(partno))-1:2]

Bob

-----Original Message-----
From: John Hunter [mailto:jthunter@nbnet.nb.ca]
Sent: Wednesday, September 10, 2003 12:15 PM
To: powerh-l@lists.swau.edu
Subject: RE: Wild Card/Generic retrieval

Not to be too picky with responses...

but what if the value of partno was '4th6th' ? ie. a value with 2 strings of
th?

then perhaps 

> select if 'th' = reverse(reverse(truncate(partno))[1:2])

But I'm not sure if reverse would be more efficient than matchpattern.

-John

-----Original Message----- 

 If 0 = index(partno,"th") - size(truncate(part-no)) - 1 

Join us at Cognos' biggest event of the year Enterprise 2003, The Cognos
Business Forum. Taking place in over 25 cities around the world, it's an
opportunity for Business and IT leaders to learn about strategies for
driving performance. Visit http://www.cognos.com/enterprise03 for more
details. 

This message may contain privileged and/or confidential information. If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. Thank you.

Join us at Cognos' biggest event of the year Enterprise 2003, The Cognos
Business Forum.  Taking place in over 25 cities around the world, it's an
opportunity for Business and IT leaders to learn about strategies for
driving performance. Visit http://www.cognos.com/enterprise03 for more
details. 

This message may contain privileged and/or confidential information.  If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so.  Thank you.

------_=_NextPart_001_01C377C2.68BAA8D0
Content-Type: text/html;
	charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Wild Card/Generic retrieval</TITLE>

<META content="MSHTML 5.50.4926.2500" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=121383517-10092003><FONT face=Arial color=#0000ff size=2>Anyone 
remember the show "Name That Tune"? As in, I can name that tune in 3 notes... 
Well, this is beginning to look like that. I can perform that task with 3 
functions...</FONT></SPAN></DIV>
<DIV><SPAN class=121383517-10092003><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=121383517-10092003><FONT face=Arial color=#0000ff size=2>I'm 
not sure how efficient the REVERSE function is, but LOWER is an SQL function. 
How about DOWNSHIFT or UPSHIFT? As well, if you're using REVERSE, shouldn't you 
look for "ht"?</FONT></SPAN></DIV>
<DIV><SPAN class=121383517-10092003><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=121383517-10092003><FONT face=Arial color=#0000ff 
size=2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
  <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma 
  size=2>-----Original Message-----<BR><B>From:</B> Edis, Robert 
  [mailto:REdis@blistex.com]<BR><B>Sent:</B> Wednesday, September 10, 2003 1:29 
  PM<BR><B>To:</B> Powerh-L (E-mail)<BR><B>Subject:</B> RE: Wild Card/Generic 
  retrieval<BR><BR></FONT></DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=661052617-10092003>What 
  about SELECT IF 'th' = 
  LOWER(REVERSE(TRUNCATE(partno)))[1:2]?</SPAN></FONT></DIV>
  <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
    <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma 
    size=2>-----Original Message-----<BR><B>From:</B> Deskin, Bob 
    [mailto:Bob.Deskin@cognos.com]<BR><B>Sent:</B> Wednesday, September 10, 2003 
    12:08 PM<BR><B>To:</B> 'John Hunter'; 
    powerh-l@lists.swau.edu<BR><B>Subject:</B> RE: Wild Card/Generic 
    retrieval<BR><BR></FONT></DIV>
    <DIV><SPAN class=549270217-10092003><FONT face=Arial color=#0000ff 
    size=2>Actually, since the original requirement was to find "th" in the last 
    two character positions, excluding blanks, a substring would be a direct 
    test as follows:</FONT></SPAN></DIV>
    <DIV><SPAN class=549270217-10092003><FONT face=Arial color=#0000ff 
    size=2></FONT></SPAN>&nbsp;</DIV>
    <DIV><SPAN class=549270217-10092003><FONT face=Arial color=#0000ff 
    size=2>SELECT IF "th" = 
    partno[(SIZE(TRUNCATE(partno))-1:2]</FONT></SPAN></DIV>
    <DIV><SPAN class=549270217-10092003><FONT face=Arial color=#0000ff 
    size=2></FONT></SPAN>&nbsp;</DIV>
    <DIV><SPAN class=549270217-10092003><FONT face=Arial color=#0000ff 
    size=2>Bob</FONT></SPAN></DIV>
    <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
      <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma 
      size=2>-----Original Message-----<BR><B>From:</B> John Hunter 
      [mailto:jthunter@nbnet.nb.ca]<BR><B>Sent:</B> Wednesday, September 10, 
      2003 12:15 PM<BR><B>To:</B> powerh-l@lists.swau.edu<BR><B>Subject:</B> RE: 
      Wild Card/Generic retrieval<BR><BR></FONT></DIV>
      <DIV><SPAN class=240445515-10092003>Not to be too picky with 
      responses...</SPAN></DIV>
      <DIV><SPAN class=240445515-10092003></SPAN>&nbsp;</DIV>
      <DIV><SPAN class=240445515-10092003>but what if the value of partno was 
      '4th6th' ? ie. a value with 2 strings of th?</SPAN></DIV>
      <DIV><SPAN class=240445515-10092003></SPAN>&nbsp;</DIV>
      <DIV><SPAN class=240445515-10092003>then perhaps </SPAN></DIV>
      <DIV><SPAN class=240445515-10092003></SPAN>&nbsp;</DIV>
      <DIV><SPAN class=240445515-10092003>&gt; select if 'th' = 
      reverse(reverse(truncate(partno))[1:2])</SPAN></DIV>
      <DIV><SPAN class=240445515-10092003></SPAN>&nbsp;</DIV>
      <DIV><SPAN class=240445515-10092003>But I'm not sure if reverse would be 
      more efficient than matchpattern.</SPAN></DIV>
      <DIV><SPAN class=240445515-10092003><FONT face=Arial color=#0000ff 
      size=2></FONT></SPAN>&nbsp;</DIV>
      <DIV><SPAN class=240445515-10092003>-John</SPAN></DIV>
      <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
        <DIV class=OutlookMessageHeader dir=ltr align=left><FONT size=2><FONT 
        face=Tahoma>-----Original Message-----<SPAN 
        class=240445515-10092003><FONT face=Arial 
        color=#0000ff>&nbsp;</FONT></SPAN></FONT></FONT></DIV>
        <DIV class=OutlookMessageHeader dir=ltr align=left><FONT size=2><FONT 
        face=Tahoma><SPAN 
        class=240445515-10092003></SPAN></FONT></FONT>&nbsp;</DIV>
        <DIV class=OutlookMessageHeader dir=ltr align=left><FONT size=2><FONT 
        face=Tahoma><SPAN class=240445515-10092003>&nbsp;</SPAN></FONT>If 0 = 
        index(partno,"th") - size(truncate(part-no)) - 1</FONT> 
    </DIV></BLOCKQUOTE></BLOCKQUOTE><BR>
    <P><FONT face=Arial size=2>Join us at Cognos' biggest event of the year<I> 
    Enterprise 2003, The Cognos Business Forum</I>. Taking place in over 25 
    cities around the world, it's an opportunity for Business and IT leaders to 
    learn about strategies for driving performance. Visit<U> 
    http://www.cognos.com/enterprise03</U> for more details. </FONT></P><BR>
    <P><FONT face=Arial size=2>This message may contain privileged and/or 
    confidential information. If you have received this e-mail in error or are 
    not the intended recipient, you may not use, copy, disseminate or distribute 
    it; do not open any attachments, delete it immediately from your system and 
    notify the sender promptly by e-mail that you have done so. Thank 
    you.</FONT></P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
<BR>

Join us at Cognos' biggest event of the year Enterprise 2003, The Cognos Business Forum. Taking place in over 25 cities around the world, it's an opportunity for Business and IT leaders to learn about strategies for driving performance. Visit http://www.cognos.com/enterprise03 for more details.

This message may contain privileged and/or confidential information. If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you.

------_=_NextPart_001_01C377C2.68BAA8D0--