Solved

PLSQL: update with dense_rank()

Posted on 2014-10-30
24
307 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
[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
  • 8
  • 6
  • 6
  • +1
24 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414721
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 25

Expert Comment

by:chaau
ID: 40414722
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
ID: 40414726
i have this error
ORA-00933: SQL command not properly ended
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414727
sorry that isn't legitimate Oracle syntax
0
 

Author Comment

by:enrique_aeo
ID: 40414730
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 25

Expert Comment

by:chaau
ID: 40414731
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 420 total points
ID: 40414732
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
ID: 40414740
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414744
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 25

Expert Comment

by:chaau
ID: 40414747
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
ID: 40414754
both query with the MERGE
i have this error
ORA-00905: missing keyword
0
 

Author Comment

by:enrique_aeo
ID: 40414759
i have this version

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
0
 
LVL 25

Expert Comment

by:chaau
ID: 40414763
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414764
>>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
ID: 40414769
i have this error:
ORA-01732.png
0
 
LVL 25

Expert Comment

by:chaau
ID: 40414774
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
ID: 40414778
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
ID: 40414783
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414784
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 25

Assisted Solution

by:chaau
chaau earned 20 total points
ID: 40414787
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
ID: 40414791
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
ID: 40414808
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 420 total points
ID: 40414841
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
ID: 40414847
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 64
Oracle Errors 11 89
Cannot connect to Oracle database, python not recognizing cx_Oracle 2 71
oracle query 3 35
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

751 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