Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Displaying Null in Database Painter

  1. How-to
0
Votes
Undo
  1. Louis Arena
    Louis Arena
  2. PowerBuilder
  3. Monday, 13 November 2023 22:32 PM UTC

I am using a simple SQL statement in the database painter.  Some columns have the NULL value, but they are displayed as blanks.  How can I show that it is actually a NULL.  Also when I export the result set to a CSV or XLSX I want to the word NULL in the column.  I cannot find anything about how to display this.  Any thoughts?

SaveAs Excel12 Aborts when run remote
Visual Studio 2022 with PB 2022
Responses (4)
Brad Mettee
Brad Mettee Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 15:27 PM UTC
  2. PowerBuilder
  3. # 1

Can you modify the select to return 'NULL' if the column value is null?

select if colname is null then 'NULL' else colname endif as NullCol from tablename;

 

Comment
Louis Arena
  1. Louis Arena
  2. Tuesday, 14 November 2023 15:42 PM UTC
Yes you can I just did it. for example, select referred_by = isnull(referred_by,'NULL).... But my point is why should I have to code this all the time. Why does the database painter that interfaces with the database manipulating the data it returns? What programmer of PB decided that he would change our data, and empty string is not NULL. Also, how can I be the only person complaining about this?
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 01:14 AM UTC
  2. PowerBuilder
  3. # 2

Hi Louis;

That's exactly what Display Formats are designed to do!

  • String format: "string-format;null-format"
  • Example: "@@@@@@";NULL"
  • Number format: Positive-format;negative-format;zero-format;null-format
  • Example: "[green];[red];Zero;Null"

FYI: https://docs.appeon.com/pb2022/pbug/ug69750.html#d0e41819

Display formats can be used in both the DW & DB painters. ;-)

Regards ... Chris 

Comment
John Fauss
  1. John Fauss
  2. Tuesday, 14 November 2023 02:30 AM UTC
But can a display format be used to represent a null column value as the string NULL when the data is exported to CSV or XLSX formats? That was Louis' second question.
  1. Helpful 1
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 14 November 2023 15:58 PM UTC
Hi John;

Since PB will export the data to Excel from it's primary buffer where the datum is a mirror image of what the DBMS result set was, no formatting will be done on the PB side. However, if Louis opens the Excel data file in an Excel "template" he can have Excel format the datum the same was as the DW does using Display Formats via an Excel macro. In fact, PB designed the DF feature originally after the Excel DF / Macro standard. ;-)

FYI: =IF(ISBLANK(B4),"Customer Name","")

Also, Louis could use OLE and transfer the data to the Excel spreadsheet cells under PowerScript control and thus format each cell as per the IsNull() test. That would be another way. HTH

Regards ... Chris
  1. Helpful
There are no comments made yet.
Louis Arena
Louis Arena Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 14:47 PM UTC
  2. PowerBuilder
  3. # 3

Also, where/how in the database painter when you execute your SQL can you format the column?  Obviously the datawindow is easy to manipulate.  But when you run the SQL and you are in the results tab, there is no way to set the string function on a column.  And it appears that when and if you can set a string function to display the value as NULL you will not be able to export NULL to CSV or XLXS.  Microsoft ISQL Manager displays the NULL value, which PC should as well because that is actually the value, it is not an empty string.  However, in MS ISQL I cannot export to CVS with headers.  So both applications are lacking.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 14 November 2023 15:51 PM UTC
Hi Louis;

Display Formats are also available to use in the DB Painter as well! ;-)

Regards ... Chris
  1. Helpful
Louis Arena
  1. Louis Arena
  2. Tuesday, 14 November 2023 15:59 PM UTC
see my question above, where/how do you get to the display formats in the DB Painter because I cannot find it.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 16:39 PM UTC
  2. PowerBuilder
  3. # 4

Hi Louis;

  For the DB Painter, it's a two step process

1) Create the require DF ...

2) Attach the DF to the appropriate column ...

3) Run your SQL. You should now see the DF's active.  ;-)

HTH

Regards ... Chris

Comment
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.