I just tried a select statement that would return 5 spaces between two other text
strings.
SELECT ('x' & ' ' & ' ' & 'z') FROM ...
You might be able to fit this in using the IFNULL function. The problem I see arising
is when x or z is null as well
IFNULL(y,(x & ' ' & ' ' & y),LJS(y,5))
Ramsour Mike <[EMAIL PROTECTED]> wrote:
>Greetings:
>
>I need to create an ASCII fixed field formatted file (say that 3 times real
>fast). �I am using the LJS function but am having an issue with fields that
>are null. �This may have been discussed before but is there a way to embed
>space characters into a field when it is NULL?
>
>For instance: �(LJS(IFNULL,<field>,' � � ',<field>),5))
>
>which is to say that if the field is null embed five spaces otherwise use
>the contents of the field.
>
>I ended up doing a workaround that embedded XXXXX if the field was null and
>then I used R:Code to replace the XXXXX with 5 spaces in the output file.
>Not exactly elegant but it worked. �I've tried using the CHAR function, too,
>but that didn't work unless I'm missing something.
>
>Any suggestions are greatly appreciated.
>
>Incidentally, I tried the same technique in 7.0 and got the same results.
>
>Thanks
>
>Mike Ramsour
>Voice: �740-829-4340
>
>Test:
>
>SET VAR vspace TEXT = (SFIL(' ',10)) -- trying to create a text variable
>that has a length of 10 characters that are all spaces. �Also tried using
>CHAR(32)
>--
>SET VAR vlen INTEGER = (SLEN(.vspace) -- to test the length of vspace
>variable
>--
>SHOW VAR vlen gives a result of 0
>--
>SET VAR vspace TEXT = (SFIL(' ',10) + '9') -- trying to create a text
>variable that starts with 10 spaces and ends with a 9
>--
>SET VAR vlen INTEGER = (SLEN(.vspace) -- to test the length of vspace
>variable
>--
>SHOW VAR vlen gives a result of 11, i.e. 10 spaces plus 1 character
>--
>
>
--
Albert Berry
Full Time Consultant to
PSD Solutions
350 West Hubbard, Suite 210
Chicago, IL 60610
312-828-9253 Ext. 32
__________________________________________________________________
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397
Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
