Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-05
9
Medium Priority
?
640 Views
Last Modified: 2013-12-26
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
Comment
Question by:berXpert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 8

Expert Comment

by:Vikas_Dixit
ID: 10761915
What is the datatype of the return column ? And are you explictly adding these spaces in the stored procedure ?

--VIkas
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10762878
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10762888
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10765388
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10765419
Opps, sorry. this for sql server, not sybase.

SET CONCAT_NULL_YIELDS_NULL OFF
0
 
LVL 5

Author Comment

by:berXpert
ID: 10765765
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
 
LVL 8

Assisted Solution

by:Vikas_Dixit
Vikas_Dixit earned 100 total points
ID: 10768881
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
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 400 total points
ID: 10771003
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
 
LVL 5

Author Comment

by:berXpert
ID: 10932967
Sorry for delay but solution works great, thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question