update isssue

Hi,
I have below query based upon below query i want to update date_expiry but it is not working.
Thanks

 update dim_employee T1
   set
   T1.DATE_EXPIRY
   =(  

   SELECT
cc.*
,to_date(cc.Date_expiry_2,'YYYYMMDD')  as Date_expiry_3
FROM
(
select
case  when  LEAD_rn ='0' then '' else  to_char(date_loaded,'YYYYMMDD') end as Date_expiry_2
,bb.*
from
(
select  distinct
Lead(b.employeeid,1,0) OVER (PARTITION BY b.employeeid, b.week ORDER BY b.date_loaded asc ) as lead_rn
,b.*
from stg_employee a
inner join dim_employee b
on a.employeeid =B.employeeid
and date_expiry is null
) bb
)cc
where cc.employeeid =T1.employeeid
and cc.week = T1.week
and cc.date_loaded = T1.date_loaded
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
In the first place, your update shows you trying to set date_expiry to more than one value, cc.* and to_date(cc.Date_expiry_2,'YYYYMMDD') which can't be. It would be of great help if you could provide some sample data for the beginning state of the table and what you would expect it to be after the update and why.
0
sam2929Author Commented:
i am doing
SELECT
distinct to_date(cc.Date_expiry_2,'YYYYMMDD')  as Date_expiry_3

error i getting is ora-00921 unexcepted end of sql command
0
awking00Information Technology SpecialistCommented:
That message just says some needed component is missing in your query and doesn't really help in understanding your problem. Tell us what it is you're trying to do and show some examples of input and desired output.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
Why are you taking date_loaded and converting it to a string then converting it back to a date in the outer query?
0
sam2929Author Commented:
Please look at sample.
All i need is expiry old record when new one is inserted.

d_employee

emplid week   datecreated dateexpired
001        212      2018-03-19    
001        212       2018-03-19    2018-03-19

001       212         2018-03-22      

001        212         2018-03-22    2018-03-19



result should be


d_employee

emplid week   datecreated dateexpired
001        212      2018-03-19      2018-03-22  ---expire this record
001        212       2018-03-19     2018-03-19

001       212         2018-03-22      

001        212         2018-03-22    2018-03-19
0
Mark GeerlingsDatabase AdministratorCommented:
I'm not sure that you need the multiple levels of nested selects and the TO_CHAR and TO_DATE conversions.  

Why not use something much simpler like this:

update dim_employee T1
    set
    T1.DATE_EXPIRY
    =(select date_loaded from stg_employee a
  where a.employeeid =T1.employeeid)
where t1.date_expiry is null
and exists (select 1 from stg_employee a
 where a.employeeid =T1.employeeid);
0
slightwv (䄆 Netminder) Commented:
Might it be possible to take a step back and possibly solve this problem in the future with a BEFORE INSERT trigger?

This way you can take the :NEW values and update the date_expiry column with the new date_loaded value.

Then you don't have to worry about the actual UPDATE syntax.
0
sam2929Author Commented:
Mark this will not work we are just updating d employee for now please look at my last comment.

So updating expiry for same table
Based emplid week and datecreated
0
Mark GeerlingsDatabase AdministratorCommented:
Then change the table for the two sub-queries and add a check of date_created like this:

update dim_employee T1
     set
     T1.DATE_EXPIRY
     =(select min(date_created) from dim_employee T2
   where T2.employeeid =T1.employeeid
    and t2.date_created > T1.date_created)
 where t1.date_expiry is null
 and exists (select 1 from dim_employee T3
  where T3.employeeid =T1.employeeid
    and t3.date_created > T1.date_created);
0
sam2929Author Commented:
Performance really bad can we use analytics functions to do it
0
Mark GeerlingsDatabase AdministratorCommented:
An index on employeeid would likely help much more than analytic functions.  I assumed that this table would have a single-column index on the employeeid column, or possibly a multi-column index that starts with this column.  Is that not true?
0
slightwv (䄆 Netminder) Commented:
What indexes do you have?

I'm not sure how to write this using an analytics function but if necessary, I know an Expert with them.  I'll send him an email to see if he might have some magic.

Until he gets here see if this works for you:
/*
drop table tab1 purge;
create table tab1(emplid char(3), week number, datecreated date, dateexpired date);

insert into tab1 values('001',212,to_date('2018-03-19','YYYY-MM-DD'),null);
insert into tab1 values('001',212,to_date('2018-03-19','YYYY-MM-DD'),to_date('2018-03-19','YYYY-MM-DD'));
insert into tab1 values('001',212,to_date('2018-03-22','YYYY-MM-DD'),null);
insert into tab1 values('001',212,to_date('2018-03-23','YYYY-MM-DD'),to_date('2018-03-19','YYYY-MM-DD'));
commit;
*/


UPDATE tab1 t1
SET dateexpired = (
  SELECT max(datecreated)
  FROM tab1 t2
  WHERE t1.emplid = t2.emplid and t2.dateexpired is null and t1.datecreated<t2.datecreated )
where dateexpired is null;

select * from tab1;
rollback;

Open in new window

0
sdstuberCommented:
I don't think I would use analytics for this.

The query complexity goes up for no gain in functionality  and little to no gain efficiency.
Using analytics you can find the next value fairly easily...

SELECT emplid,
       week,
       datecreated,
       dateexpired,
       LEAD(CASE WHEN dateexpired IS NULL THEN datecreated END IGNORE NULLS)
           OVER(PARTITION BY emplid ORDER BY datecreated)
           nextdate
  FROM testtab1;

But, that query is not valid as an updatable target.
So you would have to use at least one subquery to create a valid update statement.

If you used the above in an update-only merge, that still requires a separate query but you can connect them by rowid.  So, in the absence of indexes, this might be more efficient.  It's probably worth trying, but I wouldn't get my hopes up for it.

MERGE INTO testtab1 t
     USING (SELECT ROWID   rid,
                   --emplid,
                   --week,
                   --datecreated,
                   --dateexpired,
                   LEAD(CASE WHEN dateexpired IS NULL THEN datecreated END IGNORE NULLS)
                       OVER(PARTITION BY emplid ORDER BY datecreated)
                       nextdate
              FROM testtab1) s
        ON (t.ROWID = s.rid)
WHEN MATCHED
THEN
    UPDATE SET t.dateexpired = s.nextdate
             WHERE t.dateexpired IS NULL AND s.nextdate IS NOT NULL;

This also has the advantage of not forcing a dummy update of NULL to NULL which could cause unnecessary trigger action.  If you wanted the triggers to fire anyway, then simply remove the "AND s.nextdate IS NOT NULL"


But, I would try slightwv's suggestion, maybe using MIN instead of MAX because you probably want the "next" entry if there are more than one following date.  It might not matter if the application controls the data so there will only ever be one "next" so MIN and MAX would be the same.


UPDATE testtab1 t1
   SET dateexpired =
           (SELECT MIN(datecreated)
              FROM testtab1 t2
             WHERE     t1.emplid = t2.emplid
                   AND t2.dateexpired IS NULL
                   AND t1.datecreated < t2.datecreated)
 WHERE dateexpired IS NULL;
0
sam2929Author Commented:
merge works great a small issue i am calling this merge via procedure and it is not liking it

Syntax check (1964: 2): Found:  'LEAD' Only FIRST_VALUE and LAST_VALUE are valid with IGNORE NULLS
0
sdstuberCommented:
the LEAD functionality has expanded over time, what version of oracle are your running?  

Full version  please,  like 12.2.0.1  or 11.2.0.4

Don't say "11" or "12c"  please give all 4 digits.
0
Mark GeerlingsDatabase AdministratorCommented:
"merge works great" but it gives you an error you can't get past?  That doesn't look like "works great" to me.

Can you tell us what indexes the table has?  Are you totally opposed to using the simple update with two sub-queries that I had suggested?  I suspect that would be fast if the table has an index with employeeid as the first column.  If there is no index like that now, I suggest creating one on employeeid and date_created, then trying the update again that I suggested.
0
sam2929Author Commented:
using 11.20.3.0

Thanks
0
sam2929Author Commented:
11.2.0.3.0
0
sam2929Author Commented:
sorry my bad

select * from V$VERSION

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
0
sdstuberCommented:
>>> merge works great a small issue i am calling this merge via procedure and it is not liking it

What is "IT" ?


>>> Syntax check (1964: 2): Found:  'LEAD' Only FIRST_VALUE and LAST_VALUE are valid with IGNORE NULLS

That's not an Oracle error, where did that come from?  Are you using some code formatting tool?


As of 11.2.0.1  LEAD supports the IGNORE NULLS clause, but not prior
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions086.htm#SQLRF00656

If you are in 12.anything (or even 11.2.0.3) you shouldn't have that problem.  Perhaps whatever is parsing your code is causing the issue, but not Oracle.
0
sam2929Author Commented:
Running code in toad .

when i run the sql no issues run good.

When i try to create a procedure with create procedure statement then i get that error.

Thanks
0
sdstuberCommented:
Do you get an ORA-xxxxxx error when you actually compile the procedure?  If so, what is it?
Or are you simply getting a warning from the Toad formatter?

Try not using Toad, maybe your version is out of date.

I just used Toad to create a stored procedure using the merge above, I did it on an 11.2.0.2 database.  It compiled and executed without error.  I didn't even get warnings on the syntax.

 I also tested it on 12.1.0.2 from within Toad.  

Here's my 12.1.0.2 Toad "Run as Script" (F5) output:

Time Start: 2018-03-29 08:51:59
SQL> select version from v$instance

VERSION          
-----------------
12.1.0.2.0       
1 row selected.
SQL> CREATE TABLE testtab1
(
    emplid         CHAR(3),
    week           NUMBER,
    datecreated    DATE,
    dateexpired    DATE
)
Table created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-19', 'YYYY-MM-DD'),
                NULL
            )
1 row created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-19', 'YYYY-MM-DD'),
                TO_DATE('2018-03-19', 'YYYY-MM-DD')
            )
1 row created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-22', 'YYYY-MM-DD'),
                NULL
            )
1 row created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-23', 'YYYY-MM-DD'),
                TO_DATE('2018-03-19', 'YYYY-MM-DD')
            )
1 row created.
SQL> COMMIT
Commit complete.
SQL> CREATE OR REPLACE PROCEDURE testmerge
AS
BEGIN
    MERGE INTO testtab1 t
         USING (SELECT ROWID
                           rid,
                       --emplid,
                       --week,
                       --datecreated,
                       --dateexpired,
                       LEAD(CASE WHEN dateexpired IS NULL THEN datecreated END IGNORE NULLS)
                           OVER(PARTITION BY emplid ORDER BY datecreated)
                           nextdate
                  FROM testtab1) s
            ON (t.ROWID = s.rid)
    WHEN MATCHED
    THEN
        UPDATE SET t.dateexpired = s.nextdate
                 WHERE t.dateexpired IS NULL AND s.nextdate IS NOT NULL;
END;
Procedure created.
SQL> SELECT * FROM testtab1

EMPLID       WEEK DATECREATED DATEEXPIRED
------ ---------- ----------- -----------
001           212 19-MAR-18              
001           212 19-MAR-18   19-MAR-18  
001           212 22-MAR-18              
001           212 23-MAR-18   19-MAR-18  

4 rows selected.
SQL> begin
    testmerge;
end;
 PL/SQL procedure successfully completed.
SQL> SELECT * FROM testtab1

EMPLID       WEEK DATECREATED DATEEXPIRED
------ ---------- ----------- -----------
001           212 19-MAR-18   22-MAR-18  
001           212 19-MAR-18   19-MAR-18  
001           212 22-MAR-18              
001           212 23-MAR-18   19-MAR-18  

4 rows selected.
SQL> drop table testtab1
Table dropped.
SQL> drop procedure testmerge
Procedure dropped.
Time End: 2018-03-29 08:52:00
Elapsed Time for Script Execution: 703 msecs

Open in new window


So, the merge does work, and it does compile and run in pl/sql too.  I don't know what else to tell you.  You must be doing something different on your end.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
And you are still convinced that the complexity of this merge statement will work better for you than a simple index, and an update with two sub-queries?  Have you actually tried that with an index on employeeid?
0
sdstuberCommented:
"complexity of this merge"  - it's funny how different people interpret complexity.

To me an update/merge with one subquery seems less complex than an update with two subqueries.
My merge syntax could be simplified by removing the commented columns, but I left them in to (hopefully) make it obvious that I wasn't making a signficant functional change to the query, simply commenting out the parts that aren't really used when updating.

But, if I were to pick an update over the merge, slightwv's seems best with only one subquery (again, I'd use MIN instead of MAX, but that's just me guessing at the desired results if there are more than one future row)

More important than complexity or performance though...
It looks to me like the update with 2 subqueries (post 42512355) produces wrong results because it updates an extra row it shouldn't have.
0
Mark GeerlingsDatabase AdministratorCommented:
"the update with 2 subqueries (post 42512355) produces wrong results because it updates an extra row it shouldn't have."
How were you able to determine that?  Which "extra row" got updated?
0
sdstuberCommented:
Time Start: 2018-03-29 14:22:05
SQL> SELECT version FROM v$instance

VERSION          
-----------------
12.1.0.2.0       
1 row selected.
SQL> CREATE TABLE testtab1
(
    emplid         CHAR(3),
    week           NUMBER,
    datecreated    DATE,
    dateexpired    DATE
)
Table created.
SQL> CREATE INDEX testtab1idx
    ON testtab1(emplid)
Index created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-19', 'YYYY-MM-DD'),
                NULL
            )
1 row created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-19', 'YYYY-MM-DD'),
                TO_DATE('2018-03-19', 'YYYY-MM-DD')
            )
1 row created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-22', 'YYYY-MM-DD'),
                NULL
            )
1 row created.
SQL> INSERT INTO testtab1
     VALUES (
                '001',
                212,
                TO_DATE('2018-03-23', 'YYYY-MM-DD'),
                TO_DATE('2018-03-19', 'YYYY-MM-DD')
            )
1 row created.
SQL> COMMIT
Commit complete.
SQL> SELECT * FROM testtab1

EMPLID       WEEK DATECREATED DATEEXPIRED
------ ---------- ----------- -----------
001           212 19-MAR-18              
001           212 19-MAR-18   19-MAR-18  
001           212 22-MAR-18              
001           212 23-MAR-18   19-MAR-18  

4 rows selected.
SQL> UPDATE testtab1 t1
   SET t1.dateexpired =
           (SELECT MIN(datecreated)
              FROM testtab1 t2
             WHERE t2.emplid = t1.emplid AND t2.datecreated > t1.datecreated)
 WHERE     t1.dateexpired IS NULL
       AND EXISTS
               (SELECT 1
                  FROM testtab1 t3
                 WHERE t3.emplid = t1.emplid AND t3.datecreated > t1.datecreated)
2 rows updated.
SQL> SELECT * FROM testtab1

EMPLID       WEEK DATECREATED DATEEXPIRED
------ ---------- ----------- -----------
001           212 19-MAR-18   22-MAR-18  
001           212 19-MAR-18   19-MAR-18  
001           212 22-MAR-18   23-MAR-18       <<<<<<<<<<<<< This row should not have been updated
001           212 23-MAR-18   19-MAR-18  

4 rows selected.
SQL> DROP TABLE testtab1
Table dropped.
Time End: 2018-03-29 14:22:05
Elapsed Time for Script Execution: 702 msecs

Open in new window

0
Mark GeerlingsDatabase AdministratorCommented:
That looks to me like a flaw in the sample data, rather than a flaw in the update logic.  The last record for an employee in a week shouldn't have the DATEEXPIRED already filled in, should it?   And even if it does, how do we know that an earlier record with a null DATEEXPIRED should not get updated?  I understood the requirement to be that any/all earlier records with a null DATEEXPIRED should get updated, if a later record exists for that employee/week combination.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.