stored procedure datawindow returns some blanks

stored procedure datawindow returns some blanks

Post by mccabes » Fri, 29 Aug 2003 09:27:32


I am having a problem with a datawindow that has a stored procedure as
a data source. The stored procedure uses a names hierarchy to
determine which name should go in a field, ad_name. if the first name
is null, it uses a different name, etc. There is a catch-all at the
bottom that just puts a hard-coded name in any remaining null. When I
execute the sproc from query analyzer, everything looks fine. So I
create a datawindow to display the results from the sproc, and only
ad_name values that were set on the first pass show up. Any that were
set on the second tier of the hierarchy show as blank even though they
show properly when the sproc is executed in query analyzer. I tried
hardcoding the second name, and it still comes up blank in the
datawindow. Any ideas? Would very much appreciate them...
 
 
 

stored procedure datawindow returns some blanks

Post by mccabes » Sat, 30 Aug 2003 00:35:34

UPDATE: I figured out a work-around: The only names that were being
blanked out were ones that were updated on the second, third and forth
pass. So they were null when initially inserted into my temp table in
the sproc. So I changed my sproc and used [IsNull(ad_name, '')] to
replace values that would initially have been null with empty strings.
Then later, in my 2nd, 3rd, etc. updates, I checked for [ad_name =
''] instead of [ad_name is null]. This fixed the issue that
powerbuilder had with interpreting the resultset returned to it by the
sproc. Kinda hokey, but it worked. Still don't know what was wrong
with the results that made powerbuilder *** on them...

 
 
 

stored procedure datawindow returns some blanks

Post by ralf » Tue, 02 Sep 2003 20:03:31


Hi,

Second solution: use CASE ... WHEN ... is sproc , for exaple:
SELECT
CASE
WHEN col1 IS NULL THEN 'blank'
ELSE col1
END AS ad_name
FROM
tab1

this statement gives you alternate with more clues.

Gr.
Ralf.