Problem aligning to the left

[dndaughtery]

I have a query which doesn't show any data for any of the fields which I have set to align to the left as follows


LEFT(Replicate(' ', 15) + IsNull(CONVERT(VARCHAR, RWFSTN), ''),15)


Why is this and how can I fix it?

 

[dndaughtery]

I'm getting a NULL when the size pf the data is greater tha 20 when I use :


SELECT right(ISNULL(RWAD1,'') + replicate(' ',20 -isnull(len(RWAD1),0)), 30)
FROM CertegyImportFile_T_EC where recordtype = 'detail record'

 

[gmmastros]

I see that you are struggling. So, let me help.

Since you are doing this alot, I suggest you implent this with a couple UDF's. It will slow down the process a little, but will probably allow you to be a little more accurate.

First, the 2 UDF's

Create function LeftAlign(@Data VarChar(8000), @Length Integer)
Returns VarChar(8000)
As
Begin
	Return Left(IsNull(@Data, '') + Replicate(' ', @Length), @Length)
End

Create function RightAlign(@Data VarChar(8000), @Length Integer)
Returns VarChar(8000)
As
Begin
	Return Right(Replicate(' ', @Length) + IsNull(@Data, ''), @Length)
End

Then, to use it...

INSERT INTO TempOutputFileCertegy_T_EC
SELECT  RWGLTRT  +   
            dbo.LeftAlign(RWMRC,25) +
            dbo.LeftAlign(RWBKR,9) +
            dbo.LeftAlign(RWCK_A,18) +
            dbo.LeftAlign(RWCK,6) +
            dbo.LeftAlign(replace(convert(varchar, RWCKDT, 101), '/', ''), 8)  + 
            dbo.LeftAlign(RWCKAMT,7) +
            dbo.LeftAlign(RWFEE,5) +
            dbo.LeftAlign(RWDRL,24) +
            dbo.LeftAlign(RWDRST,2) +
            dbo.RightAlign(RWFSTN,15) +
            dbo.RightAlign(RWLSTN,15) +
            dbo.RightAlign(RWAD2,20) +
            dbo.RightAlign(RWAD1,20) +
            dbo.RightAlign(RWCITY,20) +
            dbo.RightAlign(RWSTAT,2) +
            dbo.LeftAlign(SUBSTRING(RWZIP5, 0, 6), 5) +
            dbo.LeftAlign(RWHTL, 10) +
            dbo.LeftAlign(RWWTL, 10) +
            dbo.LeftAlign(RWRTCD, 1) +
            dbo.LeftAlign(RWRJCD, 3) +
            dbo.LeftAlign(RWSTID, 6) +
            dbo.LeftAlign(RWMEMO, 15) +
            dbo.RightAlign(RWMSMO2, 30)
        FROM CertegyImportFile_T_EC
        WHERE RecordType = 'DETAIL RECORD' AND RWCKAMT > 12.94

I may not have that code perfect, so you'll need to verify that it runs OK for you. Hopefully this will help you out. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

George's suggestions saves you a lot of headaches

I have 2 questions since you are saving this to a flat file why not using DTS?
and second if the data is longer than what you need is that data still correct (after being truncated)?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/