We help IT Professionals succeed at work.

Embedded SQL iSeries - Days calculation / compare

High Priority
147 Views
Last Modified: 2019-11-05
I need to calculate number of days, adjust it, and then use it to compare on embedded SQL statement. Details attached.  

Thank You in Advance!
Lynn days-calc-with-caveat.docx
Comment
Watch Question

Author

Commented:
I can do this with multiple sql statement and creating a work file.  I was hoping there was a way to keep is embedded off the master. ?
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Your WITH Clause construct syntax is wrong.. Since I don't see the complete query I would refer you to check the below link to get the syntax fixed out correctly..
Please find the correct syntax here under
To use the WITH clause to run multiple subqueries in multiple clauses in a SELECT statement.
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.1.0/com.ibm.nz.dbu.doc/r_dbuser_with_clause.html
Code attached for your reference..
WITH    manager (mgr_id, mgr_name, mgr_dept) AS 
   (SELECT id, name, grp
    FROM emp_copy
    WHERE mgr = id AND grp != 'gone'), 
employee (emp_id, emp_name, emp_mgr) AS 
   (SELECT id, name, mgr_id
    FROM emp_copy JOIN manager ON grp = mgr_dept),
mgr_cnt (mgr_id, mgr_reports) AS
    (SELECT mgr, COUNT (*)
    FROM emp_copy
    WHERE mgr != id
    GROUP BY mgr)
SELECT *
FROM employee JOIN manager ON emp_mgr = mgr_id JOIN mgr_cnt
WHERE emp_id != mgr_id
ORDER BY mgr_dept;

Open in new window

Author

Commented:
Thank You.  Much appreciated. I will work with this and reopen a new question if needed.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Sure, let me know if any assistance required further..