Forum Stats

  • 3,741,291 Users
  • 2,248,405 Discussions
  • 7,861,727 Comments

Discussions

How to select record based on priority

Hussain#
Hussain# Member Posts: 142 Red Ribbon
edited Oct 15, 2020 8:07PM in SQL & PL/SQL

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

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,777 Bronze Crown
    edited Oct 11, 2020 10:45AM

    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.

    AYousef
  • Hussain#
    Hussain# Member Posts: 142 Red Ribbon

    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#
    Hussain# Member Posts: 142 Red Ribbon

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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,727 Red Diamond

    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
    Marwim Member Posts: 3,621 Gold Trophy
    edited Nov 16, 2020 8:41AM

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

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

    Marcus

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    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
    EdStevens Member Posts: 28,243 Gold Crown

    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#
    Hussain# Member Posts: 142 Red Ribbon

    Hi,

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


    Thanks.

Sign In or Register to comment.