Home » SQL & PL/SQL » SQL & PL/SQL » creating a temp table...pls help
creating a temp table...pls help [message #193300] Fri, 15 September 2006 10:34 Go to next message
bnath001
Messages: 10
Registered: September 2006
Junior Member
New to oracle .
I want to group by records and insert them into a temp table in oracle.

this is the query so far I have written:

select deal from fas133_main_copy
where trade_date <= trunc(last_day(add_months(sysdate,-1)))
and maturity_date > trunc(last_day(add_months(sysdate,-1))) group by deal);

I get set of records that are grouped by "deal" column. I want to get more columns from this "fas133_main_copy" table. so the only I get read those columns is to put the output of the above sql statement into a temp table and join the temp table and "fas133_main_copy" table and read more columns from "fas133_main_copy" table. is that the righ approach?

thanks
nath
Re: creating a temp table...pls help [message #193304 is a reply to message #193300] Fri, 15 September 2006 10:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
>is that the righ approach?

Doesn't sound like it.

What are you trying to achieve ?

What is the reason you are not selecting the columns you want from fas133_main_copy directly?

like

select [columns you want]
from fas133_main_copy
where [where clause you want]
group by [grouping you want]

best to post table creation scripts and sample data inserts first, so we can see what you have got in the table.
Re: creating a temp table...pls help [message #193306 is a reply to message #193304] Fri, 15 September 2006 10:58 Go to previous messageGo to next message
bnath001
Messages: 10
Registered: September 2006
Junior Member
I could have got all the columns in one sql statement. but it didn't work.

if I do:
select deal, trade_date from fas133_main_copy
where trade_date <= trunc(last_day(add_months(sysdate,-1))) and maturity_date > trunc(last_day(add_months(sysdate,-1)))
group by deal

the error says "trade_date not a group by expression".
should I group by "trade_date" field also?

could you pls post the corrected script if you can?

thanks a lot
nath
Re: creating a temp table...pls help [message #193307 is a reply to message #193304] Fri, 15 September 2006 11:00 Go to previous messageGo to next message
bnath001
Messages: 10
Registered: September 2006
Junior Member
basically i have a data in a table that should be grouped by "Deal" field

In the select statement I need more than DEAL field. I need atlast 5 fields from that table. but should Group by only Deal field.

how can I do this in oracle

thanks
nath
Re: creating a temp table...pls help [message #193308 is a reply to message #193306] Fri, 15 September 2006 11:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Okay.

As you have seen
select deal, trade_date from fas133_main_copy 
where trade_date <= trunc(last_day(add_months(sysdate,-1))) and maturity_date > trunc(last_day(add_months(sysdate,-1))) 
group by deal


doesn't work. the Data is grouped by deal, so if there are multiple entries with the same "deal" then which "trade_date" should be returned?

So basicly you can also group by trade_date, too. Then if there are 2 deals with different trade dates the query will return 2 rows, if there are 2 deals with the same date the query will return one row.

You can also tell oracle to group by deal and return, for example, the maximum trade date in the group with

select deal, max(trade_date) from fas133_main_copy 
where trade_date <= trunc(last_day(add_months(sysdate,-1))) and maturity_date > trunc(last_day(add_months(sysdate,-1))) 
group by deal


(or mininumm trade date with min(), average with avg(), and so on)
Re: creating a temp table...pls help [message #193309 is a reply to message #193308] Fri, 15 September 2006 11:15 Go to previous message
bnath001
Messages: 10
Registered: September 2006
Junior Member
thanks for the response.

regards
nath
Previous Topic: to check the record before inserting
Next Topic: select statement help!
Goto Forum:
  


Current Time: Fri Dec 09 04:20:30 CST 2016

Total time taken to generate the page: 0.08984 seconds