| View not pulling with updated table information [message #677296] |
Tue, 10 September 2019 14:13  |
 |
WillJ
Messages: 16
Registered: September 2019
Location: Will J
|
Junior Member |
|
|
Good Day ALL
i am very new to writing code. i had some help with this so far. this may take some time for me to explain the right information.
okay, SO i have this 'View' code on the Oracle Apex SQL workshop that references a 2 tables to consolidate a total of money remaining on each input. the problem is the code does not pull new entries from the PR_Tracker table.
So the code below (if i understand correctly) is pulling the columns from the purchases table to calculate total Price per Requisition Number- and calculating the amount - total spent = total remaining and putting that information on the PR Tracker table where the PR number = Requisition number on the total_remaining column
that all works right, but when i add to the PR_Tracker table the code does not pull new data, just the same 4 lines that where there when i created it.
CREATE OR REPLACE FORCE VIEW "VO_PR_BALANCE" ("PR_NUMBER", "ACCOUNT", "AMOUNT", "TOTAL_SPENT", "TOTAL_REMAINING") AS
WITH TOT AS (Select
REQUISITION_NUMBER,
SUM(TOTAL_PRICE) TOTAL_SPENT
FROM
PURCHASES
Group by Requisition_number)
select PR_NUMBER,
ACCOUNT,
AMOUNT,
TOT.TOTAL_SPENT,
PR.AMOUNT - TOT.TOTAL_SPENT TOTAL_REMAINING
FROM PR_TRACKER PR, TOT
WHERE PR.PR_NUMBER = TOT.REQUISITION_NUMBER
/
|
|
|
|
|
|
|
|
| Re: View not pulling with updated table information [message #677301 is a reply to message #677296] |
Tue, 10 September 2019 14:56  |
|
WillJ
Messages: 16
Registered: September 2019
Location: Will J
|
Junior Member |
|
|
My Code was not formatted correctly, sorry.
CREATE
OR
replace FORCE editionable VIEW "VO_PR_BALANCE" ("PR_NUMBER", "ACCOUNT", "AMOUNT", "TOTAL_SPENT", "TOTAL_REMAINING") AS WITH tot AS
(
SELECT requisition_number,
SUM(total_price) total_spent
FROM purchases
GROUP BY requisition_number)
SELECT pr_number,
account,
amount,
tot.total_spent,
pr.amount - tot.total_spent total_remaining
FROM pr_tracker pr,
tot
WHERE pr.pr_number = tot.requisition_number
How to use {code} tags and make your code easier to read
[Updated on: Tue, 10 September 2019 15:03] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: View not pulling with updated table information [message #677307 is a reply to message #677296] |
Wed, 11 September 2019 03:00 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Just to be clear - views are just queries that are permanently stored in the DB.
SELECT * FROM <view>
will always return exactly the same data as running the query in the view directly.
So if you're not getting the response you want then either:
a) the query is wrong, as flyboy suggested
b) the data you expect it to pick up either doesn't exist or hasn't been committed by the session that inserted it.
|
|
|
|
|
|
| Re: View not pulling with updated table information [message #677313 is a reply to message #677312] |
Wed, 11 September 2019 07:22 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
In the view - In the place you normally put joins, the from clause:
CREATE
OR
replace FORCE editionable VIEW "VO_PR_BALANCE" ("PR_NUMBER", "ACCOUNT", "AMOUNT", "TOTAL_SPENT", "TOTAL_REMAINING") AS WITH tot AS
(
SELECT requisition_number,
SUM(total_price) total_spent
FROM purchases
GROUP BY requisition_number)
SELECT pr_number,
account,
amount,
tot.total_spent,
pr.amount - tot.total_spent total_remaining
from pr_tracker pr left join tot on pr.pr_number = tot.requisition_number
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
