RE: [EXTERNAL] RE: wrkqry vs sql result difference -- MIDRANGE-L

That's good. Glad it works for you, apologies for the confusion in missing that from the example.

Cheers,
Karl.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Smith, Mike
Sent: Tuesday, September 22, 2020 11:31 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: wrkqry vs sql result difference

Ahh,
I did not add the case to my order by. Looks like that will do it.

Thanks

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Karl Haggart
Sent: Tuesday, September 22, 2020 2:26 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: wrkqry vs sql result difference

Hi Mike,

Apologies, you will also need to cater for the case in the Order By clause. Did you do that?

Thanks,
Karl.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Smith, Mike
Sent: Tuesday, September 22, 2020 10:25 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: wrkqry vs sql result difference

I just gave this a try, with the same results.
I also used the rtvqmqry command to get the select statement, which also gave the same results.

Thanks
MIke

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Karl Haggart
Sent: Tuesday, September 22, 2020 12:25 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: wrkqry vs sql result difference

Hi Mike,

Did you try to put the " ara" section in a case statement, so something like

Select case when ARA is null then ' ' else ARA end, , acct, name On acct = f2acct Order by ara, acct

Thanks,
Karl.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon Hamberg
Sent: Tuesday, September 22, 2020 9:15 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: wrkqry vs sql result difference

Just for fun, Mike, you might try the RTVQMQRY command to pull the SELECT statement that corresponds to what is in the QRYDFN - it won't take care of the NULL issue but might be a handy way to get the SELECT statement that will get the same results - other than how NULL is handled, where Query gives a blank, SQL gives the hyphen.

Also, when you use RTVQMQRY, you can get messages that say what incompatibilities there might be.

Cheers
Vern

On 9/22/2020 8:03 AM, Smith, Mike wrote:

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.