Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

select / insert distinct record running from aqua data against oracle

Posted on 2006-11-07
4
Medium Priority
?
1,197 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 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

824 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