Avatar of rhservan
rhservan
Flag for United States of America asked on

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
Microsoft SQL ServerDB2SQL

Avatar of undefined
Last Comment
rhservan

8/22/2022 - Mon
momi_sabag

this looks fine
which error are you getting?
can  you post the complete statement?
ASKER CERTIFIED SOLUTION
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lowfatspread

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?
rhservan

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_SortOrder so - HPosition, BreakOnGroups

DB2 views:
1. Employees - FirstName, LastName, Termdate,EmpId,Location(store#)
2. Transactions - StoreNumber,HirePosition, Salary
----> Does not exist in DB2:    
 4. tbl_LuRt_Position_SortOrder so - HPosition, BreakOnGroups
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
rhservan

ASKER
SELECT CONCAT(EMP.FIRSTNAME, EMP.LASTNAME) AS EmpName,emp.location,
EMP.TermDate, trans.position,(1.0/empcount.NumEmployees) AS TOPercent
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.
momi_sabag

so you don't get any error from the database right?
it is a logical problem
rhservan

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.