How to select record based on priority

Hussain#

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')

Gaz in Oz

Your posted DDL and your insert statements don't match. You use GST_RATE in the inserts but your table has a DST_RATE in it.

Please post DDL for both tables and inserts for both tables so that it works...

Also post SQL query you have tried AND the expected output from the sample data you post.

Regardless, perhaps you could use DENSE_RANK() function to get the results you haven't posted yet.

Hussain#

Dear,

Below are the statements which corrected now. Now my requirement is when i run select statement by given category and item then it should be bring which record having more detail.

INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,DIS_RATE,FROM_DATE,TO_DATE,ACTIVE)

     VALUES (1,2,.10,SYSDATE,NULL,'A'

INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,DIS_RATE,FROM_DATE,TO_DATE,ACTIVE)

     VALUES (1,null,.20,SYSDATE,NULL,'A')

Based on these table left side is item master information whereas right side discount policy. Now if i pass item 2 then it will bring discount .1% where as if pass item 2 then it will show .2%

Hussain#

I posted script and insert statement, please guide on this.

Frank Kulash

Hi,

Below are the statements which corrected now. Now my requirement is when i run select statement by given category and item then it should be bring which record having more detail.

INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,DIS_RATE,FROM_DATE,TO_DATE,ACTIVE)

     VALUES (1,2,.10,SYSDATE,NULL,'A'

INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,DIS_RATE,FROM_DATE,TO_DATE,ACTIVE)

     VALUES (1,null,.20,SYSDATE,NULL,'A')

Would you like to get answers that work? Then make sure the CREATE TABLE and INSERT statements you post work, too. The INSERT statements above are not corrected. Test (and, if necessary, fix) your statements before you post them. Post working CREATE TABLE and INSERT statements for all the tables involved.

One way to solve your problem is a Top-N query, where you choose the N items (N=1 ion this case) from the top of an ordered list. Suppose you have a query where you want to join to the "best" possible row in inv_item_discount, and rows with matching matching item_id are "better" than rows where item_id is NULL. One way to do that is to join all possible rows (that is, all rows that might be acceptable, if no better row is available), use a ranking function to number them so a "better" row gets a lower number than a "worse" row, and then take only the rows where that ranking function returned 1. n example of a ranking function is

ROW_NUMBER () OVER (PARTITION BY im.category_id, im.item_id
                    ORDER BY  CASE
                                  WHEN iid.item_id IS NOT NULL
                                  THEN 'A'
                                  ELSE 'B'
                              END
                   )  AS rn

Depending on what you want in case of a tie (that is, two or more rows having a equal claim to being the "best" row) you may want to use RANK or DENSE_RANK (as Gaz suggested earlier) instead of ROW_NUMBER.

Marwim

A similar problem with a suggested solution. Maybe overkill if you only need 2 levels.

https://community.oracle.com/tech/developers/discussion/4019363/implement-decision-table

Edit: the associated blog entry: http://matzberger.de/oracle/decision-table.html

Marcus

James Su

Your description is not very clear but I guess this is what you want:

select * from (

select * from INV_ITEM_DISCOUNT

where category_id = :p_category_id

   and (item_id = :p_item_id or item_id is null)

order by item_id nulls last    

)

where rownum=1

EdStevens

I'm rather surprised that the CREATE TABLE even works, with a column named TO_DATE. That's also the name of a sql function. Even if it works, it's a poor choice of names.

Hussain#

Hi,

Thanks for highlighted this issue, and made it correct. By the i am look an option where can mark correct/Answer button.

Thanks.