Home » SQL & PL/SQL » SQL & PL/SQL » insert the data based on prevoius columns
insert the data based on prevoius columns [message #184790] Fri, 28 July 2006 01:23 Go to next message
aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad
Member

hi all,

I have a table named m_budget_stock where i have following which i am send in a word document please keep you page orientation in landscape to see the page
i have given the problems in the attachment it self please check it.


thanks for help

aditya.
  • Attachment: a.doc
    (Size: 67.00KB, Downloaded 280 times)
Re: insert the data based on prevoius columns [message #184797 is a reply to message #184790] Fri, 28 July 2006 01:49 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Your question is making me bit confusion.
Quote:


The data should be like in the place of 01-MAR-06 it should be 01-APR-06



Quote:


company_code, bud_year, bud_version, grade_code, bud_month, mon_iss should remain same


both are contradicting. because i can see '01-MAR-06' only for bad_month fields. however i assumed bad_month should change.

insert into m_budget_stock ( 
 company_code,
 bud_year,
 bud_version,
 grade_code,
 bud_month ,
 op_balqty ,
 rec       ,
 mon_iss   ,
 clstk     )
 select 
  company_code,
  bud_year,
  bud_version,
  grade_code,
  add_months(bud_month,1) ,
  clstk
  null       ,
  mon_iss   ,
  null from m_budget_stock;


Quote:

Code tag edited

[Updated on: Fri, 28 July 2006 01:58] by Moderator

Report message to a moderator

Re: insert the data based on prevoius columns [message #184799 is a reply to message #184790] Fri, 28 July 2006 01:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


One option is :

At the end of each Month Shedule a job for insert script like ...


Insert into  m_budget_stock( company_code ,
                             bud_year ,
                             bud_version ,
                             grade_code ,
                             bud_month ,
                             op_balqty ,
                             rec       ,
                             mon_iss    ,
                             clstk      
                            )
                     select  company_code ,
                             bud_year ,      -->  Or TO_NUMBER(TO_CHAR((SYSDATE+1),'YYYY')))
                             bud_version ,
                             grade_code ,
                             add_months(bud_month,1) ,
                             clstk,
                             NULL       ,
                             mon_iss    ,
                             NULL
                     From   m_budget_stock
                     where trunc(bud_month) = trunc(sysdate,'MONTH');
                                   


Thumbs Up
Rajuvan.
Re: insert the data based on prevoius columns [message #184805 is a reply to message #184799] Fri, 28 July 2006 02:18 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Raju,
he did not specify any filterering condiftion. so i just leave to him. anyway it is obvious.

but in your bad_year part, i believe it should be like
TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYYY'))
instead of
TO_NUMBER(TO_CHAR((SYSDATE+1),'YYYY')))

because here we have to add the months instead of days.

Thanks,
Thangam
Re: insert the data based on prevoius columns [message #184808 is a reply to message #184805] Fri, 28 July 2006 02:29 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
oops.
actually it should be(i believe)
TO_NUMBER(TO_CHAR(ADD_MONTHS(bad_month,1),'YYYY'))
instead of
TO_NUMBER(TO_CHAR((SYSDATE+1),'YYYY')))

Re: insert the data based on prevoius columns [message #184811 is a reply to message #184808] Fri, 28 July 2006 02:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Even TO_NUMBER(TO_CHAR((SYSDATE+1),'YYYY')))
will work ...

Because I asked aditya_gangadharam to schedule the job to run this script AT THE END ON EACH MONTH .

Thumbs Up
Rajuvan
Re: insert the data based on prevoius columns [message #184819 is a reply to message #184811] Fri, 28 July 2006 03:07 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
it will scuite for process which is depends on sysdate. but here we are not very sure about it. that is why i added like this. in that case bad_year shoud be same as year part of the bad_month field.
further i'm wondering, why they are having two seperate column as bad_year and bad_month.
Previous Topic: help in converting date...
Next Topic: Update before trgger not getting invoked
Goto Forum:
  


Current Time: Fri Dec 02 22:54:02 CST 2016

Total time taken to generate the page: 0.19404 seconds