Exporting Fixed Length records 1

[MsHart2u]

revisiting a script I am working on, Script works brilliantly, except that I need help on fixing the length of 2 text fields to be required lengths of 30 and 15, respectively. Ideally, I am attempting to create and export from SQL fixed length records. Please advise

The code used in attempting to force the length are as follows:
case when len(@Title)>=30 THEN left(@Title,30) ELSE (@Title+ space(30-len(@Title))) END +
case when len(@AllAuthors)>=15 THEN left(@AllAuthors,15) ELSE (@AllAuthors+ space(15-len(@AllAuthors))) END

Current output results:
1436450232Rio de Janeiro 3Fallon, SteveLPlanet01499TVLTP0901
178645092XSouth Africa Lesotho & SwazilaDoggett, ScottLPlanet02499TVLTP0102
186450322XSouth Africa Lesotho & SwazilaRichmond, SimonLPlanet02499TVLTP0102

Required/Desired Output:
1436450232Rio de Janeiro 3 Fallon, Steve LPlanet01499TVLTP0901
1864503076South Africa Lesotho & SwazilaDoggett, Scott LPlanet02499TVLTP0102
186450322XSouth Africa Lesotho & SwazilaRichmond, SimonLPlanet02499TVLTP0102

Thanks alot for your help.

Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[nigelrivett]

left(@Title + space(30),30) + left(@AllAuthors + space(15),15)

and to right justify
right(space(30) + @Title,30)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MsHart2u]

Nigelrivett, et.al,
With your suggestion, it ran successfully, but still fails to fix the output field lengths. Results are the same as before. Here is my code in its entirety:

 -- Create a forward-only cursor to collect the data
delete from tblROSIRecords
declare myCursor INSENSITIVE CURSOR FOR
  Select distinct txtISBN, txtTitle,txtAuthor,txtPublisher, 
	   ListPrice,txtCategory,txtBookType, dtmPubDate,tfPrimaryAuthor
    From tmpROSI 
    order by txtISBN,tfPrimaryAuthor DESC, txtAuthor
Open myCursor
-- local variables
declare @CurrISBN varchar(10)
declare @prevISBN varchar(10)
declare @Title varchar(30)
declare @CurrAuthor varchar(15)
declare @Publisher varchar(7)
declare @ListPrice varchar(5)
declare @Category varchar(3)
declare @BookType varchar(2)
declare @PubDate varchar(4)
declare @primary bit
declare @AllAuthors varchar(200)
declare @Space14 varchar(14)
declare @Space6 varchar(6)
declare @QOH varchar(5)
declare @Spaces varchar(25)
set @CurrAuthor = ''
set @AllAuthors = ''
set @prevISBN = '-1'-- Prevent Insert of 1st record 
Set @Space14=space(14)
Set @Space6=space(6)
Set @QOH=space(5)
Set @Spaces = @Space14+@QOH+@Space6

Fetch myCursor into @CurrISBN, @Title,@CurrAuthor,@Publisher,
	@ListPrice,@Category,@BookType,@PubDate,@primary

While @@fetch_status = 0
BEGIN
  If @CurrISBN <> @prevISBN
	if @prevISBN!='-1'
         Begin
 INSERT tblROSIrecords VALUES (@prevISBN+ left(@Title + space(30),30) 
	+ left(@AllAuthors + space(15),15)+@Publisher+@ListPrice+@Category+@BookType+@PubDate+@Spaces)
	   SET @AllAuthors = ''
         End
  set @prevISBN = @CurrISBN
  IF LEN(@AllAuthors)>1 
	set @AllAuthors = LTRIM(@AllAuthors+'; ') + RTRIM(@CurrAuthor) 
	ELSE set @AllAuthors = RTRIM(@CurrAuthor)

Fetch myCursor into @CurrISBN, @Title,@CurrAuthor,@Publisher,
	@ListPrice,@Category,@BookType,@PubDate,@primary
END
 INSERT tblROSIrecords VALUES (@prevISBN+ left(@Title + space(30),30) 
	+ left(@AllAuthors + space(15),15)+@Publisher+@ListPrice+@Category+@BookType+@PubDate+@Spaces)
close myCursor
deallocate myCursor

Perhaps my error is the way I'm using it?
Thanks, in advance, for your help.

Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[MsHart2u]

OOPS-Y. Your suggestion worked. I have to look at my DTS and see why it isn't exporting the record.

Hands down. Thank You, NigelRivett.


Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[nigelrivett]

>> see why it isn't exporting the record.
Look for nulls.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.