Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

select / insert distinct record running from aqua data against oracle

Posted on 2006-11-07
4
Medium Priority
?
1,192 Views
Last Modified: 2013-12-12
I am trying to write sql statement to select / insert a distinct value (metername) and other info from a table where pointname is not distinct.   metername = pointname

insert into METER(METERNAME,METERTYPE,DESCRIPTION,METERID,LANGCODE,HASLD)
select distinct POINTNAME,'GAUGE',DESCRIPTION,meterseq.nextval,'EN',0
from mydatabase

I'm using aqua data studio against a oracle 9i db

PLEASE HELP!!!
0
Comment
Question by:brainsprocket
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 750 total points
ID: 17896059
in this statement meterseq.nextval changes with every selected row, so POINTNAME is never distinct.

assuming POINTNAME and DESCRIPTION are distinct, you could try this:

insert into METER(METERNAME,METERTYPE,DESCRIPTION,METERID,LANGCODE,HASLD)
select POINTNAME,'GAUGE',DESCRIPTION,meterseq.nextval,'EN',0
from (
    select DISTINCT POINTNAME, DESCRIPTION
    from mydatabase);
 
0
 
LVL 7

Expert Comment

by:Stephen Lappin
ID: 17896532
I have not tested this, but try distinct on individual columns instread of the row:

insert into METER(METERNAME,METERTYPE,DESCRIPTION,METERID,LANGCODE,HASLD)
select (distinct POINTNAME),'GAUGE',(distinct DESCRIPTION),meterseq.nextval,'EN',0
from mydatabase
0
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 750 total points
ID: 17896846
Try this:

insert into METER(METERNAME,METERTYPE,DESCRIPTION,LANGCODE,HASLD,METERID)
  select a.*, meterseq.nextval
    from (
      select POINTNAME,'GAUGE',MAX(DESCRIPTION),'EN',0
        from mydatabase
        group by pointname
    ) a

You have to decide which description You want in case when several same pointname's are present - I've chosen last one alphabetically (this is the MAX function -if You want first just use MIN. If You need concatenation of all of them follow this link for details: http://asktom.oracle.com/pls/ask/f?p=4950:8:14499142677306489710::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402).
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17939593
lappins, the one which you gave is not working. It is giving missing expression error. May be that is the reason you didn't want to test it :)

I think the easiest way is to go as per jwahl.

Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question