<HTML xmlns:eXclaimer="http://www.exclaimer.co.uk">
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-16">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-16">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>varchar vs char</TITLE>
<META content="MSHTML 5.50.4943.400" name=GENERATOR></HEAD><BODY ><DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>A
space is not a null and vice versa. In many applications, having a space is
meaningful. We don't assume that if you specify trailing spaces you don't really
want them. I'm at home as I write this, so can't test what you get if you
say</FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>DEFINE
x varchar*10 = "abc"</FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>but I
would hope that you get a length of 3 with no trailing spaces without having to
truncate. However, if you specify trailing spaces, that's what you get. And if
you use an expression that might give you trailing spaces, and you don't want
them, then you'll have to use the TRUNCATE function.</FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>I
gather that these columns were not varchar before. Simply switching to
varchar doesn't change the data content not the code. However, some things
are automatic. When us you item association as in </FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>item_a
= item_b</FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>in a
let or an ITEM statement, and both are varchar, you should get the appropriate
length.</FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff size=2>So
when you change datatypes from char to varchar, edit more than the datatype. And
when you convert your data, use QTP to change from having spaces to not using
TRUNCATE. That should be a one-time conversion.</FONT></SPAN></DIV>
<DIV><SPAN class=300130811-28092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=300130811-28092005><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>
powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com]<B>On Behalf
Of </B>fernando.olmos@hpa.com.au<BR><B>Sent:</B> September 28, 2005 2:44
AM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> varchar vs
char<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>Can someone please explain why this is happening
and is it correct?</FONT> </P>
<P><FONT face=Arial size=2>Why is it that in PH (and this does not happen in
Oracle), a column with a varchar will retain any trailing spaces when data
with spaces after the last char is read into it?</FONT></P>
<P><FONT face=Arial size=2>ie:
</FONT><BR> <FONT face="Courier New"
size=2>define a varchar * 10 = "abc
" ;has 3 spaces after the 'c'
letter</FONT> <BR> <FONT
face="Courier New" size=2>define b char*20 = "|" + a + "|"</FONT>
<BR> <FONT face="Courier New"
size=2>report a b</FONT> <BR> <FONT
face="Courier New" size=2>go</FONT> </P>
<P><FONT face=Arial size=2>This shows ...</FONT> </P>
<P><FONT face="Courier New" size=2>
A B</FONT> </P>
<P><FONT face="Courier New" size=2>
abc |abc |</FONT>
</P>
<P><FONT face=Arial size=2>Of course if I change the B define to trunc() the A
define it works ...</FONT> </P>
<P> <FONT face="Courier New"
size=2>define b char*20 = "|" + trunc(a) + "|"</FONT> </P>
<P><FONT face=Arial size=2>It won't make any difference if I change the
char*20 to varchar*20 either.</FONT> </P>
<P><FONT face=Arial size=2>I don't want to have to use trunc(). We have tons
of columns we're migrating over to a new PH environment that are all varchars.
I don't want to have to specify trunc() on ever item that populates those
columns. Is there a setting in PH that tells the dictionary to "naturally"
trunc()?</FONT></P>
<P><FONT face=Arial size=2>Thanks again gang.</FONT> </P>
<P><I><FONT face=Arial color=#0000ff size=4>Fernando Olmos</FONT></I>
<BR><B><FONT face=Arial size=2>M.I.S.</FONT></B> </P>
<P><SPAN lang=en-au><B><FONT face=Arial color=#0000ff
size=2>HPA</FONT></B></SPAN><B><SPAN lang=en-us></SPAN></B><SPAN
lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us><FONT
face=Arial><BR></FONT></SPAN><SPAN lang=en-au></SPAN><SPAN lang=en-au><FONT
face=Arial color=#000000 size=2>Direct: 03 9217 5411</FONT></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us><BR></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-au></SPAN><SPAN lang=en-au></SPAN><SPAN
lang=en-au><FONT face=Arial color=#000000 size=2>Mobile:
0410 382 857</FONT></SPAN><SPAN lang=en-us></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-us><FONT face=Arial><BR></FONT></SPAN><SPAN
lang=en-au></SPAN><SPAN lang=en-au><FONT face=Arial color=#000000
size=2>Fax: 03 9217 5716</FONT><FONT
face=Arial></FONT></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us> </SPAN></P>
<P><SPAN lang=en-au></SPAN><A
href="file:///H:/Appdata/Microsoft/Signatures/www.hpa.com.au"><SPAN
lang=en-au></SPAN><SPAN lang=en-au><U></U></SPAN><SPAN lang=en-au><U><B><FONT
face=Arial color=#0000ff size=2>www.hpa.com.au</FONT></B></U></SPAN><SPAN
lang=en-au></SPAN></A><SPAN lang=en-au></SPAN><SPAN lang=en-au></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN
lang=en-us></SPAN> </P><BR><PRE>**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,
then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
</PRE></BLOCKQUOTE></DIV>
<DIV> </DIV>
<DIV> <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>
</DIV>
<DIV>
<P>
<FONT FACE="Arial" SIZE="2"> </FONT>
</P>
</DIV></BODY></HTML>