pb7 - sql2k8 - need to display 'X' for null values getting from db

[Former Member]

Friends,

In our sql 2k8 table..we have null values in one of the students mark field.

while displaying those marks in the datawindow ... how can i display the null values as 'X'.

I want to replace the null with 'X' in the datawindow.

now my datawindow is displaying the null values as 0.

i am not able to change the 0 to 'X'

i changed the columns display type as ddlistbox and all the below for the datavalue in ddlb

1. 'Null!' (both case upper and lower)

2. Null

3. ''

4. empty without anything

5. "Null!" (both case upper and lower)

In the select query of the datawindow query design i tried with the case like

case when field is null then 'X'

this also not working again its displaying as 0. in the db the column value shows as NULL

thanks

[arnd_schmidt · Accepted solution]

You can use a display format for the column like "0;-0;0;\X" to display null as 'X'.

PowerBuilder 10.5 > User's Guide > Displaying and Validating Data > Defining display formats

hth

Arnd

[Former Member]

Is the column in the datawindow editable?

[Former Member]

Hi.

I think the easiest way to change the value of null is to use the isnull sql function....

isnull(<column name>, <new value>).

Example in a query (using the demo CustDB provided with SQL Anywhere and Powerbuilder):

select order_id,

cust_id,

prod_id,

emp_id,

disc,

quant,

isnull(notes, 'X') as notes,

status,

last_modified

from DBA.ULOrder;

Andreas.

[Former Member]

i forget to mention that notes column is a tinyint data type...

if i use isnull function then i am getting conversion error...

[Former Member]

Even in that case you can change the datatype on your query...

select order_id,

cust_id,

prod_id,

emp_id,

disc,

quant,

isnull(cast(notes as varchar(10)), 'X') as notes,

status,

last_modified

from DBA.ULOrder;

Andreas.

[Former Member]

isnull(cast(notes as varchar(10), 'X')) as notes,

now all the values are showing as 1