Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sammy.

Asked: February 25, 2017 - 8:28 am UTC

Last updated: February 25, 2017 - 9:48 am UTC

Version: Oralce Sql Developer Version 4.2.0.16.260

Viewed 1000+ times

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

Comments

Sammy John, March 19, 2017 - 1:11 pm UTC

thank you it was very helpful