Re: Run SQL Scripts and saving NULL to Excel -- MIDRANGE-L

John, I was using the query while regression testing to validate the
original file (produced by the unmodified program) matched the new file
(produced with the new modified program) produced the exact same results.
Since the new file was missing rows these were NULL rows which were easily
seen in the query results. But when I tried to Save to Excel they came out
as blanks.
So to answer your question I haven't really looked at this in Excel as I
really don't need the Excel sheet. I get all the information from the
query results. I was more curious if there was a preference or setting I
was missing. As I said, now that I know that it is an Excel issue if I
need to show NULLs in Excel in the future I'll know to look for the
solution in Excel rather than iACS.

Greg and Rob, I use COALESCE all the time but in this case I wasn't really
trying to hide NULLs. As I explained above it was more a question of why
the NULLs did not show in Excel. That has been answered.

Thanks to everyone for their valuable input and advise.

On Fri, Apr 8, 2022 at 7:51 AM Rob Berendt <rob@xxxxxxxxx> wrote:

I like using null's as God intended them.

However if you feel you just have to change them to some default value
then I recommend IFNULL at
https://www.ibm.com/docs/en/i/7.4?topic=functions-ifnull
I find it easier to understand for the newbie.

COALESCE is also an option. And it might even predate IFNULL. However
most people have to go to the manual to find out what that means. And it's
probably even more fun to the "English as a second language" crowd.
https://www.ibm.com/docs/en/i/7.4?topic=functions-coalesce

If you want to get more obscure you could try VALUE which works exactly
the same as COALESCE however the manual clearly states: COALESCE should be
used for conformance to the SQL 2003 standard.
https://www.ibm.com/docs/en/i/7.4?topic=functions-value

Which begs the question: Is IFNULL sql standard or should one use
COALESCE?

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Greg Wilburn
Sent: Thursday, April 7, 2022 4:25 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Run SQL Scripts and saving NULL to Excel

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.

I think you could also use COALESCE()....

If character:
Select myfield1, COALESCE(myNULLfield,'NULL'), etc.

If numeric
Select myfield1, COALESCE(myNULLfield,0), etc

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Robert Rogerson
Sent: Thursday, April 7, 2022 11:39 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Run SQL Scripts and saving NULL to Excel

I tried Google and the IBM site but couldn't find an answer.

I run a query that results in NULL values. When I try to save the results
to a spreadsheet (using Save As on the context menu) the columns that
contained NULLs in the results show as blanks in the Excel spreadsheet.

I've tried changing the values for nulls in Edit->Preferences->Results Tab
to NULL, (NULL), <NULL> but these appear to only change what is displayed
on the results pane.

Does anyone know what needs to be changed so that when a NULL is
encountered in the results and then I save the results to Excel the NULL is
preserved (or at least not blank).

Any advise would be appreciated,

Thanks,

Rob
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com