Re: wrkqry vs sql result difference

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

Ensure that your sql job is set to not use *lngshr and hex-sort. Just to
leave that issue out.

Since your join uses the named logical files where the sql Finds which’s
logically file fits the query best and sort according to you job settings.


tir. 22. sep. 2020 kl. 15.03 skrev Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx
> :

> I have a query that I'm trying to convert to sql.
>
> The query has a join file(2) matched records with primary file
>
> Sorted by area and account
>
>
>
> There are a dozen or so records not in the secondary file however, when
> the results display I get something like this
>
>
>
> AREA                     Account
>
>
>
> '      '                       1
>
> '     '                      2
>
> '     '                      3
>
> AREA1                   10
>
> AREA1                 11
>
>
>
>
>
> When I convert this to SQL I don't' get the same results, because of the
> NULL values, but I can't figure out how to get the null converted to a
> space in the query
>
> My results look like this
>
>
>
> AREA                     Account
>
> '    '                         2
>
> AREA1                   10
>
> AREA1                   11
>
>
>
> -                         1
>
>
>
> -                          3
>
>
>
>
>
> I have tried using select  ifnull(ara, ' ') as area
>
> And                                    coalesce(ara, ' ') as area
>
> But no luck
>
>
>
> My query looks like this
>
>
>
> Select ara, acct, name
>
> From file1  left outer join file2
>
> On acct = f2acct
>
> Order by ara, acct
>
>
>
> Thanks
>
>
>
> MIke
>
>
>
>
>
>
>
> NOTICE: This message, including any attachment, is intended as a
> confidential and privileged communication. If you have received this
> message in error, or are not the named recipient(s), please immediately
> notify the sender and delete this message.
>
> --
>
> 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