[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] Update query
- Subject: RE: [sqr-users] Update query
- From: "Alexander, Steve" <Steven.Alexander@sanjoseca.gov>
- Date: Thu, 10 Nov 2005 09:44:50 -0800
- Delivery-date: Thu, 10 Nov 2005 12:45:40 -0500
- List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
I think you want to take each employee in PS_EMPLOYEES and look up his or
her supervisor. You could simplify the SQL by looking for the supervisor
data in PS_EMPLOYEES rather than in a complex join of PS_JOB and
PS_PERSONAL_DATA. But even if you did that, I think you are trying to store
an entire temporary table of all the supervisors in two fields of each row
of PS_EMPLOYEES. You only want one supervisor for each employee.
-----Original Message-----
From: Thompson, Betty P [mailto:thompsob@uww.edu]
Sent: Thursday, November 10, 2005 7:07 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] Update query
Oh, my... That didn't come through the way I had intended.
I'll try again:
UPDATE PS_EMPLOYEES
SET (SUPERVISOR_ID,SUPERVISOR_NAME) =
------------------------------------------------------ copy begin
(
SELECT
SUP.EMPLID
, SUP.NAME
FROM PS_PERSONAL_DATA SUP
, PS_JOB J
WHERE J.POSITION_NBR = PS_EMPLOYEES.REPORTS_TO
AND J.EMPLID = SUP.EMPLID
AND PS_EMPLOYEES.REPORTS_TO != ' '
AND J.EMPL_RCD = 0
AND PS_EMPLOYEES.EMPL_STATUS IN
('A','P','L','S','T','U','R')
AND J.EFFDT =
(
SELECT MAX(C.EFFDT)
FROM PS_JOB C
WHERE C.EMPLID = J.EMPLID
AND C.EMPL_RCD = J.EMPL_RCD
AND C.EFFDT <= SYSDATE
)
AND J.EFFSEQ =
(
SELECT MAX(D.EFFSEQ)
FROM PS_JOB D
WHERE D.EMPLID = J.EMPLID
AND D.EMPL_RCD = J.EMPL_RCD
AND D.EFFDT = J.EFFDT
AND J.EMPL_STATUS IN ('A','P','L','S')
)
)
------------------------------------------------------ copy end
---- omit ----WHERE EMPLID IN
---- omit ---- (
---- omit ---- SELECT JJ.EMPLID
---- omit ---- FROM PS_JOB JJ
---- omit ---- WHERE JJ.EMPLID =
PS_EMPLOYEES.EMPLID
---- omit ---- AND PS_EMPLOYEES.REPORTS_TO != ' '
---- omit ---- AND PS_EMPLOYEES.EMPL_STATUS IN
('A','P','L','S','T','U','R')
---- omit ---- AND JJ.EFFDT =
---- omit ---- (
---- omit ---- SELECT
MAX(CC.EFFDT)
---- omit ---- FROM PS_JOB CC
---- omit ---- WHERE
CC.EMPLID = JJ.EMPLID
---- omit ---- AND
CC.EMPL_RCD = JJ.EMPL_RCD
---- omit ---- AND CC.EFFDT
<= SYSDATE
---- omit ---- )
---- omit ---- AND JJ.EFFSEQ =
---- omit ---- (
---- omit ---- SELECT
MAX(DD.EFFSEQ)
---- omit ---- FROM PS_JOB DD
---- omit ---- WHERE
DD.EMPLID = JJ.EMPLID
---- omit ---- AND
DD.EMPL_RCD = JJ.EMPL_RCD
---- omit ---- AND DD.EFFDT =
JJ.EFFDT
---- omit ---- AND
JJ.EMPL_STATUS IN ('A','P','L','S')
---- omit ---- )
---- omit ---- )
WHERE EXISTS <<<<<<<<<<<<--------------------------- add this line
<<<<<<<<<<<<--------------------------- and then copy
in the block of code from above
-----Original Message-----
From: sqr-users-bounces+thompsob=uww.edu@sqrug.org
[mailto:sqr-users-bounces+thompsob=uww.edu@sqrug.org] On Behalf Of
Thompson, Betty P
Sent: Thursday, November 10, 2005 9:02 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] Update query
I do most of my coding in Textpad, which makes it easy to block and
shift lines of code. I find formatted code much simpler to read and
comprehend, and therefore, formatting helps minimize coding errors.
As for your sql... Try this:
UPDATE PS_EMPLOYEES
SET (SUPERVISOR_ID,SUPERVISOR_NAME) =
------------------------------------------------------ copy begin
(
SELECT
SUP.EMPLID
, SUP.NAME
FROM PS_PERSONAL_DATA SUP
, PS_JOB J
WHERE J.POSITION_NBR = PS_EMPLOYEES.REPORTS_TO
AND J.EMPLID = SUP.EMPLID
AND PS_EMPLOYEES.REPORTS_TO != ' '
AND J.EMPL_RCD = 0
AND PS_EMPLOYEES.EMPL_STATUS IN
('A','P','L','S','T','U','R')
AND J.EFFDT =
(
SELECT MAX(C.EFFDT)
FROM PS_JOB C
WHERE C.EMPLID = J.EMPLID
AND C.EMPL_RCD = J.EMPL_RCD
AND C.EFFDT <= SYSDATE
)
AND J.EFFSEQ =
(
SELECT MAX(D.EFFSEQ)
FROM PS_JOB D
WHERE D.EMPLID = J.EMPLID
AND D.EMPL_RCD = J.EMPL_RCD
AND D.EFFDT = J.EFFDT
AND J.EMPL_STATUS IN ('A','P','L','S')
)
)
------------------------------------------------------ copy end
---- omit ----WHERE EMPLID IN
---- omit ---- (
---- omit ---- SELECT JJ.EMPLID
---- omit ---- FROM PS_JOB JJ --- you
omitted EMPL_RCD = 0
---- omit ---- WHERE JJ.EMPLID =
PS_EMPLOYEES.EMPLID
---- omit ---- AND PS_EMPLOYEES.REPORTS_TO != ' '
---- omit ---- AND PS_EMPLOYEES.EMPL_STATUS IN
('A','P','L','S','T','U','R')
---- omit ---- AND JJ.EFFDT =
---- omit ---- (
---- omit ---- SELECT
MAX(CC.EFFDT)
---- omit ---- FROM PS_JOB CC
---- omit ---- WHERE
CC.EMPLID = JJ.EMPLID
---- omit ---- AND
CC.EMPL_RCD = JJ.EMPL_RCD
---- omit ---- AND CC.EFFDT
<= SYSDATE
---- omit ---- )
---- omit ---- AND JJ.EFFSEQ =
---- omit ---- (
---- omit ---- SELECT
MAX(DD.EFFSEQ)
---- omit ---- FROM PS_JOB DD
---- omit ---- WHERE
DD.EMPLID = JJ.EMPLID
---- omit ---- AND
DD.EMPL_RCD = JJ.EMPL_RCD
---- omit ---- AND DD.EFFDT =
JJ.EFFDT
---- omit ---- AND
JJ.EMPL_STATUS IN ('A','P','L','S')
---- omit ---- )
---- omit ---- )
WHERE EXISTS <<<<<<<<<<<<-----------------------------------------
add this line
<<<<<<<<<<<<-----------------------------------------
and then copy in the block of code from above
-----Original Message-----
From: sqr-users-bounces+thompsob=uww.edu@sqrug.org
[mailto:sqr-users-bounces+thompsob=uww.edu@sqrug.org] On Behalf Of
Pradnya Jawale
Sent: Thursday, November 10, 2005 5:20 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Update query
Hi all,
I am trying to update supervisor is and name in employees table with
following query.
UPDATE PS_EMPLOYEES
SET (SUPERVISOR_ID,SUPERVISOR_NAME) = ( SELECT SUP.EMPLID , SUP.NAME
FROM PS_PERSONAL_DATA SUP
, PS_JOB J
WHERE J.POSITION_NBR = PS_EMPLOYEES.REPORTS_TO
AND J.EMPLID = SUP.EMPLID
AND PS_EMPLOYEES.REPORTS_TO != ' '
AND J.EMPL_RCD = 0
AND PS_EMPLOYEES.EMPL_STATUS IN ('A','P','L','S','T','U','R')
AND J.EFFDT = (
SELECT MAX(C.EFFDT)
FROM PS_JOB C
WHERE C.EMPLID = J.EMPLID
AND C.EMPL_RCD = J.EMPL_RCD
AND C.EFFDT <= SYSDATE)
AND J.EFFSEQ = (
SELECT MAX(D.EFFSEQ)
FROM PS_JOB D
WHERE D.EMPLID = J.EMPLID
AND D.EMPL_RCD = J.EMPL_RCD
AND D.EFFDT = J.EFFDT
AND J.EMPL_STATUS IN ('A','P','L','S') ) ) WHERE EMPLID IN ( SELECT
JJ.EMPLID
FROM PS_JOB JJ
WHERE JJ.EMPLID = PS_EMPLOYEES.EMPLID
AND PS_EMPLOYEES.REPORTS_TO != ' '
AND PS_EMPLOYEES.EMPL_STATUS IN ('A','P','L','S','T','U','R')
AND JJ.EFFDT = (
SELECT MAX(CC.EFFDT)
FROM PS_JOB CC
WHERE CC.EMPLID = JJ.EMPLID
AND CC.EMPL_RCD = JJ.EMPL_RCD
AND CC.EFFDT <= SYSDATE)
AND JJ.EFFSEQ = (
SELECT MAX(DD.EFFSEQ)
FROM PS_JOB DD
WHERE DD.EMPLID = JJ.EMPLID
AND DD.EMPL_RCD = JJ.EMPL_RCD
AND DD.EFFDT = JJ.EFFDT
AND JJ.EMPL_STATUS IN ('A','P','L','S') ) )
If I hardcode some employee then it runs fine. But otherwise it gives me
following error:
UPDATE PS_EMPLOYEES
*
ERROR at line 1:
ORA-01407: cannot update ("PRJ"."PS_EMPLOYEES"."SUPERVISOR_ID") to NULL
I checked the data in the source tables and there are no issues with the
data.
Anybody faced this error earlier.
Thanks in advance.
Regards,
Pradnya.
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users