You Asked
CREATE TABLE DB_FV_WG
(
STORE_ID NUMBER(3,0) Not Null,
FV_02 NUMBER,
Y_02 NUMBER,
YEAR VARCHAR(6),
Month_NO NUMBER(2,0),
L_Code NUMBER(2,0)
)
Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('111',null,null,2016,1,3);
Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('111',null,null,2016,1,4);
Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('121',null,null,2016,1,3);
Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('121',null,null,2016,1,4);
Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('131',null,null,2016,1,3);
Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('131',null,null,2016,1,4);
select * from DB_FV_WG
Create Table FV(Store_ID NUMBER(3,0) not null,MD NUMBER(5,2),TD NUMBER(5,2))
Insert into FV(Store_ID,MD, TD) Values('111','-15.93','23.69');
Insert into FV(Store_ID,MD, TD) Values('151','-10.12','53.19');
Insert into FV(Store_ID,MD, TD) Values('121','-52.20','256.62');
select * from FV
update DB_FV_WG t
set FV_02 =
nvl(
(select MD,
from FV
where store_id = t.store_id
),0),
Y_02 =
nvl(
(select YD,
from FV
where store_id = t.store_id
),0)
WHERE t.year = '2016' and t.Month_NO = 01 and t.L_Code = 3;
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
-- output required is Main table is DB_FV_WG and value to be updated to column FV_02 from table FV column MD and
Y_02 from column YD table FV,
-- condition case is if the store id is not found in table FV then in main table DB_FV_WG column FV_02, Y_02 must be set as
zero
-- if store id found the update FV_02,Y_02 as per the value in FV column MD, YD
Requesting for output :
1) using NVL(),
2) using Case Statement and Joins,
3) Using COALESCE()
and Connor said...
Thanks for the test case - makes our life easier.
You just have some errant commas (and a suspect column name typo)
SQL>
SQL> CREATE TABLE DB_FV_WG
2 (
3 STORE_ID NUMBER(3,0) Not Null,
4 FV_02 NUMBER,
5 Y_02 NUMBER,
6 YEAR VARCHAR(6),
7 Month_NO NUMBER(2,0),
8 L_Code NUMBER(2,0)
9 );
Table created.
SQL>
SQL>
SQL> Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('111',null,null,2016,1,3);
1 row created.
SQL> Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('111',null,null,2016,1,4);
1 row created.
SQL> Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('121',null,null,2016,1,3);
1 row created.
SQL> Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('121',null,null,2016,1,4);
1 row created.
SQL> Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('131',null,null,2016,1,3);
1 row created.
SQL> Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('131',null,null,2016,1,4);
1 row created.
SQL>
SQL> select * from DB_FV_WG;
STORE_ID FV_02 Y_02 YEAR MONTH_NO L_CODE
---------- ---------- ---------- ------ ---------- ----------
111 2016 1 3
111 2016 1 4
121 2016 1 3
121 2016 1 4
131 2016 1 3
131 2016 1 4
6 rows selected.
SQL>
SQL> Create Table FV(Store_ID NUMBER(3,0) not null,MD NUMBER(5,2),TD NUMBER(5,2));
Table created.
SQL>
SQL> Insert into FV(Store_ID,MD, TD) Values('111','-15.93','23.69');
1 row created.
SQL> Insert into FV(Store_ID,MD, TD) Values('151','-10.12','53.19');
1 row created.
SQL> Insert into FV(Store_ID,MD, TD) Values('121','-52.20','256.62');
1 row created.
SQL>
SQL> select * from FV;
STORE_ID MD TD
---------- ---------- ----------
111 -15.93 23.69
151 -10.12 53.19
121 -52.2 256.62
3 rows selected.
SQL>
SQL>
SQL> update DB_FV_WG t
2 set FV_02 =
3 nvl(
4 (select MD, -- comma
5 from FV
6 where store_id = t.store_id
7 ),0),
8 Y_02 =
9 nvl(
10 (select YD, -- comma and column name
11 from FV
12 where store_id = t.store_id
13 ),0)
14 WHERE t.year = '2016' and t.Month_NO = 01 and t.L_Code = 3;
from FV
*
ERROR at line 5:
ORA-00936: missing expression
SQL>
SQL>
SQL> update DB_FV_WG t
2 set FV_02 =
3 nvl(
4 (select MD
5 from FV
6 where store_id = t.store_id
7 ),0),
8 Y_02 =
9 nvl(
10 (select TD
11 from FV
12 where store_id = t.store_id
13 ),0)
14 WHERE t.year = '2016' and t.Month_NO = 01 and t.L_Code = 3;
3 rows updated.
SQL>
SQL>
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment