• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

DW don't get ending space characters from a stored procedure

Hi guys,

I have a DW based on an stored procedure that have 20 space characters at the end of some rows I need to send in a file to a Bank (so its mandatory to send them).

Running in Sybase (RapidSQL) I see the 20 chars after execute my sp but in DW painter I retrieve same sp and the 20 space chars are not there !!!

What do I need to do to keep those space characters in my DW in order to save them in bank's file?

Thank you for any clue.

BerX
0
berXpert
Asked:
berXpert
  • 5
  • 2
  • 2
2 Solutions
 
Vikas_DixitCommented:
What is the datatype of the return column ? And are you explictly adding these spaces in the stored procedure ?

--VIkas
0
 
namasi_navaretnamCommented:
BerX,

If the column is defined as char then trailing spaces will be kept. If defined as varchar then trailing spaces are ignored. Is 'emptry sting is null' checked?

I created a dw out of this proc and spaces are there within dw.
create procedure sp_space
as
begin
select 'Namasi' + space(20)
end

This is the datawindow syntax.
release 8;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no )
header(height=72 color="536870912" )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=84 color="536870912" )
table(column=(type=char(26) updatewhereclause=yes name=spacecol dbname="compute_0001" )
 procedure="1 execute dbo.sp_space;1 " )
column(band=detail id=1 alignment="0" tabsequence=10 border="0" color="33554432" x="5" y="4" height="76" width="741" format="[general]" html.valueishtml="0"  name=spacecol visible="1" edit.limit=26 edit.case=any edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" netscapelayers="0" )
 

Regards-
0
 
namasi_navaretnamCommented:
You can also try somethinglike this if you need 80 chars of fixed length.

create procedure sp_space
as
begin
select rtrim(mycol) +   space(  80 - char_length(rtrim(mycol)) )
from mytable
end

regards-
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
namasi_navaretnamCommented:
berXpert,

Also check to see if a null value is added somehow in your stored proc.

select 'Test ' + NULL + '       '    --  returns null
select 'test' +  an expression that retuens null + '      '   -- returns null

In your proc you can try:

Create procedure sp_test
as
begin
SET CONCAT_NULL_YIELDS_NULL OFF

-- add your select here
end

regards-
0
 
namasi_navaretnamCommented:
Opps, sorry. this for sql server, not sybase.

SET CONCAT_NULL_YIELDS_NULL OFF
0
 
berXpertAuthor Commented:
Vikas, column data type is Char(69) and I'm explictly adding these spaces like in first sp posted by Namasi.


Thank you Namasi, I tried your first sample but it fails. I have PB9 and maybe it's a bug because when I build your DW it said spacecol it's a string(26) but trailing spaces are not there :0(
0
 
Vikas_DixitCommented:
Check if in your DB profile, There is some attribute set to remove trailing blanks .....
Otherwise you may need to do it at the DW level.
--VIkas
0
 
namasi_navaretnamCommented:
My pb 9 has expired. I could not verify. Good Idea, Vikas.


PB9 help says:
To specify that PowerBuilder should not trim trailing spaces:

·      Database profile  Clear the Trim Trailing Spaces In CHAR Data checkbox on the Syntax tab in the Database Profile Setup dialog box.
·      PowerBuilder application script  Type the following in a PowerBuilder script:

SQLCA.DBParm = "TrimSpaces = 0"

Also check if there is a property in the Edit tab of dw column.

regards-
0
 
berXpertAuthor Commented:
Sorry for delay but solution works great, thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now