[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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