ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Creating Join LF

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating Join LF

    Hi All,

    I have a query as below...

    Query UserPRF that has below file selection and record selection criteria.

    File selection as below?
    File . . . . . . . . . FILE_A (File ID: T01)
    File . . . . . . . . . FILE_B (File ID: T02)
    File . . . . . . . . . FILE_C (File ID: T03)
    File . . . . . . . . . FILE_D (File ID: T04)


    2=Matched records with primary file
    UPUPRF EQ SPUSR (File ID = T02)
    UPUPRF EQ USRID3 (File ID = T03)
    UPUPRF EQ USRID (File ID = T04)


    Select record condition as below?
    SPUSR EQ ' '
    AND USRVM# NE ' '
    AND USRPRT NE ' '


    And I am trying to achieve the same using join LF as below...

    A R RDSPUSR TEXT('Active User Profile')
    A*
    A JFILE(FILE_A FILE_B +
    A FILE_C FILE_D )
    A J JOIN(1 2)
    A JFLD(UPUPRF SPUSR )
    A J JOIN(1 3)
    A JFLD(UPUPRF USRID3 )
    A J JOIN(1 4)
    A JFLD(UPUPRF USRID )
    A UPUPRF
    A JREF(1)
    A COLHDG('User')
    A USRNME
    A JREF(4)
    A COLHDG('User Name')
    A UPTEXT
    A JREF(1)
    A COLHDG('User Text')


    So when I compile the file and run using runqry or SQL then it does not produce any records but if I run the above query then it produce records on same machine, so what am I missing in my DDS of JLF?

    Thanks,
    NIL
    Cheers...
    Nil

  • #2
    Since SPUSR, USRVM#, AND USRPRT are not in the LF doesn't the query return an error?

    Comment


    • #3
      Yes those are there in my DDS. Below is the complete DDS.

      A R RDSPUSR TEXT('Active User Profile')
      A*
      A JFILE(FILE_A FILE_B +
      A FILE_C FILE_D )
      A J JOIN(1 2)
      A JFLD(UPUPRF SPUSR )
      A J JOIN(1 3)
      A JFLD(UPUPRF USRID3 )
      A J JOIN(1 4)
      A JFLD(UPUPRF USRID )
      A UPUPRF
      A JREF(1)
      A COLHDG('User')
      A USRNME
      A JREF(4)
      A COLHDG('User Name')
      A UPTEXT
      A JREF(1)
      A COLHDG('User Text')
      A USREXT
      A JREF(3)
      A COLHDG('User Division')
      A USRVM#
      A JREF(3)
      A COLHDG('User Terminal')
      A USRWS#
      A JREF(3)
      A COLHDG('User Department')
      A USRPRT
      A JREF(4)
      A COLHDG('Network')
      A SPUSR
      A JREF(2)
      A COLHDG('SP User')
      A K UPUPRF
      ****************** End of data ********************************
      Cheers...
      Nil

      Comment

      Working...
      X