Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to select record based on priority

Hussain#Oct 11 2020 — edited Oct 15 2020

HI,
Database 12c.
Below are the script based on discount policy which is maintained in a table. Now there two way to define discount either it can be category level or item level. For example, beneath item wise discount has been entered which is first priority, if in case not found then system will see at category level.

Create table INV_ITEM_DISCOUNT
(
CATEGORY_ID NUMBER(3),
ITEM_ID NUMBER,
DIS_RATE NUMBER(11,4),
FROM_DATE DATE,
TO_DATE DATE,
ACTIVE VARCHAR2(1)
)
INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,GST_RATE,FROM_DATE,TO_DATE,ACTIVE)
VALUES (1,1,.10,SYSDATE,NULL,'A')

INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,GST_RATE,FROM_DATE,TO_DATE,ACTIVE)
VALUES (1,null,.10,SYSDATE,NULL,'A')

Comments

Processing

Post Details

Added on Oct 11 2020
8 comments
4,275 views