Home » SQL & PL/SQL » SQL & PL/SQL » creating a temporary table (oracle 10g )
creating a temporary table [message #594564] Fri, 30 August 2013 03:02 Go to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

hi,

i have a clarification regarding creating a temporary table.

i have this query mentioned below
CREATE TABLE WEBPEN AS (SELECT PNSR_PPO_NO PPO,PNSR_FILE_NO,
       DECODE(F_GET_APPLN_NO(PNSR_PK),'1',PNSR_VOL_NO,F_GET_APPLN_NO(PNSR_PK)) APPLN_NO,
       PNSR_FULL_NAME NAME, 
       TO_CHAR(PNSR_DOB,'DD/MM/YYYY') DOB,
       TO_CHAR(PNSR_DOR,'DD/MM/YYYY') DOR,
       F_GET_ADBK_NAME(PNSR_TO_PENSION) TREASURY,
       PNSR_SPOUSE_NAME SPOUSE,
       DCRGWHELD_AMT DCRGWHELD, 
       PNSR_COMUTE_AMT COMMUT,
       PNSR_PEN_AMT PENSION, 
       PNSR_DCRG_AMT DCRG, 
       PNSR_FP_AMT FP2, 
       PNSR_REDUCED_AFT_CMT PENAFCOMMU,
       PNSR_CMVL_AMT COMMUSUM, 
       PNSR_SUB_TREASURY STREASURY
FROM   M_PENSIONER A,
       M_PEN_DCRG_WITHHELD B 
WHERE  A.PNSR_PK = B.DCRGWHELD_PEN_PK(+)
AND    PNSR_PEN_RLZD ='Y')


This creates a table webpen with around 54107 rows. What i am want is every time run "select * from webpen" it should run the above query and give the result as per the values in main table M_PENSIONER ,M_PEN_DCRG_WITHHELD

I am not sure how to do this. What i want is it should truncate the existing values and insert the value by running the above mentioned query .

I would be grateful if i get some suggestion / help in this regard
Re: creating a temporary table [message #594566 is a reply to message #594564] Fri, 30 August 2013 03:05 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds like you really want a view instead.
Re: creating a temporary table [message #594575 is a reply to message #594566] Fri, 30 August 2013 03:29 Go to previous messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

may be. how do i create a view. is it the same as the above query as instead of create table <table_name> should i have to use create view <view_name> .
Re: creating a temporary table [message #594577 is a reply to message #594575] Fri, 30 August 2013 03:32 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Any reason why you don't want to look it up in the documentation?
Re: creating a temporary table [message #594582 is a reply to message #594566] Fri, 30 August 2013 07:43 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Fri, 30 August 2013 04:05
Sounds like you really want a view instead.


Or just a simple select.
Re: creating a temporary table [message #594604 is a reply to message #594582] Fri, 30 August 2013 11:38 Go to previous messageGo to next message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
CREATE view WEBPEN AS 
SELECT PNSR_PPO_NO PPO,PNSR_FILE_NO,
       DECODE(F_GET_APPLN_NO(PNSR_PK),'1',PNSR_VOL_NO,F_GET_APPLN_NO(PNSR_PK)) APPLN_NO,
       PNSR_FULL_NAME NAME, 
       TO_CHAR(PNSR_DOB,'DD/MM/YYYY') DOB,
       TO_CHAR(PNSR_DOR,'DD/MM/YYYY') DOR,
       F_GET_ADBK_NAME(PNSR_TO_PENSION) TREASURY,
       PNSR_SPOUSE_NAME SPOUSE,
       DCRGWHELD_AMT DCRGWHELD, 
       PNSR_COMUTE_AMT COMMUT,
       PNSR_PEN_AMT PENSION, 
       PNSR_DCRG_AMT DCRG, 
       PNSR_FP_AMT FP2, 
       PNSR_REDUCED_AFT_CMT PENAFCOMMU,
       PNSR_CMVL_AMT COMMUSUM, 
       PNSR_SUB_TREASURY STREASURY
FROM   M_PENSIONER A,
       M_PEN_DCRG_WITHHELD B 
WHERE  A.PNSR_PK = B.DCRGWHELD_PEN_PK(+)
AND    PNSR_PEN_RLZD ='Y';
Re: creating a temporary table [message #594616 is a reply to message #594604] Fri, 30 August 2013 21:37 Go to previous message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

thanks for all the help and suggestion, i could create view and solved my requirement
Previous Topic: query help (if, else)
Next Topic: Read csv File from Shared Location
Goto Forum:
  


Current Time: Sun Sep 21 10:05:35 CDT 2014

Total time taken to generate the page: 0.12305 seconds