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 |
|
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 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topic:
BlackSwan wrote on Tue, 10 September 2019 21:40Welcome 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 #680090 is a reply to message #680087] |
Tue, 21 April 2020 10:13 |
|
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 #680094 is a reply to message #680087] |
Tue, 21 April 2020 10:29 |
cookiemonster
Messages: 13925 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 #680101 is a reply to message #680095] |
Tue, 21 April 2020 11:55 |
|
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(,
"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'
Values (
'HQ',
'$1,000',
'WillJ',
'123456789'
Values (
'HQ',
'$500',
'WillJ',
'123456789'
Values (
'HQ',
'$500',
'WillJ',
'987456321'
Values (
'HQ',
'$2500',
'WillJ',
'987456321'
Values (
'HQ',
'$750',
'WillJ',
'987456321'
|
|
|
Re: Duplicate returns from list of Values [message #680102 is a reply to message #680101] |
Tue, 21 April 2020 11:56 |
|
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 #680108 is a reply to message #680104] |
Wed, 22 April 2020 02:53 |
cookiemonster
Messages: 13925 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 |
|
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 |
cookiemonster
Messages: 13925 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 #680124 is a reply to message #680117] |
Wed, 22 April 2020 10:26 |
|
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
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:10:06 CDT 2024
|