Re: Run SQL Scripts and saving NULL to Excel

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I need to make a correction - there is an RDB parameter for the 2nd 
file, so you can compare between systems - very cool!

Simon Hutchinson has an article on it here - 
https://www.rpgpgm.com/2020/06/using-sql-to-compare-files-for.html

Cheers
Vern

On 4/8/2022 11:35 AM, Vern Hamberg via MIDRANGE-L wrote:
> 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