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

/

]]>

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

]]>

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]]>

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;

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.]]>

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?]]>

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

thank you so much. i am sure i will be back for other issues since this is my very first application. ]]>