Solved

select / insert distinct record running from aqua data against oracle

Posted on 2006-11-07
4
1,167 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
4 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 250 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:lappins
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 250 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now