Home » SQL & PL/SQL » SQL & PL/SQL » View not pulling with updated table information (APEX Oracle -PL/SQL)
View not pulling with updated table information [message #677296] Tue, 10 September 2019 14:13 Go to next message
WillJ
Messages: 5
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 #677298 is a reply to message #677296] Tue, 10 September 2019 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: View not pulling with updated table information [message #677300 is a reply to message #677298] Tue, 10 September 2019 14:51 Go to previous messageGo to next message
WillJ
Messages: 5
Registered: September 2019
Location: Will J
Junior Member
Okay. i will try again.
Re: View not pulling with updated table information [message #677301 is a reply to message #677296] Tue, 10 September 2019 14:56 Go to previous messageGo to next message
WillJ
Messages: 5
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 
*BlackSwan added {code tags}
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 #677302 is a reply to message #677296] Wed, 11 September 2019 00:02 Go to previous messageGo to next message
flyboy
Messages: 1880
Registered: November 2006
Senior Member
Is there any row in PURCHASES table for those "new entries from the PR_Tracker table" yet?

As, in the posted code, tables are joined with inner join - it returns only those rows that satisfy the join condition.

Maybe all you need is its change to outer join.
...
from pr_tracker pr left join tot on pr.pr_number = tot.requisition_number;
Re: View not pulling with updated table information [message #677307 is a reply to message #677296] Wed, 11 September 2019 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 13689
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 #677312 is a reply to message #677302] Wed, 11 September 2019 07:19 Go to previous messageGo to next message
WillJ
Messages: 5
Registered: September 2019
Location: Will J
Junior Member
Thanks for the replies.

that is what is happening, the New PR_TRACKER entries do not load onto the Purchases table until i have an entry.

Flyboy,

where would i put that code for the Join? on the view? if so where?
Re: View not pulling with updated table information [message #677313 is a reply to message #677312] Wed, 11 September 2019 07:22 Go to previous messageGo to next message
cookiemonster
Messages: 13689
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 
Re: View not pulling with updated table information [message #677314 is a reply to message #677313] Wed, 11 September 2019 07:28 Go to previous message
WillJ
Messages: 5
Registered: September 2019
Location: Will J
Junior Member
Nice, i was trying that and it looks like the i had the ";" giving me an error.

thank you so much. i am sure i will be back for other issues since this is my very first application.
Previous Topic: How To Show Column Value separated with colon as well as another column vlaue
Next Topic: what is utc and gmt
Goto Forum:
  


Current Time: Mon Oct 21 19:59:29 CDT 2019