Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate returns from list of Values (Oracle APEX )
Duplicate returns from list of Values [message #680018] Fri, 17 April 2020 14:42 Go to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
Good afternoon,

still learning how to work this.

i have an oracle apex database to control funds. I have added a list of values SQL below. When i select the list on my form, there are numerous duplicates that i would like to avoid. not sure how else to explain, please ask me what other information you need.

WITH BD as (select PR_NUMBER,
ACCOUNT,
AMOUNT,
T.total,
Notes,
PR.AMOUNT - T.total AS TOTAL_REMAINING
from PR_TRACKER PR, PR_TOTAL T
WHERE PR.PR_NUMBER = T.REQUISITION_NUMBER)
SELECT
REQUISITION_NUMBER ||' - '|| Cardholderid ||' - Total_Remaining $'|| TOTAL_REMAINING
FROM PURCHASES, BD
WHERE PURCHASES.REQUISITION_NUMBER = BD.PR_NUMBER(+)
ORDER BY 1
Re: Duplicate returns from list of Values [message #680019 is a reply to message #680018] Fri, 17 April 2020 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

BlackSwan wrote on Tue, 10 September 2019 21:40
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

So, Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
Indent the code, use code tags and align the columns in result.
Format your query, if you don't know how to do it, learn it using SQL Formatter.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Fri, 17 April 2020 15:13]

Report message to a moderator

Re: Duplicate returns from list of Values [message #680020 is a reply to message #680018] Fri, 17 April 2020 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
WillJ wrote on Fri, 17 April 2020 12:42
Good afternoon,

still learning how to work this.

i have an oracle apex database to control funds. I have added a list of values SQL below. When i select the list on my form, there are numerous duplicates
What criteria must be met for rows to be considered DUPLICATE?
Re: Duplicate returns from list of Values [message #680082 is a reply to message #680020] Tue, 21 April 2020 08:18 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
Blackswan,

my oracle apex version has this number APEX_LISTENER_VERSION 19.2.0.r1991647... not sure if that is what you need.

For my duplicate values, I don't want any in the PR numbers column.

How else can I help explain this for you?

Re: Duplicate returns from list of Values [message #680083 is a reply to message #680082] Tue, 21 April 2020 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
OK, there are multiple rows with same value in PR_NUMBER column.
How to decide which row is kept in display & which rows are removed?
Re: Duplicate returns from list of Values [message #680085 is a reply to message #680083] Tue, 21 April 2020 09:12 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
If we can only display one of each PR number?
Re: Duplicate returns from list of Values [message #680087 is a reply to message #680085] Tue, 21 April 2020 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
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: Duplicate returns from list of Values [message #680090 is a reply to message #680087] Tue, 21 April 2020 10:13 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
Thanks. let me try this again.

The code below is a List of Values within the shared components section to be returned on a form on a select list in oracle apex. The drop down on the form displays the PR_Number, Carholderid, and total remaining from the PR_Tracker table. the totals is a calculation from each entry on the Purchases table that matches the PR_Number to Requisition number.

the issue is when i have the form open and select the drop down, it is pulling from the purchases table and not the PR_Tracker table. causing the drop down to be to show all the inputs from the purchases table, instead of the single PR_Numbers.

So, basically the Purchases table will have all the purchases. the PR_Tracker Table will have just the PR_Numbers (and the other columns). i need the code below to just return one PR_Number that .

WITH BD as (select PR_NUMBER,
ACCOUNT,
AMOUNT,
T.total,
Notes,
PR.AMOUNT - T.total AS TOTAL_REMAINING
from PR_TRACKER PR, PR_TOTAL T
WHERE PR.PR_NUMBER = T.REQUISITION_NUMBER)
SELECT
REQUISITION_NUMBER ||' - '|| Cardholderid ||' - Total_Remaining $'|| TOTAL_REMAINING
FROM BD, Purchases
WHERE BD.PR_NUMBER(+) = PURCHASES.REQUISITION_NUMBER
ORDER BY 1
Re: Duplicate returns from list of Values [message #680092 is a reply to message #680090] Tue, 21 April 2020 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables.
We don't have your data.
Therefore we can NOT run, test, debug, or improve posted code.
You have refused to actually SHOW us what is expected & desired results.

PLEASE read & follow Posting Guidelines
Re: Duplicate returns from list of Values [message #680093 is a reply to message #680092] Tue, 21 April 2020 10:27 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
i am not sure how to upload everything. let me get back to this.
Re: Duplicate returns from list of Values [message #680094 is a reply to message #680087] Tue, 21 April 2020 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
When you have multiple instances of the same pr number do they all have the same value for total_remaining or different values?

Which should they have?

I suspect your calculation may be wrong, but since we know nothing about your data, tables or the relationships between them, that's just a guess.

You need to give a test case as Michel mentioned initially so we can see what you have and what you want.
Re: Duplicate returns from list of Values [message #680095 is a reply to message #680093] Tue, 21 April 2020 11:05 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
WillJ wrote on Tue, 21 April 2020 10:27
i am not sure how to upload everything. let me get back to this.
Just post the relevant CREATE TABLE statements to allow people to create your tables. And post some INSERT statements to populate those tables with representative data.

It's all in the OraFAQ Forum guide that has already been cited to you several times.
Re: Duplicate returns from list of Values [message #680101 is a reply to message #680095] Tue, 21 April 2020 11:55 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
i know and I am sorry i am still learning. i just was unsure how much was needed... now i still going to mess this up.

So my create statements are below. not sure if you need the trigger statement but it was on the Oracle Apex table. not sure how to create an insert statement since this done from the form. But i will try it about since it is the once thing i did not copy, HOPEFULLY it will be right. fingers crossed this is close to what you need.

This database as quite a few tables but the two that are important are below.

CREATE TABLE "PR_TRACKER"
( "PR_NUMBER" NUMBER,
"ACCOUNT" VARCHAR2(30),
"AMOUNT" NUMBER,
"TOTAL_REMAINING" NUMBER,
"TOTAL_PURCHASES" NUMBER,
"DATE_CREATED" DATE,
"LAST_NAME" VARCHAR2(25),
"NOTES" VARCHAR2(55),
"CLOSED" NUMBER,
CONSTRAINT "PR_TRACKER_PK" PRIMARY KEY ("PR_NUMBER")
USING INDEX ENABLE
)
/
INSERT INTO PR_TRACKER (
PR_NUMBER,
ACCOUNT,
AMOUNT,
DATE_CREATED,
LAST_NAME,
NOTES
)
Values (
'123456789',
'TEST123',
'$5,000', not sure if i need the "$" or ","
'4/21/2020',
'WillJ',
'this is a test insert 1 of 2'
);

INSERT INTO PR_TRACKER (
PR_NUMBER,
ACCOUNT,
AMOUNT,
DATE_CREATED,
LAST_NAME,
NOTES
)
Values (
'987456321',
'TEST321',
'$5,000', not sure if i need the "$" or ","
'4/21/2020',
'WillJ',
'this is a test insert 2 of 2'
);

CREATE OR REPLACE EDITIONABLE TRIGGER "PR_TRACKER_TRG"
BEFORE INSERT ON RECRET.PR_TRACKER
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.PR_NUMBER IS NULL THEN
SELECT PR_TRACKER_SEQ3.NEXTVAL INTO :NEW.PR_NUMBER FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "PR_TRACKER_TRG" ENABLE
/

CREATE TABLE "PURCHASES"
( "COMPANY" VARCHAR2(Cool,
"NCOIC" VARCHAR2(70),
"RECRUITER" VARCHAR2(70),
"VENDOR" VARCHAR2(60),
"QUANTITY" NUMBER,
"CREDIT" VARCHAR2(1),
"CREDIT_PRICE" NUMBER,
"TOTAL_PRICE" NUMBER,
"OBLIGATED" VARCHAR2(1),
"PAYMENT_RECEIVED" VARCHAR2(1),
"ITEMS_RECEIVED" VARCHAR2(1),
"AAR_RECEIVED" VARCHAR2(1),
"INVOICERECEIVED" VARCHAR2(1),
"APPROVALRECEIVED" VARCHAR2(1),
"CARDHOLDERID" VARCHAR2(70),
"PURCHASE_TYPE" VARCHAR2(100),
"PAYMENT_METHOD" VARCHAR2(25),
"STATEMENT_DATE" DATE,
"NOTES" VARCHAR2(400),
"CALENDAR" VARCHAR2(1),
"EVENT_START_DATE" DATE,
"EVENT_END_DATE" DATE,
"ATTACHMENT" BLOB,
"ID" NUMBER(4,0),
"FY" NUMBER,
"TOTAL_SPENT" NUMBER,
"ACCOUNT_TYPE" VARCHAR2(25),
"ATTACHMENT-2" BLOB,
"ATTACHMENT-3" BLOB,
"REQUISITION_NUMBER" NUMBER,
"ATTACHMENT-4" BLOB,
"EVENT_DESCRIPTION" VARCHAR2(255),
"MATCHED" VARCHAR2(3),
"PR_AMOUNT" NUMBER,
"DATE_REQUESTED" TIMESTAMP (6) WITH LOCAL TIME ZONE,
CONSTRAINT "PURCHASES_PK" PRIMARY KEY ("ID")
USING INDEX (CREATE UNIQUE INDEX "PURCHASES_CON" ON "PURCHASES" ("ID")
) ENABLE
)
/
CREATE OR REPLACE EDITIONABLE TRIGGER "PURCHASES_TRG"
BEFORE INSERT ON RECRET.PURCHASES
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
NULL;
END COLUMN_SEQUENCES;
END;

/
ALTER TRIGGER "PURCHASES_TRG" ENABLE
/

CREATE OR REPLACE EDITIONABLE TRIGGER "PURCHASES_TRG1"
BEFORE INSERT ON RECRET.PURCHASES
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.ID IS NULL THEN
SELECT PURCHASES_SEQ2.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "PURCHASES_TRG1" ENABLE
/

Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
)
Values (
'HQ',
'$1,200',
'WillJ',
'123456789'
Wink
Values (
'HQ',
'$1,000',
'WillJ',
'123456789'
Wink
Values (
'HQ',
'$500',
'WillJ',
'123456789'
Wink
Values (
'HQ',
'$500',
'WillJ',
'987456321'
Wink
Values (
'HQ',
'$2500',
'WillJ',
'987456321'
Wink
Values (
'HQ',
'$750',
'WillJ',
'987456321'
Wink
Re: Duplicate returns from list of Values [message #680102 is a reply to message #680101] Tue, 21 April 2020 11:56 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
i had all the spaces and formatting right, i thought. let me try to fix it. i tried the SQLFlow, but it failed to work right, so again something is wrong.


CREATE TABLE "PR_TRACKER"
( "PR_NUMBER" NUMBER,
"ACCOUNT" VARCHAR2(30),
"AMOUNT" NUMBER,
"TOTAL_REMAINING" NUMBER,
"TOTAL_PURCHASES" NUMBER,
"DATE_CREATED" DATE,
"LAST_NAME" VARCHAR2(25),
"NOTES" VARCHAR2(55),
"CLOSED" NUMBER,
CONSTRAINT "PR_TRACKER_PK" PRIMARY KEY ("PR_NUMBER")
USING INDEX ENABLE
)
/
INSERT INTO PR_TRACKER (
PR_NUMBER,
ACCOUNT,
AMOUNT,
DATE_CREATED,
LAST_NAME,
NOTES
)
Values (
'123456789',
'TEST123',
'$5,000', not sure if i need the "$" or ","
'4/21/2020',
'WillJ',
'this is a test insert 1 of 2'
);

INSERT INTO PR_TRACKER (
PR_NUMBER,
ACCOUNT,
AMOUNT,
DATE_CREATED,
LAST_NAME,
NOTES
)
Values (
'987456321',
'TEST321',
'$5,000', not sure if i need the "$" or ","
'4/21/2020',
'WillJ',
'this is a test insert 2 of 2'
);

CREATE OR REPLACE EDITIONABLE TRIGGER "PR_TRACKER_TRG"
BEFORE INSERT ON RECRET.PR_TRACKER
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.PR_NUMBER IS NULL THEN
SELECT PR_TRACKER_SEQ3.NEXTVAL INTO :NEW.PR_NUMBER FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "PR_TRACKER_TRG" ENABLE
/

CREATE TABLE "PURCHASES"
( "COMPANY" VARCHAR2(Cool,
"NCOIC" VARCHAR2(70),
"RECRUITER" VARCHAR2(70),
"VENDOR" VARCHAR2(60),
"QUANTITY" NUMBER,
"CREDIT" VARCHAR2(1),
"CREDIT_PRICE" NUMBER,
"TOTAL_PRICE" NUMBER,
"OBLIGATED" VARCHAR2(1),
"PAYMENT_RECEIVED" VARCHAR2(1),
"ITEMS_RECEIVED" VARCHAR2(1),
"AAR_RECEIVED" VARCHAR2(1),
"INVOICERECEIVED" VARCHAR2(1),
"APPROVALRECEIVED" VARCHAR2(1),
"CARDHOLDERID" VARCHAR2(70),
"PURCHASE_TYPE" VARCHAR2(100),
"PAYMENT_METHOD" VARCHAR2(25),
"STATEMENT_DATE" DATE,
"NOTES" VARCHAR2(400),
"CALENDAR" VARCHAR2(1),
"EVENT_START_DATE" DATE,
"EVENT_END_DATE" DATE,
"ATTACHMENT" BLOB,
"ID" NUMBER(4,0),
"FY" NUMBER,
"TOTAL_SPENT" NUMBER,
"ACCOUNT_TYPE" VARCHAR2(25),
"ATTACHMENT-2" BLOB,
"ATTACHMENT-3" BLOB,
"REQUISITION_NUMBER" NUMBER,
"ATTACHMENT-4" BLOB,
"EVENT_DESCRIPTION" VARCHAR2(255),
"MATCHED" VARCHAR2(3),
"PR_AMOUNT" NUMBER,
"DATE_REQUESTED" TIMESTAMP (6) WITH LOCAL TIME ZONE,
CONSTRAINT "PURCHASES_PK" PRIMARY KEY ("ID")
USING INDEX (CREATE UNIQUE INDEX "PURCHASES_CON" ON "PURCHASES" ("ID")
) ENABLE
)
/
CREATE OR REPLACE EDITIONABLE TRIGGER "PURCHASES_TRG"
BEFORE INSERT ON RECRET.PURCHASES
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
NULL;
END COLUMN_SEQUENCES;
END;

/
ALTER TRIGGER "PURCHASES_TRG" ENABLE
/

CREATE OR REPLACE EDITIONABLE TRIGGER "PURCHASES_TRG1"
BEFORE INSERT ON RECRET.PURCHASES
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.ID IS NULL THEN
SELECT PURCHASES_SEQ2.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "PURCHASES_TRG1" ENABLE
/

Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
)
Values (
'HQ',
'$1,200',
'WillJ',
'123456789'
);
Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
)
Values (
'HQ',
'$1,000',
'WillJ',
'123456789'
);
Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
)
Values (
'HQ',
'$500',
'WillJ',
'123456789'
);
Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
)
Values (
'HQ',
'$500',
'WillJ',
'987456321'
);
 Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
)
Values (
'HQ',
'$2500',
'WillJ',
'987456321'
);
Insert INTO Purchases (
Company,
TOTAL_PRICE,
CARDHOLDERID,
REQUISITION_NUMBER
) 
Values (
'HQ',
'$750',
'WillJ',
'987456321'
);

CM: fixed end tag - it's /code not \code

[Updated on: Tue, 21 April 2020 13:08] by Moderator

Report message to a moderator

Re: Duplicate returns from list of Values [message #680103 is a reply to message #680102] Tue, 21 April 2020 13:12 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Numbers shouldn't have $ or commas, or quotes for that matter.
So
'$5,000',
should be
5000,

So what's the logic, in English, not code, for working out total remaining?
Re: Duplicate returns from list of Values [message #680104 is a reply to message #680103] Tue, 21 April 2020 13:20 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
Cookiemonster,

So, that 5000 was a currency that's why i added it that way.

So each entry to the purchase table will have a cost (total_spent). which must have a Requisition number (which is a PR_NUMBER) assigned to it. i just need all cost per PR_Number (Requisition Number) added together. then subtracted from the Amount on the PR_number.

PR_Number amount =5000
Total_Spent (summed)3500
Total_remaining = 1500

make sense?
Re: Duplicate returns from list of Values [message #680108 is a reply to message #680104] Wed, 22 April 2020 02:53 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the pr_total table that's included in your original Select statement isn't involved?
Your original select didn't sum anything, which is why you were getting dupes. You probably want something like this:
SELECT pt.pr_number, pt.total_remaining - SUM(pur.total_spent)
FROM purchases pur JOIN pr_tracker ON pr.pr_number = pur.requisition_number
GROUP BY pt.pr_number, pt.total_remaining
Re: Duplicate returns from list of Values [message #680113 is a reply to message #680108] Wed, 22 April 2020 06:39 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
Cookiemonster


thanks for the response.

my PR_TOTAL is view. I also have another View that someone helped me with... i should have added this in the first place and looking at this might be better to use than the PR_TOTAL view.


Your code is returning a ORA-00904: "PR"."PR_NUMBER": invalid identifier error.

so looking at your code, i see the group by expression. i cant put it where it says "order by 1"

CREATE OR REPLACE FORCE VIEW "PR_TOTAL" ("REQUISITION_NUMBER", "TOTAL") AS 
  Select
 REQUISITION_NUMBER,
 SUM(TOTAL_PRICE) TOTAL_REMAINING
FROM
 PURCHASES
GROUP BY
 REQUISITION_NUMBER
CREATE OR REPLACE FORCE VIEW "VO_PR_BALANCE" ("PR_NUMBER", "ACCOUNT", "AMOUNT", "LAST_NAME", "DATE_CREATED", "Notes", "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, 
       Last_Name,
       Date_Created,
       Notes,
       tot.total_spent,
       pr.amount - tot.total_spent total_remaining 
from pr_tracker pr left join tot on pr.pr_number = tot.requisition_number
/

CM: fixed code tags

[Updated on: Wed, 22 April 2020 07:30] by Moderator

Report message to a moderator

Re: Duplicate returns from list of Values [message #680115 is a reply to message #680113] Wed, 22 April 2020 07:55 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I forgot to put the alias "pr" after pr_tracker:
SELECT pt.pr_number, pt.total_remaining - SUM(pur.total_spent)
FROM purchases pur JOIN pr_tracker ON pr.pr_number = pur.requisition_number
GROUP BY pt.pr_number, pt.total_remaining
Previously you said that total_spent should be summed but above you're summing total_price.
Which is the correct one?
Re: Duplicate returns from list of Values [message #680117 is a reply to message #680115] Wed, 22 April 2020 08:23 Go to previous messageGo to next message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
sorry. its total_price from the Purchases table.

I hate to be so bad at explaining what i need. the previous code returned the same error, i was able to figure out where to fix it. But, how do i add the notes and last name column to be displayed from the PR_Tracker table. When I try to to add it ",pt.notes" after the SUM(pur.total_price) i get the error ORA-00979: not a group by expression.

SELECT pt.pr_number, pt.amount - SUM(pur.total_Price)
FROM purchases pur JOIN pr_tracker pt ON pt.pr_number = pur.requisition_number
GROUP BY pt.pr_number, pt.amount
Re: Duplicate returns from list of Values [message #680124 is a reply to message #680117] Wed, 22 April 2020 10:26 Go to previous message
WillJ
Messages: 16
Registered: September 2019
Location: Will J
Junior Member
ALL

thank you for the help with this. I finally figured out what i needed to do. See below. until next time.

WITH BD as (select PR_NUMBER,
       ACCOUNT,
       AMOUNT,
       T.total,
       Notes,
       PR.AMOUNT - T.total AS TOTAL_REMAINING
  from PR_TRACKER PR, PR_TOTAL T
  WHERE PR.PR_NUMBER = T.REQUISITION_NUMBER)
SELECT
REQUISITION_NUMBER ||' - '|| Cardholderid ||'-'|| bd.Notes ||' - Total_Remaining $'|| TOTAL_REMAINING
FROM BD, Purchases
WHERE BD.PR_NUMBER(+) = PURCHASES.REQUISITION_NUMBER
Group by REQUISITION_NUMBER ||' - '|| Cardholderid ||'-'|| bd.Notes ||' - Total_Remaining $'|| TOTAL_REMAINING
Previous Topic: Emp Code Searching Issue
Next Topic: Need age difference between warning and critical
Goto Forum:
  


Current Time: Mon Sep 21 13:02:25 CDT 2020