Solved

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

Posted on 2004-04-05
9
602 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
  • 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 25 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 100 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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. …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now