SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert statement with select and order by clause

Eslam_ElbyalyJan 27 2020 — edited Jan 29 2020

Hi,

Oracle database 12c and 18c, Apex 18.2. ORDS 18.4, Win 7. A restaurant application.

- Payment Term is the plan the owner will pay the vendor. i.e 10% after 10 days of purchase date and 90% after 30 days of purchase date.

- PI_PAYMENT_SCHEDULE is the table which has the actual periods and amounts that should be paid.

- When creating a purchase invoice, a term is chosen then it's details should be inserted into pi_payment_schedule table.

- When paying an installment I should retrieve the one with the lowest date and it's amount.

- I can get the next due installment(the one with the lowest date) through Min(due_date) but if I can get it through the Min(PK) column which is assigned it's value through a database sequence, I think it would be better. So, figured out that I just should ensure that when inserting rows into pi_payment_schedule in order that the lowest days_to_collect is inserted first then the next and so on.

I used the following Insert statement to insert into pi_payment_schedule when creating a new invoice,

 INSERT INTO PI_PAYMENT_SCHEDULE
    ( DUE_DATE, DUE_AMOUNT, PI_ID
    )
  SELECT ( TO_DATE(:P78_DOCUMENT_DATE) + PTD.DAYS_TO_COLLECT ) DUE_DATE,
   TO_NUMBER((:P78_NET_AMOUNT) * PTD.PERCENTAGE / 100 ) DUE_AMOUNT,
    TO_NUMBER(:P78_ID)
  FROM PAYMENT_TERM_DTL PTD
  WHERE PTd.PAYMENT_TERM_ID = TO_NUMBER(:P78_PAYMENT_TERM_ID);

But it inserted rows randomly, i.e,

An invoice created on 28/01/20 with a payment term with 20% to be paid after 30 days, 30% after 20 and 50% after 50.

when I query the Min(PK) of pi_payment_schedule, it does not retrieve the record with the lowest due date.

But when I added Order by to the select statement above it inserted the records in the right order.

- My question is, is that guaranteed? I am worrying that adding Order by could work sometimes and does not in other times?

- I know I can use min(due_date) for the task but it does not have an index and the PK already has a unique index.

--------------------------------------------------------

-- DDL for Sequence PAYMENT_TERM_DTL_PK

--------------------------------------------------------

CREATE SEQUENCE "PAYMENT_TERM_DTL_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 466 CACHE 20 NOORDER NOCYCLE NOPARTITION ;

--------------------------------------------------------

-- DDL for Sequence PAYMENT_TERM_PK

--------------------------------------------------------

CREATE SEQUENCE "PAYMENT_TERM_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 303 CACHE 20 NOORDER NOCYCLE NOPARTITION ;

--------------------------------------------------------

-- DDL for Sequence PI_PAYMENT_SCHEDULE_PK

--------------------------------------------------------

CREATE SEQUENCE "PI_PAYMENT_SCHEDULE_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 807 CACHE 20 NOORDER NOCYCLE NOPARTITION ;

--------------------------------------------------------

-- DDL for Sequence PURCHASE_INVOICE_DTL_PK

--------------------------------------------------------

CREATE SEQUENCE "PURCHASE_INVOICE_DTL_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4221640 CACHE 20 NOORDER NOCYCLE NOPARTITION ;

--------------------------------------------------------

-- DDL for Sequence PURCHASE_INVOICE_PK

--------------------------------------------------------

CREATE SEQUENCE "PURCHASE_INVOICE_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 6169034 CACHE 20 NOORDER NOCYCLE NOPARTITION ;

--------------------------------------------------------

-- DDL for Table PI_PAYMENT_SCHEDULE

--------------------------------------------------------

CREATE TABLE "PI_PAYMENT_SCHEDULE" ("ID" NUMBER DEFAULT ON NULL "PI_PAYMENT_SCHEDULE_PK"."NEXTVAL", "DUE_DATE" DATE, "DUE_AMOUNT" NUMBER, "STATUS" NUMBER, "PI_ID" NUMBER) ;

COMMENT ON COLUMN "PI_PAYMENT_SCHEDULE"."STATUS" IS '1/2 TRUE/FALSE';

--------------------------------------------------------

-- DDL for Table PURCHASE_INVOICE_DTL

--------------------------------------------------------

CREATE TABLE "PURCHASE_INVOICE_DTL" ("ID" NUMBER DEFAULT ON NULL "PURCHASE_INVOICE_DTL_PK"."NEXTVAL", "ITEM_ID" NUMBER, "QTY" NUMBER, "PURCHASE_INVOICE_ID" NUMBER, "PRICE" NUMBER, "EXPIRY_DATE" DATE) ;

--------------------------------------------------------

-- DDL for Table PURCHASE_INVOICE_HDR

--------------------------------------------------------

CREATE TABLE "PURCHASE_INVOICE_HDR" ("ID" NUMBER DEFAULT ON NULL "PURCHASE_INVOICE_PK"."NEXTVAL", "BP_ID" NUMBER, "DOCUMENT_DATE" DATE DEFAULT SYSDATE, "PAYMENT_TERM_ID" NUMBER DEFAULT 0, "PAYMENT_COMPLETE" NUMBER, "NOTES" VARCHAR2(200), "DISCOUNT" NUMBER, "DISCOUNT_TYPE" NUMBER, "CREATED_ON" DATE DEFAULT SYSDATE, "CREATED_BY" NUMBER) ;

COMMENT ON COLUMN "PURCHASE_INVOICE_HDR"."PAYMENT_COMPLETE" IS 'YES/NO';

COMMENT ON COLUMN "PURCHASE_INVOICE_HDR"."DISCOUNT_TYPE" IS 'Currency or Percentage';

--------------------------------------------------------

-- DDL for Table PAYMENT_TERM

--------------------------------------------------------

CREATE TABLE "PAYMENT_TERM" ("ID" NUMBER DEFAULT ON NULL "PAYMENT_TERM_PK"."NEXTVAL", "NAME" VARCHAR2(90), "DESCRIPTION" VARCHAR2(100)) ;

--------------------------------------------------------

-- DDL for Table PAYMENT_TERM_DTL

--------------------------------------------------------

CREATE TABLE "PAYMENT_TERM_DTL" ("ID" NUMBER DEFAULT ON NULL "PAYMENT_TERM_DTL_PK"."NEXTVAL", "PERCENTAGE" NUMBER, "DAYS_TO_COLLECT" NUMBER, "PAYMENT_TERM_ID" NUMBER) ;

--------------------------------------------------------

-- DDL for View ALL_CATEGORY_VU

--------------------------------------------------------

-- DDL for Index PI_PAYMENT_SCHEDULE_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "PI_PAYMENT_SCHEDULE_PK" ON "PI_PAYMENT_SCHEDULE" ("ID") ;

--------------------------------------------------------

-- DDL for Index PURCHASE_INVOICE_DTL_FK_IDX

--------------------------------------------------------

CREATE INDEX "PURCHASE_INVOICE_DTL_FK_IDX" ON "PURCHASE_INVOICE_DTL" ("PURCHASE_INVOICE_ID") ;

--------------------------------------------------------

-- DDL for Index PI_DTL_HDRID_ITEMID_UK

--------------------------------------------------------

CREATE UNIQUE INDEX "PI_DTL_HDRID_ITEMID_UK" ON "PURCHASE_INVOICE_DTL" ("PURCHASE_INVOICE_ID", "ITEM_ID") ;

--------------------------------------------------------

-- DDL for Index TABLE1_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "TABLE1_PK" ON "PURCHASE_INVOICE_DTL" ("ID") ;

--------------------------------------------------------

-- DDL for Index PURCHASE_INVOICE_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "PURCHASE_INVOICE_PK" ON "PURCHASE_INVOICE_HDR" ("ID") ;

--------------------------------------------------------

-- DDL for Index PAYMENT_TERM_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "PAYMENT_TERM_PK" ON "PAYMENT_TERM" ("ID") ;

--------------------------------------------------------

-- DDL for Index PAYMENT_TERM_DTL_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "PAYMENT_TERM_DTL_PK" ON "PAYMENT_TERM_DTL" ("ID") ;

--------------------------------------------------------

-- Constraints for Table PI_PAYMENT_SCHEDULE

--------------------------------------------------------

ALTER TABLE "PI_PAYMENT_SCHEDULE" ADD CONSTRAINT "PIPAYSCHED_DUEAMOUNTOVERZERO" CHECK (DUE_AMOUNT > 0) ENABLE;

ALTER TABLE "PI_PAYMENT_SCHEDULE" ADD CONSTRAINT "PI_PAYMENT_SCHEDULE_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;

ALTER TABLE "PI_PAYMENT_SCHEDULE" MODIFY ("DUE_AMOUNT" CONSTRAINT "DUE_AMOUNT_NOT_NULL" NOT NULL ENABLE);

ALTER TABLE "PI_PAYMENT_SCHEDULE" MODIFY ("DUE_DATE" CONSTRAINT "DUE_DATE_NOT_NULL" NOT NULL ENABLE);

--------------------------------------------------------

-- Constraints for Table PURCHASE_INVOICE_DTL

--------------------------------------------------------

ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PI_DTL_HDRID_ITEMID_UK" UNIQUE ("PURCHASE_INVOICE_ID", "ITEM_ID") USING INDEX ENABLE;

ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PIDTL_PRICE_OVER_ZERO_CHK" CHECK (PRICE > 0) ENABLE;

ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PRICE" NOT NULL ENABLE);

ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PID_QTY_OVER_ZERO" CHECK (QTY > 0) ENABLE;

ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PURCHASE_INVOICE_ID" NOT NULL ENABLE);

ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("QTY" NOT NULL ENABLE);

ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("ITEM_ID" NOT NULL ENABLE);

--------------------------------------------------------

-- Constraints for Table PURCHASE_INVOICE_HDR

--------------------------------------------------------

ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PURCHASE_INVOICE_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;

ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("CREATED_BY" NOT NULL DISABLE);

ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);

ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("BP_ID" NOT NULL ENABLE);

ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("CREATED_ON" NOT NULL ENABLE);

--------------------------------------------------------

-- Constraints for Table PAYMENT_TERM

--------------------------------------------------------

ALTER TABLE "PAYMENT_TERM" MODIFY ("NAME" NOT NULL ENABLE);

ALTER TABLE "PAYMENT_TERM" ADD CONSTRAINT "PAYMENT_TERM_PK" PRIMARY KEY ("ID") USING INDEX ENABLE NOVALIDATE;

--------------------------------------------------------

-- Constraints for Table PAYMENT_TERM_DTL

--------------------------------------------------------

ALTER TABLE "PAYMENT_TERM_DTL" MODIFY ("PAYMENT_TERM_ID" NOT NULL ENABLE);

ALTER TABLE "PAYMENT_TERM_DTL" ADD CONSTRAINT "PAYMENT_TERM_DTL_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;

ALTER TABLE "PAYMENT_TERM_DTL" MODIFY ("DAYS_TO_COLLECT" NOT NULL ENABLE);

ALTER TABLE "PAYMENT_TERM_DTL" MODIFY ("PERCENTAGE" NOT NULL ENABLE);

--------------------------------------------------------

-- Ref Constraints for Table PI_PAYMENT_SCHEDULE

--------------------------------------------------------

ALTER TABLE "PI_PAYMENT_SCHEDULE" ADD CONSTRAINT "PIPAYSCHEDULE_PIID_PIHDR_ID_FK" FOREIGN KEY ("PI_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ON DELETE CASCADE ENABLE;

--------------------------------------------------------

-- Ref Constraints for Table PURCHASE_INVOICE_DTL

--------------------------------------------------------

ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PI_ID_PID_PID_FK" FOREIGN KEY ("PURCHASE_INVOICE_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ON DELETE CASCADE ENABLE;

--------------------------------------------------------

-- Ref Constraints for Table PURCHASE_INVOICE_HDR

--------------------------------------------------------

ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PINVOICE_PAYTRMID_PAYTRM_ID_FK" FOREIGN KEY ("PAYMENT_TERM_ID") REFERENCES "PAYMENT_TERM" ("ID") ENABLE;

--------------------------------------------------------

-- Ref Constraints for Table PAYMENT_TERM_DTL

--------------------------------------------------------

ALTER TABLE "PAYMENT_TERM_DTL" ADD CONSTRAINT "PAYTERMDTLID_PAYTERM_ID_FK" FOREIGN KEY ("PAYMENT_TERM_ID") REFERENCES "PAYMENT_TERM" ("ID") ON DELETE CASCADE ENABLE;

--------------------------------------------------------

-------------------------------------------------------

Added on Jan 27 2020

8 comments

1,758 views