DB2 issue with assinging TableName on a select statement, How to?
FYI: Here is the complete YTD Sproc in MSSQL Format
The attached sproc works correctly in the in the micorsoft environment and is derived from there.
I need to convert this to a DB2 Sproc. I have an immediate concern now and will be asking additional new questions. The issue revolves around empcount & NumEmployees.
empcount is a derived table from a select statement I cannot make this work in DB2 environment. Please provide a solution that will work.
Here is the text of the section in which the empcount is created:
(This info is marked in yellow highlighter where it applies in the attachment)
INNER JOIN (
SELECT
HirePosition, COUNT(EmpID) AS NumEmployees
FROM
dbo.tbl_LuRt_Employee
WHERE
TermDate IS NULL
AND Salary >= 100
GROUP BY
HirePosition) empcount
ON e.HirePosition = empcount.HirePosition
sproc---YTD---mssqlformat.docx
The attached sproc works correctly in the in the micorsoft environment and is derived from there.
I need to convert this to a DB2 Sproc. I have an immediate concern now and will be asking additional new questions. The issue revolves around empcount & NumEmployees.
empcount is a derived table from a select statement I cannot make this work in DB2 environment. Please provide a solution that will work.
Here is the text of the section in which the empcount is created:
(This info is marked in yellow highlighter where it applies in the attachment)
INNER JOIN (
SELECT
HirePosition, COUNT(EmpID) AS NumEmployees
FROM
dbo.tbl_LuRt_Employee
WHERE
TermDate IS NULL
AND Salary >= 100
GROUP BY
HirePosition) empcount
ON e.HirePosition = empcount.HirePosition
sproc---YTD---mssqlformat.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
which os and version of db2 are you trying to use db2/udb varies significantly between platforms and versions....
the sql you showed however is vanilla enough to work across all recent versions...
what error are you actually generating?
the sql you showed however is vanilla enough to work across all recent versions...
what error are you actually generating?
ASKER
The problems I am experiencing are the fact that I have only two views to chose from in DB2 for my data, which is significantly different than what the orginal sproc is calling for.
MSSQL:
1. tbl_LuRt_Employee e FirstName, LastName, TermDate, HirePosition
2. tbl_LuRt_Employee EmpId
3. vw_LuRt_Stores s - StoreNumber
4. tbl_LuRt_Position_SortOrde
DB2 views:
1. Employees - FirstName, LastName, Termdate,EmpId,Location(st
2. Transactions - StoreNumber,HirePosition, Salary
----> Does not exist in DB2:
4. tbl_LuRt_Position_SortOrde
MSSQL:
1. tbl_LuRt_Employee e FirstName, LastName, TermDate, HirePosition
2. tbl_LuRt_Employee EmpId
3. vw_LuRt_Stores s - StoreNumber
4. tbl_LuRt_Position_SortOrde
DB2 views:
1. Employees - FirstName, LastName, Termdate,EmpId,Location(st
2. Transactions - StoreNumber,HirePosition, Salary
----> Does not exist in DB2:
4. tbl_LuRt_Position_SortOrde
ASKER
SELECT CONCAT(EMP.FIRSTNAME, EMP.LASTNAME) AS EmpName,emp.location,
EMP.TermDate, trans.position,(1.0/empcou
FROM DB2.EMPLOYEES EMP, DB2.TRANSACTIONS TRANS
INNER JOIN (SELECT TRANS.Position, COUNT(TRANS.EMPLOYEEID) AS NumEmployees
FROM DB2.TRANSACTIONS TRANS, DB2.EMPLOYEES EMP
WHERE TRANS.Salary >= 100
--AND EMP.TermDate IS NULL AND <-------- I get better results when this is marked out all
columns return but the termdate column is blank. Otherwise when it is not marked out it
returns all columns as empty.
GROUP BY TRANS.Position ) empcount
ON TRANS.POSITION = empcount.POSITION
So I would consider this section is close to working just need to resolve the problem concerning the termdate.
EMP.TermDate, trans.position,(1.0/empcou
FROM DB2.EMPLOYEES EMP, DB2.TRANSACTIONS TRANS
INNER JOIN (SELECT TRANS.Position, COUNT(TRANS.EMPLOYEEID) AS NumEmployees
FROM DB2.TRANSACTIONS TRANS, DB2.EMPLOYEES EMP
WHERE TRANS.Salary >= 100
--AND EMP.TermDate IS NULL AND <-------- I get better results when this is marked out all
columns return but the termdate column is blank. Otherwise when it is not marked out it
returns all columns as empty.
GROUP BY TRANS.Position ) empcount
ON TRANS.POSITION = empcount.POSITION
So I would consider this section is close to working just need to resolve the problem concerning the termdate.
so you don't get any error from the database right?
it is a logical problem
it is a logical problem
ASKER
I am out until thursday in the AM CST Feel Free to respond Please review the last two comments above this as comment. I have seen several contributions which have moved me forward, I look forward to completing this tomorrow July 5.
which error are you getting?
can you post the complete statement?