• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Data source / sql question

 7 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
vc727
Advanced Member Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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/
Deleted User
New Member Send Private Message
Posts: 0
New 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 Send Private Message
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.