Solved

PLSQL: update with dense_rank()

Posted on 2014-10-30
24
268 Views
Last Modified: 2014-10-30
hi experts:

i need execute this query
update stage_bd_exports_paino
   set idventaproductocentro = (select dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro
                                  from stage_bd_exports_paino pvs
                                 order by pvs.codigocentro,
                                          pvs.codigoCliente,
                                          materialReal,
                                          pvs.organizacionventa)

but i have this error
ORA-00907: missing right parenthesis
0
Comment
Question by:enrique_aeo
  • 8
  • 6
  • 6
  • +1
24 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I don't think you should do it like that because you need a correlated query, and that won't be efficient with an analytic function like dense_rank().

I believe you will need to join a "derived table" something like this (you need to use the real primary key of that table)

UPDATE stage_bd_exports_paino SBEP
SET SBEP.idventaproductocentro = X.idventaproductocentro
INNER JOIN (
            SELECT
              ID
            , DENSE_RANK() OVER (ORDER BY pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) AS idventaproductocentro
            FROM stage_bd_exports_paino pvs
            ) X ON SBEP.ID = X.ID

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
In oracle you should do it differently. Try this:
update 
 (select idventaproductocentro, pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa, 
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new
                                  from stage_bd_exports_paino pvs
) t
SET t.idventaproductocentro = t.idventaproductocentro_new

Open in new window

0
 

Author Comment

by:enrique_aeo
Comment Utility
i have this error
ORA-00933: SQL command not properly ended
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
sorry that isn't legitimate Oracle syntax
0
 

Author Comment

by:enrique_aeo
Comment Utility
in this query
update
 (select idventaproductocentro, pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa,
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new
                                  from stage_bd_exports_paino pvs
) t
SET t.idventaproductocentro = t.idventaproductocentro_new

ORA-01732: data manipulation operation not legal on this view
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
You need to include more columns to the subquery, specifically the columns that are included in the primary key. Can you show us the table structure?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 420 total points
Comment Utility
When I see updates like this, I instantly think MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

As Paul pointed out, you need some 'key' on which to update so there is some where clause.

If you could provide sample data and expected results we can provide better examples.
0
 

Author Comment

by:enrique_aeo
Comment Utility
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
sorry, my fault entirely. Oracle doesn't support direct joins with an update.

The following is untested, and I have assumed ID is the primary key:

MERGE
INTO    stage_bd_exports_paino SBEP
USING   (
            SELECT
              ID
            , DENSE_RANK() OVER (ORDER BY pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) AS idventaproductocentro
            FROM stage_bd_exports_paino pvs
        ) x
ON      (SBEP.ID = X.ID)
WHEN MATCHED THEN
UPDATE
SET SBEP.idventaproductocentro = X.idventaproductocentro
;

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
The same query with the MERGE:
 MERGE INTO stage_bd_exports_paino
  USING
  (
      select idventaproductocentro, pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa,
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new,
ROWID r
from stage_bd_exports_paino pvs )
  ON (ROWID = r)
  WHEN MATCHED THEN UPDATE
  SET idventaproductocentro = idventaproductocentro_new;

Open in new window

0
 

Author Comment

by:enrique_aeo
Comment Utility
both query with the MERGE
i have this error
ORA-00905: missing keyword
0
 

Author Comment

by:enrique_aeo
Comment Utility
i have this version

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

Expert Comment

by:chaau
Comment Utility
OK, as you do not have the primary key the only query that would work will be:
update 
 (select rowid, 
IDVENTAPRODUCTOCENTRO ,
ORGANIZACIONVENTA     ,
CODIGOCLIENTE         ,
SPOT_NSPOT            ,
CODIGOCENTRO          ,
DIRECCIONZONAVENTA    ,
MATERIALREAL          ,
IDANIOEJERCICIO       ,
IDVENTAMES            ,
IDMES                 ,
MILLARES              ,
COSTOFIJO             ,
RESINA                ,
PRECIO                ,
TOTAL                 ,
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new
                                  from stage_bd_exports_paino pvs
) t
SET t.idventaproductocentro = t.idventaproductocentro_new

Open in new window

The idea is to include all columns and the ROWID to allow Oracle to uniquely identify the record that needs to be updated
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

Going from very old memory but I think that leaves out MERGE.  Back in 9i I remember you had to have both clauses "WHEN MATCHED" and WHEN NOT MATCHED".  One does an update, the other an insert.  I don't remember seeing a trick to get around the INSERT side.
0
 

Author Comment

by:enrique_aeo
Comment Utility
i have this error:
ORA-01732.png
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
There is no way but to create a primary key for your table. Can you tell us the reason why your table is without the primary key?
0
 

Author Comment

by:enrique_aeo
Comment Utility
the table has more than 5,000 records, tell me how I can create a primary key
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 60 total points
Comment Utility
ORA-01732:      data manipulation operation not legal on this view
Cause:      An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.
Action:      UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

We need to use the table(s) not a view
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I still don't know what you are trying to do but your original update, just lose the ORDER BY on the query and it should run.

Not sure if you'll get the results you want, but it should run.

update stage_bd_exports_paino
   set idventaproductocentro = (
	select dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro
                                  from stage_bd_exports_paino pvs
	) 
/

Open in new window

0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 20 total points
Comment Utility
The primary key is what uniquely identifies each of the records in your table. It can consist of a single column (i.e. Order ID) or be a composite key (i.e. Company+OrderID - for the situations where each of the companies have their own order numbering system). Only you should know what these columns are. If there is no such column(s) you can create one (like an ID column proposed by Paul) that will be internal to this table and be populated by a value from a sequence
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Oh!

IDventaproductocentro

Looks like you are trying to add a primary key to this table

If that is true then please use ROW_NUMBER() not DENSE_RANK()
0
 

Author Comment

by:enrique_aeo
Comment Utility
i need dense_rank because should be grouped with the same number every 12 records

i create primary key
PK_MITABLA primary key (CODIGOCENTRO, CODIGOCLIENTE, MATERIALREAL, ORGANIZACIONVENTA, IDMES)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 420 total points
Comment Utility
Try this:
merge into stage_bd_exports_paino a
using (
	select
		CODIGOCENTRO, CODIGOCLIENTE, MATERIALREAL, ORGANIZACIONVENTA, IDMES,
		dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro
        from stage_bd_exports_paino pvs
) mydata
on (
	a.CODIGOCENTRO = mydata.CODIGOCENTRO and
	a.CODIGOCLIENTE = mydata.CODIGOCLIENTE and
	a.MATERIALREAL = mydata.MATERIALREAL and
	a.ORGANIZACIONVENTA = mydata.ORGANIZACIONVENTA and
	a.IDMES = mydata.IDMES
   )
when matched then update
	set idventaproductocentro = mydata.idventaproductocentro
when not matched then insert
	(idventaproductocentro ) values ( null )
/

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
yes... I had second thoughts... lack of faith etc.
If it were me I might do something like that, but then I probably wouldn't be in this position
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two statements 6 50
Pl/SQL Query 31 61
oracle report printing 2 pages in one page 2 35
Dataware house query tuning 9 29
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

11 Experts available now in Live!

Get 1:1 Help Now