Re: SQL index over field from another file

Be sure to deal with JDFTVAL() specification [err... lack of] on the OPNQRYF then. As shown, the Open Query File request is coded as an INNER JOIN. If the SQL VIEW is queried by the OPNQRYF, note that the default record is generated with the NULL value which sorts highest whereas /default values/ generated for the default record will often have the /name/ column as a character field with default of blanks sorting as the lowest value.

Regards, Chuck

On 22 Apr 2013 13:46, Scott Mildenberger wrote:

<<SNIP>>
I agree that outer join doesn't make sense unless something is wrong
but I thought it would be a safety net in case somebody figured out
how to get a AR balance out there without a customer. The old version
of the programs would have showed it so I didn't want my program to
'hide' it. If anything ever showed, I would have to track down why
and fix it.

CRPence on Monday, April 22, 2013 2:14 PM wrote:

<<SNIP>>

FWiW, the outer join seems odd. One might expect that a customer
"master" file would be the parent, thus ensuring no possible
ABCUSTOMER value in ARBAL which is not a TUCUST value in
CUSTMAST.?

On 22 Apr 2013 10:29, Scott Mildenberger wrote:

<<SNIP>>
I have the following SQL statement which gives me the fields
in file ARBAL ordered by a field in file CUSTMAST.

select arbal.*
from ARBAL
left outer join CUSTMAST
on ABCUSTOMER = TUCUST
order by TUNAME

<<SNIP>>

Here is the OPNQRYF that accomplishes the task if that is useful:

OPNQRYF FILE((ARBAL) (CUSTMAST))
FORMAT(ARBAL)
KEYFLD((TUNAME))
JFLD((ARBAL/ABCUSTOMER CUSTMAST/TUCUST *EQ))
JORDER(*FILE)