Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

Data source / sql question

Sort:
You are not authorized to post a reply.
Author
Messages
vc727
Advanced Member
Posts: 44
Advanced Member
8/19/2009 3:44 PM

    I am a very novice sql user who really just trys different things until it works not really knowing why. So when I was asked to create a query that gives basic employee data with a count of active dependants I created the following query that works in Toad and in the crystal designer but  when I publish it to LBI it doesn't recognize the datasource. is there a different way to write it so it will accomplish the same thing and be published to LBI?

    SELECT a.employee,
    TRIM(a.LAST_NAME)||', '||TRIM(a.FIRST_NAME)||' '||TRIM(a.MIDDLE_INIT) NAME,
    a.process_level,
    a.PL_NAME,
    a.DEPT_NAME,
    a.TITLE,
    TRIM(a.SUP_LAST_NAME)||', '||TRIM(a.SUP_FIRST_NAME) SUP_NAME,
    (SELECT COUNT(LAWPROD.EMDEPEND.LAST_NAME) TTL_DEP
    FROM LAWPROD.EMDEPEND
    INNER JOIN LAWPROD.EMPLOYEE
    ON LAWPROD.EMDEPEND.COMPANY = LAWPROD.EMPLOYEE.COMPANY
    AND LAWPROD.EMDEPEND.EMPLOYEE = LAWPROD.EMPLOYEE.EMPLOYEE
    WHERE LAWPROD.EMPLOYEE.EMP_STATUS LIKE 'A%'
    AND LAWPROD.EMDEPEND.ACTIVE_FLAG='A'
    AND LAWPROD.EMDEPEND.EMPLOYEE=a.employee
    OR LAWPROD.EMPLOYEE.EMP_STATUS LIKE 'L%'
    AND LAWPROD.EMDEPEND.ACTIVE_FLAG='A'
    AND LAWPROD.EMDEPEND.EMPLOYEE=a.employee
    GROUP BY LAWPROD.EMPLOYEE.COMPANY, LAWPROD.EMDEPEND.EMPLOYEE, lawprod.employee.last_name
    ) ttl_dep,
    a.addr1,
    a.addr2,
    a.city,
    a.state,
    a.zip
    FROM
    (SELECT
    LAWPROD.EMPLOYEE.COMPANY,
    LAWPROD.EMPLOYEE.EMPLOYEE,
    LAWPROD.EMPLOYEE.LAST_NAME,
    LAWPROD.EMPLOYEE.FIRST_NAME,
    LAWPROD.EMPLOYEE.MIDDLE_INIT,
    LAWPROD.EMPLOYEE.PROCESS_LEVEL,
    LAWPROD.PRSYSTEM.R_NAME PL_NAME,
    LAWPROD.DEPTCODE.R_NAME DEPT_NAME,
    LAWPROD.PAPOSITION.DESCRIPTION TITLE,
    LAWPROD.EMPLOYEE_1.LAST_NAME SUP_LAST_NAME,
    LAWPROD.EMPLOYEE_1.FIRST_NAME SUP_FIRST_NAME,
    LAWPROD.EMPLOYEE.ADDR1,
    LAWPROD.EMPLOYEE.ADDR2,
    LAWPROD.EMPLOYEE.CITY,
    LAWPROD.EMPLOYEE.STATE,
    LAWPROD.EMPLOYEE.ZIP
    FROM
    LAWPROD.EMPLOYEE
    INNER JOIN
    LAWPROD.DEPTCODE
    ON
    LAWPROD.EMPLOYEE.PROCESS_LEVEL = LAWPROD.DEPTCODE.PROCESS_LEVEL
    AND
    LAWPROD.EMPLOYEE.DEPARTMENT = LAWPROD.DEPTCODE.DEPARTMENT
    AND
    LAWPROD.EMPLOYEE.COMPANY = LAWPROD.DEPTCODE.COMPANY
    INNER JOIN
    LAWPROD.PRSYSTEM
    ON
    LAWPROD.EMPLOYEE.PROCESS_LEVEL = LAWPROD.PRSYSTEM.PROCESS_LEVEL
    AND
    LAWPROD.EMPLOYEE.COMPANY = LAWPROD.PRSYSTEM.COMPANY
    LEFT JOIN
    LAWPROD.HRSUPER
    LEFT JOIN
    LAWPROD.EMPLOYEE EMPLOYEE_1
    ON
    LAWPROD.HRSUPER.EMPLOYEE = EMPLOYEE_1.EMPLOYEE
    AND
    LAWPROD.HRSUPER.COMPANY = EMPLOYEE_1.COMPANY
    ON
    LAWPROD.EMPLOYEE.SUPERVISOR = LAWPROD.HRSUPER.CODE
    AND
    LAWPROD.EMPLOYEE.COMPANY = LAWPROD.HRSUPER.COMPANY
    LEFT JOIN
    LAWPROD.PAPOSITION
    ON
    LAWPROD.EMPLOYEE.COMPANY = LAWPROD.PAPOSITION.COMPANY
    AND
    LAWPROD.EMPLOYEE.POSITION = LAWPROD.PAPOSITION.POSITION
    WHERE
    LAWPROD.PAPOSITION.END_DATE='01jan1700'
    AND
    LAWPROD.EMPLOYEE.EMP_STATUS LIKE 'A%'
    OR
    LAWPROD.EMPLOYEE.EMP_STATUS LIKE 'L%'
    AND
    LAWPROD.PAPOSITION.END_DATE='01jan1700') a
    ORDER BY a.process_level

    mikeP
    Veteran Member
    Posts: 151
    Veteran Member
    8/19/2009 4:09 PM
      I don't know all the ins and outs of datasources in LBI and I've only worked with ODBC datasources,  but what has worked for is to use a system DSN on the development PC that has the exact same name and data area as a system DSN on the LBI server.  I asked the LBI admin for the names of the DSNs he created on the server, then created the DSNs on my dev PC using those names.
      vc727
      Advanced Member
      Posts: 44
      Advanced Member
      8/19/2009 4:21 PM
        The LAWPROD is the DSN that I use on both I think where it is losing it is when I put the queries in ( ). It seems that once that is done LBI can't find it any longer.
        Char
        Veteran Member
        Posts: 62
        Veteran Member
        8/19/2009 4:45 PM
          Native Oracle will run much faster and it requires no DSN on the server - I use it exclusively for my Oracle clients; it's not a direct answer to what's going on but it should get you around the problem and improve the performance. I recently opened a report that a client wrote and started running it with his ODBC connection and I noticed how slow it was so I opened a second copy of Crystal and looked around to see if he'd done something to make the query run slow and I found nothing so after about 5 minutes, I just set the location of the second copy of Crystal to native Oracle and ran the report - it finished in seconds and the ODBC copy was still plugging along and it had a huge head start.
          mikeP
          Veteran Member
          Posts: 151
          Veteran Member
          8/19/2009 4:48 PM
            We've not included the DSN name in the SQL, just the table name and field e.g. DBHREMP.EMPLOYEE. ( I didn't even know you could do that.) Maybe you could try removing it?
            Matthew Nye
            Veteran Member
            Posts: 514
            Veteran Member
            8/19/2009 5:26 PM
              Can you elaborate on "it doesn't recognize the datasource"? Do you mean when you try to set the Override Data Source there is nothing in the drop down even though you have one defined in Data Sources section of Server Administration? Or do you get some type of error when you run the report? If so can you post the error and maybe a screen shot?

              If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
              Adam Jacobson
              Veteran Member
              Posts: 69
              Veteran Member
              8/19/2009 5:59 PM
                First - have you tried the "use report data source option" just to see if it makes a difference?

                Also, I think you can radically simplify your query to avoid subselects. The fundamental is a query of your EMPLOYEE table with a left outer join TO EMDEPEND (because some employees have no dependents).
                The wacky SUM statement gets around the possibility of there being a null in the EMDEPEND file while we still insure that we have every record from the employee file.
                Also, since we only want to group on one field, we create a max around each other variable we have (which is kludgy but works).
                BTW, if you're doing this in crystal, you could just simply group by EMPLOYEE and summarize there. but you really shouldn't need subselects.
                SELECT
                emp.EMPLOYEE,
                max(emp.LAST_NAME||emp.first_name),
                max(dpt.DEPT_NAME),
                sum(case when dep.ACTIVTY_FLAG like 'A%' then 1 else 0 end)
                FROM
                LAWPROD.EMPLOYEE emp
                left outer join LAWPROD.EMDEPEND dep
                ON emp.COMPANY = dep.COMPANY
                AND emp.EMPLOYEE = dep.EMPLOYEE
                left outer join LAWPROD.DEPTCODE dpt
                ON emp.COMPANY = dpt.COMPANY
                AND emp.PROCESS_LEVEL = dpt.PROCESS_LEVEL
                AND emp.DEPTCODE = dpt.DEPTCODE
                GROUP BY
                emp.EMPLOYEE
                vc727
                Advanced Member
                Posts: 44
                Advanced Member
                8/20/2009 2:23 PM
                  Thanks everone I guess there is more than one way to skin a sql cat. I used some of what Adam recommended and solved my issue thanks.
                  You are not authorized to post a reply.