1. shoaib siddiqui
  2. PowerBuilder
  3. Thursday, 8 August 2024 17:26 PM UTC

Hi,

I have need of a computed column in a datawindow that should result a number Or null.
I understand that in Script I can use SetNull(SomeVariable) easily, but cannot find in Expression functions 
that can do the same.

example: If( state_code ='NY' , 100, SetNull(0)  )

or If( state_code ='NY' , 100, Null )

any function that can produce a Numreric Value or null result.

Thanks.

 

Gregory Rusak Accepted Answer Pending Moderation
  1. Thursday, 8 August 2024 20:29 PM UTC
  2. PowerBuilder
  3. # 1

Another approach to this could be casting constants in your SQL to use in your datawindow expression. Something along the lines of:

SELECT
  cast(null as integer) as null_integer,
  cast(null as varchar) as null_varchar,
  etc.
FROM dummy
Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 8 August 2024 17:56 PM UTC
  2. PowerBuilder
  3. # 2

Hi, Shoaib -

You might consider creating one or more (depending on which numeric datatypes you need to support) global functions that takes no arguments but returns the data value that has been set to Null. For example:

// Global Function f_Null_Long() Returns Long
Long ll_null
SetNull(ll_null)

Return ll_null

Global functions can be used in DataWindow expressions.

Best regards, John

Comment
There are no comments made yet.
shoaib siddiqui Accepted Answer Pending Moderation
  1. Thursday, 8 August 2024 18:06 PM UTC
  2. PowerBuilder
  3. # 3

Thanks! I would try this.

Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 9 August 2024 04:47 AM UTC
  2. PowerBuilder
  3. # 4

I had same problem some years ago. After trying different things (e.g. also a computed column with a division by zero returns NULL) I've used the approach described by Gregory.

 

One more hint: Do you want to use the NULL result of the computed column in other computed columns? There is a bug in datawindows calculation with NULL values! The result of a calculation with NULL may be not NULL as expected in some cases.

see this bug report: https://www.appeon.com/standardsupport/track/view?id=245

I reported it 7 years ago but Appeon has not fixed it yet because it is a Sybase legacy bug.

Comment
  1. shoaib siddiqui
  2. Friday, 9 August 2024 11:24 AM UTC
Yes I waned to create a computed column for Graph window to plot a value conditionally, if the condition is not TRUE the don't plot the marker, but my results set always have a value, based on other condition I wanted to make it null, so Graph engine will not draw null values.

And 'No' to the usage question, I will not be using this in any other computed field or calculations.



So I created a global a function to return null and use it expression as John suggested.

I have done this in past by another technique where create datawindow argument, pass argument as null, the use that variable name in expression.

Thanks.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.