OraFAQ Forum: SQL & PL/SQL » Months Calculation and update Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Months Calculation and update Statement (Oracle 11G, Windows )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Months Calculation and update Statement [message #686745]

Sat, 10 December 2022 02:14

hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN

Senior Member

Dear Respectful Experts,

We have a table having Column TOTAL_MONTHS e.g. if

As shown below table need to Update using Update Statement.

TOTAL_MONTHS = 15 then Update the Column named "Months<=12" = 12 and Update the Column named "Months>12" = 3

another example in below table is
TOTAL_MONTHS =8 then Update Column named "Months<=12" = 8 and Update Column named "Months>12" = 0

Title	        Amount	Total_months	Months<=12	Months>12
10101288	28000	15		
10101289	40000	13		
10101290	2000000	10		
10101291	50000	14		
10101239	6000	11		
10101240	50000	8		
10121003	690	12

Out Put Result as below

Title	        Amount	Total_months	Months<=12	Months>12
10101288	28000	15	         12	          3
10101289	40000	13	         12	          1
10101290	2000000	10	         10	          0
10101291	50000	14	         12	          2
10101239	6000	11	         11	          0
10101240	50000	8	          8	          0
10121003	690	12	         12	          0

CREATE TABLE "TEST3" 			
   (	TITLE VARCHAR2(100 BYTE), 		
	AMOUNT NUMBER, 		
	Total_Months NUMBER, 		
	Months<=12 NUMBER, 		
	Months>12 NUMBER		
   )

REM INSERTING into TEST3
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101288',28000,15,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101289',40000,13,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101290',2000000,10,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101291',50000,14,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101239',6000,11,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101240',50000,8,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10121003',690,12,null,null);

Somebody can help us please.

Thanks,

Regards,

Report message to a moderator

Re: Months Calculation and update Statement [message #686746 is a reply to message #686745]

Sat, 10 December 2022 03:08

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your problem in doing so?
It is basic computation!

Note that you have some problem in column names:

SQL> CREATE TABLE "TEST3"
  2     (       TITLE VARCHAR2(100 BYTE),
  3     AMOUNT NUMBER,
  4     Total_Months NUMBER,
  5     Months<=12 NUMBER,
  6     Months>12 NUMBER
  7     )
  8  /
        Months<=12 NUMBER,
              *
ERROR at line 5:
ORA-00902: invalid datatype

Report message to a moderator

Re: Months Calculation and update Statement [message #686747 is a reply to message #686746]

Sat, 10 December 2022 05:41

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Main issue here is storing redundant information. There is no need for columns "Months<=12" and "Months>12" since they are nothing but least("Total_Months",12) and greatest("Total_Months" - 12,0).

SY.

Report message to a moderator

Re: Months Calculation and update Statement [message #686751 is a reply to message #686746]

Sun, 11 December 2022 12:27

hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN

Senior Member

Dear Michel Cadot,
I am very sorry, I tried to make column labels understandable,
I created the script again, please help to get the result as i requested before in this Topic.

Thanks,

CREATE TABLE "TEST3" 
   (	"TITLE" VARCHAR2(100 BYTE), 
	"AMOUNT" NUMBER, 
	"Total_Months" NUMBER, 
	"Months_12" NUMBER, 
	"Months_REM" NUMBER
   )



REM INSERTING into TEST3
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10101288',28000,15,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10101289',40000,13,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10101290',2000000,10,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10101291',50000,14,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10101239',6000,11,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10101240',50000,8,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months_12","Months_REM") values ('10121003',690,12,null,null);

Report message to a moderator

Re: Months Calculation and update Statement [message #686752 is a reply to message #686747]

Sun, 11 December 2022 12:32

hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN

Senior Member

wooooow!!!!
Dear Solomon Yakobson,
do not have word to praise your knowledge and expertise, you are genius, Incredible help, It works fine,

Bundle of thanks.

Report message to a moderator

Re: Months Calculation and update Statement [message #686753 is a reply to message #686752]

Sun, 11 December 2022 13:02

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Isn't genius, without minimizing Solomon skills, a little big too much for such 101 computation and SQL?

Maybe you should think to start reading the list of SQL functions and their purpose (and maybe even the whole book) after more than 10 years working on them.

Report message to a moderator

Previous Topic:	String Extract using REGEXP_SUBSTR
Next Topic:	Parent/Child Table Design Help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 11:52:46 CDT 2024