Skip to Main Content
Forums

Data Science & Machine Learning

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Updating/deleting rows using ROracle does not complete

user231May 9 2017 — edited May 9 2017

I have successfully connected to an Oracle DB using ROracle and am able to run both select and inset queries on the data - e.g: dbGetQuery(conOut, "insert into TEST1 values(:1,:2,:3,:4,:5,:6,:7,:8)",df).

However when I attempt to either delete or update rows then R hangs - both of the following lines cause this issue:

dbGetQuery(conOut, "update TEST1 set RUN_STATUS = 'COMPLETE' where IX= '2'")

dbGetQuery(conOut, "delete from TEST1 where IX = '2'")

I definitely have write access to the TEST1 table - I can successfully insert rows with ROracle, and can run those update/delete statements successfully with Oracle SQL Developer.

Any idea how to get the update and delete working?
This post has been answered by Syedsalmancs110 on May 9 2017
Jump to Answer

Comments

fac586 
Nicholas_R wrote:
Hi, I need to set condition for the result of SQL select in APEX.
with report_3 as ( 
 -- First query 
 SELECT r3.codart, SUM(r3.valnet) AS Sum_v1
 FROM mgmv r3
 WHERE (r3.codcma IN ('X ','Y','Z', 'A', 'B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart NOT LIKE 'FOR%' AND r3.codart NOT LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')
 OR(r3.codcma IN ('A') AND r3.codcma NOT IN ('B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart LIKE 'FOR%' AND r3.codart LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')
 GROUP BY r3.codart
) 
 , report_4 as ( 
 -- Second query
 SELECT r4.codart, SUM(r4.qtamov) AS Sum_qt
 FROM mgmv r4
 WHERE (r4.codcma IN ('X ','Y','Z', 'A', 'B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart NOT LIKE 'FOR%' AND r4.codart NOT LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))
 OR(r4.codcma IN ('A') AND r4.codcma NOT IN ('B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart LIKE 'FOR%' AND r4.codart LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))
 GROUP BY r4.codart
)
SELECT DISTINCT
 r3.codart
 , Sum_qt
 , Sum_v1
 , TRUNC(case when Sum_qt <> 0 then Sum_v1/Sum_qt+0 end, 5) as Cs_md
 
FROM 
 report_3 r3 
 LEFT OUTER JOIN report_4 r4 ON r3.codart = r4.codart
I need to result FOR1 [...] and DEN1 [...] when they are in A but not in B. But I have the products, with other codes, in A e in B.

Unclear. Please expand and clarify the requirements.

Nicholas_R

with report_3 as (

-- First query

SELECT r3.codart, SUM(r3.valnet) AS Sum_v1

FROM mgmv r3

WHERE (r3.codcma IN ('X ','Y','Z', 'A', 'B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart NOT LIKE 'FOR%' AND r3.codart NOT LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

OR(r3.codcma IN ('A') AND r3.codcma NOT IN ('B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart LIKE 'FOR%' AND r3.codart LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

GROUP BY r3.codart

)

, report_4 as (

-- Second query

SELECT r4.codart, SUM(r4.qtamov) AS Sum_qt

FROM mgmv r4

WHERE (r4.codcma IN ('X ','Y','Z', 'A', 'B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart NOT LIKE 'FOR%' AND r4.codart NOT LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

OR(r4.codcma IN ('A') AND r4.codcma NOT IN ('B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart LIKE 'FOR%' AND r4.codart LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

GROUP BY r4.codart

)

SELECT DISTINCT

r3.codart

, Sum_qt

, Sum_v1

, TRUNC(case when Sum_qt <> 0 then Sum_v1/Sum_qt+0 end, 5) as Cs_md

FROM

report_3 r3

LEFT OUTER JOIN report_4 r4 ON r3.codart = r4.codart

Jorgelina1

Hi...

I do not think that what is clear, is are you expecting to get from your queries.

I think that a possible "resulset" from your 1st query, and 2nd query... and what would you like to achieve as the end result would be a lot better so we understand what the requirement is...

Regards.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 6 2017
Added on May 9 2017
#r-technologies
2 comments
3,656 views