Home » SQL & PL/SQL » SQL & PL/SQL » to different column depending on a value
to different column depending on a value [message #395991] Fri, 03 April 2009 08:01 Go to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
hi,

i have a table

table month
(
seq_nr number,
jan number,
feb number,
mar number,
apr number,
may number,
jun number,
jul number,
aug number,
sep number,
oct number,
nov number,
dec number
);

I am retriving the sysdate and getting the month.
If the month is 2 i have to update the column feb,mar,apr..dec.
if the month is 4 i have to update the column apr,may,...dec.

Whether this is possible using a single SQL statement..?
Performance also should not be affected.

Re: to different column depending on a value [message #395993 is a reply to message #395991] Fri, 03 April 2009 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is possible.
Post a Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Note that MONTH is a reserved word.

Regards
Michel

[Updated on: Fri, 03 April 2009 08:04]

Report message to a moderator

Re: to different column depending on a value [message #396027 is a reply to message #395991] Fri, 03 April 2009 09:51 Go to previous messageGo to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
hi

i am using oracle 9i.

CREATE TABLE MONTH_ABC
( "SEQ_NR" NUMBER(11,0) NOT NULL ENABLE,
"JAN" NUMBER(11,0),
"FEB" NUMBER(11,0),
"MAR" NUMBER(11,0),
"APR" NUMBER(11,0),
"MAY" NUMBER(11,0),
"JUN" NUMBER(11,0),
"JUL" NUMBER(11,0),
"AUG" NUMBER(11,0),
"SEP" NUMBER(11,0),
"OCT" NUMBER(11,0),
"NOV" NUMBER(11,0),
"DEC" NUMBER(11,0),
CONSTRAINT "MONTH_ABC_PK" PRIMARY KEY ("SEQ_NR")
)

Insert into MONTH_ABC (SEQ_NR,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC) values (1,1,1,1,1,1,1,1,1,1,1,1,1);
Insert into MONTH_ABC (SEQ_NR,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC) values (2,2,2,2,2,2,2,2,2,2,2,2,2);
Insert into MONTH_ABC (SEQ_NR,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC) values (3,3,3,3,3,3,3,3,3,3,3,3,3);


i want to use an update statement.
consider i am running it now which would take the month from sysdate(04-April) and should update the table from column APR..DEC. with plus one



If i am ruuning the statement next month(05-May)
then it should update the table from column MAY..DEC
with plus one



  • Attachment: month.txt
    (Size: 0.65KB, Downloaded 101 times)
Re: to different column depending on a value [message #396029 is a reply to message #395991] Fri, 03 April 2009 09:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would take the approach of always updating all the columns, but using case to ensure that sometimes you just update the column to it's original value.

UPDATE table
SET    jan = <value> -- always update january
      ,feb = case when to_number(to_char(sysdate,'mm')) <=2
                  then <value> 
                  else  feb  end
      ,mar = case when to_number(to_char(sysdate,'mm')) <=3
                  then <value> 
                  else  mar  end
...
Re: to different column depending on a value [message #396039 is a reply to message #396029] Fri, 03 April 2009 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far as I understand OP post, it is the opposite: update the columns that are greater than the input month.

Regards
Michel
Re: to different column depending on a value [message #396045 is a reply to message #396029] Fri, 03 April 2009 11:10 Go to previous messageGo to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
Whether there is possibilities without using the case statement we can acheive this...?

because consider if we are having 1000 rows then 1000 rows * 12 columns =12000 check condition is done.These would degarde the performance.

Here are we are doing the conditions based on a row level.Can we do it on a statement level?an one time operation.?
Re: to different column depending on a value [message #396047 is a reply to message #396045] Fri, 03 April 2009 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
These would degarde the performance.

Not really but you can prove I am wrong with a benchmark.

Regards
Michel
Re: to different column depending on a value [message #396055 is a reply to message #395991] Fri, 03 April 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The solution would be MUCH easier & straight forward, if the tables were designed to conform to Third Normal Form.

The existing MONTH_ABC table is sub-optimal & the source of the challenge.
Re: to different column depending on a value [message #396142 is a reply to message #396055] Sat, 04 April 2009 05:23 Go to previous message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
The problem is we cant change the table....
Many application depends on that table.....
if we are changing the table then we have to touch the
application too..

But i agree with michel.....

using the case statement in update statement is working fine for me....

Thanks every one for their response....
Previous Topic: strange results set
Next Topic: want output in excel automatically
Goto Forum:
  


Current Time: Sun Dec 04 23:04:07 CST 2016

Total time taken to generate the page: 0.11925 seconds