Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ora-01407 cannot update to null

Vemula MuniAug 26 2019 — edited Sep 20 2019

Source table Data : ps_cts_iqn_empl_wo

EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID
5707161245653011-Aug-1931-Aug-19101545621
5707161245253011-Aug-1931-Aug-19141545621
5707171245121010-AUG-1913-AUG-19151843221
5707171245051015-JUL-1928-JUL-19151843221
5707181245151015-Aug-1928-Aug-19151843221

Target Table :

EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id
57071653014-Aug-19
57071653013-Aug-19
57071751014-Aug-19

Required Output :

case 1 :

----------

When employee id and project id matched and  report_due_date falls between start_dt and end_dt. and more than one row present for this combination then i need to update max(work_order_id) row values in target.

Case 2 :

-------

When employee id and project id matched and  report_due_date not falls between start_dt and end_dt. i need to fetch a maximum work_order_id row and Work_order_Id end_dt should be less than report_due_date.

EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE            Vendor_id
57071653014-Aug-191245631-Aug-19101545621
57071653013-Aug-191245631-Aug-19101545621
57071751014-Aug-191245113-Aug-19151843221

CREATE TABLE ps_cts_iqn_empl_wo (EMPLID VARCHAR2(11) NOT NULL,
   WORK_ORDER_ID VARCHAR2(15) NOT NULL,
   PROJECT_ID VARCHAR2(15) NOT NULL,
   START_DT DATE,
   END_DT DATE,
   STANDARD_RATE DECIMAL(15, 2)  NULL,
   CTS_WO_OT_RATE DECIMAL(15, 2)  NULL,
   VENDOR_ID VARCHAR2(10)  NULL) ;
   
   
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');   
  Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12451','510',to_date('10-AUG-2019','DD-MON-YYYY'),to_date('13-AUG-2019','DD-MON-YYYY'),15,18,'43221');  
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12450','510',to_date('15-JUL-2019','DD-MON-YYYY'),to_date('28-JUL-2019','DD-MON-YYYY'),15,18,'43221');  
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');  
   
   
CREATE TABLE PS_CT_IQN_ACC_STG (EMPLID VARCHAR2(11) NOT NULL,
   PROJECT_ID VARCHAR2(15) NOT NULL,
   REPORT_DUE_DATE DATE,
   WORK_ORDER_ID VARCHAR2(15)  NULL,
   END_DT DATE,
   STANDARD_RATE DECIMAL(15, 2)  NULL,
   CTS_WO_OT_RATE DECIMAL(15, 2)  NULL,
   VENDOR_ID VARCHAR2(10)  NULL) 
Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('13-AUG-2019','DD-MON-YYYY'),null,null,null,null,null); 
Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);

Query

UPDATE PS_CT_IQN_ACC_STG stg

   SET (Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1

where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLID

   AND WO.project_id  = STG.project_id  and stg.report_due_date between WO.start_dt and WO.end_dt  ))

Message was edited by: Vemula Muni
This post has been answered by Frank Kulash on Aug 26 2019
Jump to Answer

Comments

645864
As an additional note, the MySQL server was installed at:
/opt/mysql/mysql
645864
There is an old version of MySQL installed on the machine in addtion to /opt/mysql/mysql. Can this be the problem?
Hi,

first of all please mind that PHP 5.2 is out of support by the PHP developers on php.net. To receive bug fixes petc. you should use PHP 5.3.

Secondly it is worth noticing that there are three function libraries (extensions) which allow accessing the MySQL database. One is the classic "mysql" extension, one is called mysqli, with "i" as in improved, and a driver for the PDO database abstraction - PDO_mysql. You are only activating one of these, in most situations you want all three of them gor maximum application compatibility.

Now with PHP 5.3 PHP bundles an implementation complete of the MySQL Client functionality. So linking against a local installation is not needed. This implementation is called mysqlnd - MySQL native driver.

To build w/ mysqlnd and all three function libraries use a configure line like this:

./configure with-apxs2=/usr/local/apache2/bin/apxs with-mysql=mysqlnd with-mysqli=mysqlnd with-pdo-mysql=mysqlnd

If you have a strong reason for 5.2 or using libmysql I'd need more information, like which version of mysql was installed. Was it self-compiled or binaries from mysql.com etc. But I hope the above is fine.

johannes
1 person found this helpful
645864
Thank you for your time Johannes.

I put PHP 5.3 on the machine, and tried the configure line you described:
./configure with-apxs2=/usr/local/apache22/bin/apxs with-mysql=mysqlnd with-mysqli=mysqlnd with-pdo-mysql=mysqlnd

I still get the same type of error:

Undefined first referenced
symbol in file
mysql_list_dbs ext/mysql/.libs/php_mysql.o
mysql_fetch_row ext/mysql/.libs/php_mysql.o
mysql_fetch_field ext/mysql/.libs/php_mysql.o
[...many more "undefined symbol"s for mysql]

ld: fatal: Symbol referencing errors. No output written to sapi/cli/php
collect2: ld returned 1 exit status
*** Error code 1
make: Fatal error: Command failed for target `sapi/cli/php'


What does "undefined symbol" mean? Any ideas? Thank you.
Did you run "make clean" in between? This looks like some results from the previous build attempt are still in the tree.
1 - 5

Post Details

Added on Aug 26 2019
#sql-pl-sql
9 comments
1,589 views