LawOledb DME string

 3 Replies
 0 Subscribed to this topic
 68 Subscribed to this forum
Sort:
Author
Messages
mcl
Advanced Member
Posts: 29
Advanced Member

    I am working on design for some custom interfaces, like garninshment detail for various vendors in their predescribed format.  I am using C# .net and comparing ODBC vs. LawOledb access.  Would like to use LawOledb for security setup, but I have trouble duplicating the database access string.  I have listed below the ODBC that pulls from PAYDEDUCTN, EMPLOYEE and PREMPGARN.  I cannot figure out how to do the same in LawOledb. I insert  PREMPGARN.CASE-NUMBER  and get an error.

    Any help will be greatly appreciated.  Let me know also if I am totally on the wrong path.

    ODBC =

    str_sql = @"SELECT PAYDEDUCTN.COMPANY,
                                   PAYDEDUCTN.EMPLOYEE,
                                   PAYDEDUCTN.CHECK_ID,
                                   PAYDEDUCTN.DED_CODE,
                                   PAYDEDUCTN.CHECK_DATE,
                                   PAYDEDUCTN.PER_END_DATE,
                                   PAYDEDUCTN.DED_AMT,
                                   PREMPGARN.CASE_NUMBER,
                                   PAYDEDUCTN.GARN_OBJ_ID,
                                   EMPLOYEE.LAST_NAME
            FROM   PROD.dbo.PAYDEDUCTN PAYDEDUCTN 
            INNER JOIN PROD.dbo.EMPLOYEE EMPLOYEE 
              ON  (PAYDEDUCTN.COMPANY=EMPLOYEE.COMPANY) 
              AND (PAYDEDUCTN.EMPLOYEE=EMPLOYEE.EMPLOYEE)
            LEFT OUTER JOIN PROD.dbo.PREMPGARN PREMPGARN 
              ON  ((PAYDEDUCTN.COMPANY=     PREMPGARN.COMPANY) 
             AND  (PAYDEDUCTN.GARN_OBJ_ID= PREMPGARN.GARN_OBJ_ID)
              AND  (PAYDEDUCTN.EMPLOYEE=    PREMPGARN.EMPLOYEE))
            WHERE (PAYDEDUCTN.COMPANY='4321')
            AND  ((PAYDEDUCTN.CHECK_DATE>={ts '" + fromDatets + @"'})
            AND (PAYDEDUCTN.CHECK_DATE<={ts '" + toDatets + @"' })) 
            AND  (PAYDEDUCTN.DED_CODE='BCD1')";

    LawOledb =

    str_sql = @"dme:FILE=PAYDEDUCTN&FIELD=COMPANY;EMPLOYEE;EMPLOYEE.LAST-NAME;EMPLOYEE.FIRST-NAME;DED-CODE;DED-AMT;CHECK-DATE;PER-END-DATE&SELECT=COMPANY=4321%26DED-CODE=BCD1%26(CHECK-DATE%3E%3D" + fromDate + "%26CHECK-DATE%3C%3D" + toDate + ")";

    John Henley
    Posts: 3353
      OLEDB can only returned related fields for relationship defined in the Lawson dictionary. Since there is not a relationship between PAYDEDUCTN and PREMPGARN, that is why you are getting the error. You will need to make a separate DME call against the PREMPGARN table to get the case number.
      Thanks for using the LawsonGuru.com forums!
      John
      mcl
      Advanced Member
      Posts: 29
      Advanced Member
        Thanks. I receive a lot of "gotcha" messages from my program, primarily because I get my dots, dashes, and underlines confused. Who decided that the field name should change based on access method?

        More questions: Is the DME call the only way to approach OLEDB in Lawson? Can we "nest" DME calls the same as nesting SELECT's in sql? If so, do you have an example? And, what is my best source for understanding the DME syntax? I figured out what I know now by looking at sql syntax in Crystal.

        John Henley
        Posts: 3353
          Dashes in field names are used in COBOL, and were carried over to DME (which originated as a web version of rngdbdump). Dashes aren't allowed in RDBMS column names, so Lawson's data layer changes them to underscores when talking to the database. Since LawOLEDB uses DME as the table interface, it would be a nice enhancement for Lawson to swap underscores for dashes when passing to query string to DME.

          You cannot nest DME strings.

          Look on Lawson support site in dec section for documents for "IOS Developers", which has the DME/Data syntax.
          Thanks for using the LawsonGuru.com forums!
          John