Home » SQL & PL/SQL » SQL & PL/SQL » Suggestions and work around required urgently (Oracle 9i)
Suggestions and work around required urgently [message #325996] Mon, 09 June 2008 20:54 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
HI FRIENDS,

I need to insert a set of values based on the following select statement into another table. The code for the select statement is as follows:
SELECT
distinct 
--id_rev_contract_id_s.NEXTVAL, 
a.cntrct_nm 
, a.cntrct_element 
, c.licensee_id 
, d.rev_type_id 
, e.tech_type_id 
, f.org_id 
, b.ORG_CNTRCT_REV_PCT 
, a.cust_nm 
, a.rev_type_desc 
, a.tech_type_desc 
, a.org_nm 
, b.contract_sign_date 
, b.contract_effective_date 
, b.contract_end_date 
FROM id_rev_det_temp_unique060908 a, id_rev_detail_temp b, id_rev_licensees c,id_rev_types d, id_rev_tech_types e,id_rev_organizations f 
WHERE 
a.org_nm = b.org_nm 
AND a.cntrct_element = b.cntrct_element 
AND a.cntrct_nm = b.cntrct_nm 
AND a.rev_type_desc = b.rev_type_desc 
AND a.tech_type_desc = b.tech_type_desc 
AND a.cust_nm = b.cust_nm 
AND b.CUST_NM= c.licensee_name 
AND b.REV_TYPE_DESC=d.rev_type 
AND b.TECH_TYPE_DESC= e.tech_type 
AND b.ORG_ID=f.org_id;



The problem here is if I don't use the DISTINCT clause in the above query the I would get 41 rows retrieved and if I use the DISTINCT clause I would get 11 rows retrieved.

But at the same time I wan't to insert the sequence values into the table by using the following line in the above code
id_rev_contract_id_s.NEXTVAL
.


But we know DISTINCT and SEQUENCE does not go hand in hand with each other.

So what shall I do to solve the above problem?

My idea is to create a temp table based on the above SELECT query excluding the sequence number and inserting the values that are in temp table along with sequence into our desired table. But I don't know how it works.

So I request you to go through the problem and get back to me with the possible solution at the earliest.

Thanks and regards,
Vamsi K Gummadi.


[Mod-edit: Frank removed unrelated poll. Polls are NOT meant to let your question stand out amongst the others.]

[Updated on: Mon, 09 June 2008 23:27] by Moderator

Report message to a moderator

Re: Suggestions and work around required urgently [message #325999 is a reply to message #325996] Mon, 09 June 2008 21:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Suggestions and work around required urgently
Please explain in detail, why it is urgent FOR ME to solve this problem for you now?

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting Guidelines at stated in URL above

Please keep line length <= 80 to read with horizontal scroll bar
Re: Suggestions and work around required urgently [message #326001 is a reply to message #325999] Mon, 09 June 2008 21:21 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi,
I am stuck up with this problem and hence I am looking out for a solution and hence mentioned it as urgent in the title tag so that some one in this form could give me the reply at the earliest and hence I can proceed with the remaining work.

Do you have any issues?

Why do you bother of the other things rather than concentrating on the actual problem? Last time also you have objected me in the same way that my ip address is in USA and why I haven't updated my profile. See brother as per my understanding the broad moto of the forum is to help the people like us who are in the start of career and to encourage them. If you keep on questioning or if your acts are discouraging then no one would look into the forums any more.

I am sorry if I have hurt you in any manner but the thing is I want to make a point and I made it out.

Thanks and regards,
Vamsi K Gummadi.

[Updated on: Mon, 09 June 2008 21:23]

Report message to a moderator

Re: Suggestions and work around required urgently [message #326005 is a reply to message #326001] Mon, 09 June 2008 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why do you expect to be treated with respect, when you do not respect the Posting Guidelines of this forum?

>Why do you bother of the other things rather than concentrating on the actual problem?
Why do you ignore posting guidelines which make it EASY for others to provide YOU the answer you are desperate for?

You did NOT provide DDL to make your table(s).
You did NOT provide DML to populate table with test data.
You did NOT provide sample output or example of desired results.
I have NO idea what you expect as the desired output.

NOBODY owes you any response; timely or otherwise.

Unless & until you following Posting Guideline, You're On Your Own (YOYO)!

The Poll needs another selection/choice
5) Poor/non-conforming to Posting Guidelines.

[Updated on: Mon, 09 June 2008 22:42] by Moderator

Report message to a moderator

Re: Suggestions and work around required urgently [message #326136 is a reply to message #325996] Tue, 10 June 2008 05:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should point you in the right direction.
SQL> create sequence test_seq;

Sequence created.

SQL> 
SQL> with src as (select 'a' col_1 from dual union all
  2               select 'b' col_1 from dual union all
  3               select 'a' col_1 from dual union all
  4               select 'd' col_1 from dual)
  5  select test_seq.nextval
  6        ,subq.*
  7  from   (select distinct col_1
  8          from   src) subq;

   NEXTVAL C
---------- -
         1 d
         2 a
         3 b
Re: Suggestions and work around required urgently [message #326839 is a reply to message #326136] Thu, 12 June 2008 16:30 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi,
Thank you for your suggestions. I worked as per you direction and also based on suggestions of some other experienced guys like you. I am now able to resolve my problem. Thank you once again for your suggestions.
Vamsi K Gummadi.
Re: Suggestions and work around required urgently [message #326841 is a reply to message #326839] Thu, 12 June 2008 16:40 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Please also note, that using 'urgent' in you posts is frowned upon and generally, intensely disliked by most forum members.
Re: Suggestions and work around required urgently [message #326854 is a reply to message #326841] Thu, 12 June 2008 20:15 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Sure will keep it in mind.
Thank you,
Vamsi K Gummadi
Re: Suggestions and work around required urgently [message #326855 is a reply to message #326854] Thu, 12 June 2008 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
vamsikgummadi wrote on Thu, 12 June 2008 18:15
Sure will keep it in mind.
Thank you,
Vamsi K Gummadi


If you had previously taken my advice

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting Guidelines at stated in URL above

you would have known that your subject title is expressly "prohibited"/discouraged!

"Choose a topic title that makes sense. A good title might be "ORA-1555 during pl/sql loop"
A less fortunate choice might be "URGNT PLZ!!!!!!!!!!!!"

Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)"
suggestions required please help me in solving this problem. [message #326856 is a reply to message #326005] Thu, 12 June 2008 20:43 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Anacedent,
Please find the inline comments.
Quote:
Why do you expect to be treated with respect, when you do not respect the Posting Guidelines of this forum?


I am not expecting any kind of respect from any of the members in this forum. The only thing I am expecting is sugestions and guidance from some experienced guys in this forum. There is a difference between respect and treatment with consideration. Remember even to spell a word we start from learning ABC... Initially I agree that I am not good at posting but in the recent times I have improved a lot while posting questions in this forum. Change doesn't comes within one day, it take's some time.

Quote:
Why do you ignore posting guidelines which make it EASY for others to provide YOU the answer you are desperate for?

It is not that we intentionally do not follow posting guide lines but this is the part we over look because of the eagerness to get answers and suggestions from the experienced guys like you. I agree that I made a mistake at this point.

Quote:
You did NOT provide DDL to make your table(s).
You did NOT provide DML to populate table with test data.
You did NOT provide sample output or example of desired results.
I have NO idea what you expect as the desired output.

See when we work at a place we can't get the exact replica of the data and it will be difficult to reproduce every thing and ask you people for suggestions. Some times it may also be due to the fact that people like us don't know what you would exactly need to answer our questions. If you think you need the DDL, DML or any thing to help people like us and if you ask us to provide
you with them we are always ready to do that.



Quote:
NOBODY owes you any response; timely or otherwise.

Yes, I completely agree that no one owes time to solve my problem. But there are some nice people like Michel, Pablolee, Barbara and some time even you and a lot of guys who help us in this forum which makes us attracted to use this forum. I have made a request not a demand. There is a difference between request and demand.
Quote:
Unless & until you following Posting Guideline, You're On Your Own (YOYO)!

Yes! I too beleive MAN IS THE MAKER OF HIS OWN DESTINY.
Quote:
The Poll needs another selection/choice
5) Poor/non-conforming to Posting Guidelines.

Thank you for reminding me of this guide line. Shall follow it from the next time I use the poll.

Thanks,
Vamsi K Gummadi

[Updated on: Thu, 12 June 2008 20:49]

Report message to a moderator

Previous Topic: How can I solve SP2-0024: Nothing to change.
Next Topic: Separating items and then ordering
Goto Forum:
  


Current Time: Fri Dec 09 15:56:01 CST 2016

Total time taken to generate the page: 0.42990 seconds