Wild Card/Generic retrieval
Edis, Robert
REdis@Blistex.com
Wed, 10 Sep 2003 12:42:20 -0500
This is a multi-part message in MIME format.
------_=_NextPart_001_01C377C2.E2C265DB
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Sorry, I've been working with SQL too much lately. Yep, it should be =
'ht'.
=20
Forgive me, I've in the thick of Impromptu report writing at the moment. =
:(
=20
Blue
-----Original Message-----
From: Deskin, Bob [mailto:Bob.Deskin@Cognos.COM]
Sent: Wednesday, September 10, 2003 12:39 PM
To: Edis, Robert; Powerh-L (E-mail)
Subject: RE: Wild Card/Generic retrieval
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...
=20
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"?
=20
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' =3D 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:
=20
SELECT IF "th" =3D partno[(SIZE(TRUNCATE(partno))-1:2]
=20
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...
=20
but what if the value of partno was '4th6th' ? ie. a value with 2 =
strings of th?
=20
then perhaps=20
=20
> select if 'th' =3D reverse(reverse(truncate(partno))[1:2])
=20
But I'm not sure if reverse would be more efficient than matchpattern.
=20
-John
-----Original Message-----=20
=20
If 0 =3D index(partno,"th") - size(truncate(part-no)) - 1=20
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.=20
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.=20
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.E2C265DB
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>RE: Wild Card/Generic retrieval</TITLE>
<META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN =
class=3D377494017-10092003>Sorry,=20
I've been working with SQL too much lately. Yep, it should be=20
'ht'.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D377494017-10092003></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D377494017-10092003>Forgive me, I've in the thick of Impromptu =
report=20
writing at the moment. :(</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D377494017-10092003></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D377494017-10092003>Blue</SPAN></FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Deskin, Bob=20
[mailto:Bob.Deskin@Cognos.COM]<BR><B>Sent:</B> Wednesday, September =
10, 2003=20
12:39 PM<BR><B>To:</B> Edis, Robert; Powerh-L =
(E-mail)<BR><B>Subject:</B> RE:=20
Wild Card/Generic retrieval<BR><BR></FONT></DIV>
<DIV><SPAN class=3D121383517-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Anyone remember the show "Name That Tune"? As in, I can name =
that tune=20
in 3 notes... Well, this is beginning to look like that. I can perform =
that=20
task with 3 functions...</FONT></SPAN></DIV>
<DIV><SPAN class=3D121383517-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D121383517-10092003><FONT face=3DArial =
color=3D#0000ff size=3D2>I'm=20
not sure how efficient the REVERSE function is, but LOWER is an SQL =
function.=20
How about DOWNSHIFT or UPSHIFT? As well, if you're using REVERSE, =
shouldn't=20
you look for "ht"?</FONT></SPAN></DIV>
<DIV><SPAN class=3D121383517-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D121383517-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Edis, Robert=20
[mailto:REdis@blistex.com]<BR><B>Sent:</B> Wednesday, September 10, =
2003=20
1:29 PM<BR><B>To:</B> Powerh-L (E-mail)<BR><B>Subject:</B> RE: Wild=20
Card/Generic retrieval<BR><BR></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D661052617-10092003>What about SELECT IF 'th' =3D=20
LOWER(REVERSE(TRUNCATE(partno)))[1:2]?</SPAN></FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Deskin, Bob=20
[mailto:Bob.Deskin@cognos.com]<BR><B>Sent:</B> Wednesday, =
September 10,=20
2003 12:08 PM<BR><B>To:</B> 'John Hunter';=20
powerh-l@lists.swau.edu<BR><B>Subject:</B> RE: Wild Card/Generic=20
retrieval<BR><BR></FONT></DIV>
<DIV><SPAN class=3D549270217-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Actually, since the original requirement was to find "th" =
in the=20
last two character positions, excluding blanks, a substring would =
be a=20
direct test as follows:</FONT></SPAN></DIV>
<DIV><SPAN class=3D549270217-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D549270217-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>SELECT IF "th" =3D=20
partno[(SIZE(TRUNCATE(partno))-1:2]</FONT></SPAN></DIV>
<DIV><SPAN class=3D549270217-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D549270217-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> John Hunter=20
[mailto:jthunter@nbnet.nb.ca]<BR><B>Sent:</B> Wednesday, =
September 10,=20
2003 12:15 PM<BR><B>To:</B> =
powerh-l@lists.swau.edu<BR><B>Subject:</B>=20
RE: Wild Card/Generic retrieval<BR><BR></FONT></DIV>
<DIV><SPAN class=3D240445515-10092003>Not to be too picky with=20
responses...</SPAN></DIV>
<DIV><SPAN class=3D240445515-10092003></SPAN> </DIV>
<DIV><SPAN class=3D240445515-10092003>but what if the value of =
partno was=20
'4th6th' ? ie. a value with 2 strings of th?</SPAN></DIV>
<DIV><SPAN class=3D240445515-10092003></SPAN> </DIV>
<DIV><SPAN class=3D240445515-10092003>then perhaps </SPAN></DIV>
<DIV><SPAN class=3D240445515-10092003></SPAN> </DIV>
<DIV><SPAN class=3D240445515-10092003>> select if 'th' =3D=20
reverse(reverse(truncate(partno))[1:2])</SPAN></DIV>
<DIV><SPAN class=3D240445515-10092003></SPAN> </DIV>
<DIV><SPAN class=3D240445515-10092003>But I'm not sure if =
reverse would be=20
more efficient than matchpattern.</SPAN></DIV>
<DIV><SPAN class=3D240445515-10092003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D240445515-10092003>-John</SPAN></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
size=3D2><FONT=20
face=3DTahoma>-----Original Message-----<SPAN=20
class=3D240445515-10092003><FONT face=3DArial=20
color=3D#0000ff> </FONT></SPAN></FONT></FONT></DIV>
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
size=3D2><FONT=20
face=3DTahoma><SPAN=20
class=3D240445515-10092003></SPAN></FONT></FONT> </DIV>
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
size=3D2><FONT=20
face=3DTahoma><SPAN =
class=3D240445515-10092003> </SPAN></FONT>If 0 =3D=20
index(partno,"th") - size(truncate(part-no)) - 1</FONT>=20
</DIV></BLOCKQUOTE></BLOCKQUOTE><BR>
<P><FONT face=3DArial size=3D2>Join us at Cognos' biggest event of =
the year<I>=20
Enterprise 2003, The Cognos Business Forum</I>. Taking place in =
over 25=20
cities around the world, it's an opportunity for Business and IT =
leaders=20
to learn about strategies for driving performance. Visit<U>=20
http://www.cognos.com/enterprise03</U> for more details. =
</FONT></P><BR>
<P><FONT face=3DArial size=3D2>This message may contain privileged =
and/or=20
confidential information. If you have received this e-mail in =
error or are=20
not the intended recipient, you may not use, copy, disseminate or=20
distribute it; do not open any attachments, delete it immediately =
from=20
your system and notify the sender promptly by e-mail that you have =
done=20
so. Thank you.</FONT></P></BLOCKQUOTE></BLOCKQUOTE><BR>
<P><FONT face=3DArial size=3D2>Join us at Cognos' biggest event of the =
year<I>=20
Enterprise 2003, The Cognos Business Forum</I>. Taking place in over =
25 cities=20
around the world, it's an opportunity for Business and IT leaders to =
learn=20
about strategies for driving performance. Visit<U>=20
http://www.cognos.com/enterprise03</U> for more details. =
</FONT></P><BR>
<P><FONT face=3DArial size=3D2>This message may contain privileged =
and/or=20
confidential information. If you have received this e-mail in error or =
are not=20
the intended recipient, you may not use, copy, disseminate or =
distribute it;=20
do not open any attachments, delete it immediately from your system =
and notify=20
the sender promptly by e-mail that you have done so. Thank=20
you.</FONT></P></BLOCKQUOTE></BODY></HTML>
------_=_NextPart_001_01C377C2.E2C265DB--