Re: SQL index over field from another file

[CRPence <CRPbottle@xxxxxxxxx>]

  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)