× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Robert

As of 7.4 there is a new SQL table function, COMPARE_FILE, that can tell you differences between tables, both data and attributes. The files being compared need to be on the system.

I hope you're on 7.4!!  :)

Regards
Vern

On 4/8/2022 8:12 AM, Robert Rogerson wrote:
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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.