Results 1 to 6 of 6

Thread: Need help

  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Need help

    Hi ,

    Here I am explaning with sample example . (clothes shopping ) I just want to pick the discount percentage based on the purchase.

    I am having the below tables .

    Purchase Information : purchase_tab
    pur_id pur_number pur_order pur_city pur_ctry pur_type pur_size pur_quantity pur_amount

    Discount Information : discount_tab
    dis_id dis_limit_from dis_limit_to discount

    discount_condition Apply Information : dis_cind_tab
    dis_cond_id dis_id dis_cond_type dis_value

    In this table(dis_cond_tab) , dis_cond_type is pur_order,pur_city,pur_ctry, pur_type, pur_size ,pur_quantity.
    And dis_value is the above(pur_order,pur_city,pur_ctry, pur_type, pur_size ,pur_quantity) one's value or values .
    We can enter same dis_cond_typt(except pur_size , pur_quantity) with different Values .
    Ex . purchase_tab.pur_order in (ABCD,AAQQ) where dis_id =21 .
    For purchase_tab.pur_size <=20 and purchase_tab.pur_quantity <=2 and purchase_tab.order in (XXYY)

    NOTE : here i am storing the columns of purchase_tab table in the dis_cnd_tab table as rows .

    Code:
    This is the smaple data ........
    purchase_tab
      pur_id   pur_date    pur_number  pur_order  pur_city  pur_ctry  pur_type  pur_size  pur_quantity  pur_amount
      ----------------------------------------------------------------------------------------------------------
       11      15-JAN-12    1001       ABCD       DELHI      IN       card       20         2            2300
       12      15-Mar-12    1002       XXYY       AGRA       IN       card       40         3            4000
    
    discount_tab   
      dis_id   dis_limit_from  dis_limit_to  discount
      --------------------------------------------------
       21        1-JAN-12        31-JAN-12      20
       23        3-MAR-12        30-APR-12      15
     
    dis_cind_tab 
      dis_cond_id   dis_id  dis_cond_type  dis_value
      -----------------------------------------------
        90     	      21 	  pur_order       ABCD
        91              21      pur_ctry          IN
        92	       21      pur_order       A123
        93              23      pur size           20
        94	       23      pur_order       XXYY
          95            23      pur_quantity    2
    Case1 : For the purchase pur_id = 11, i need to apply the percentage on pur_amount = 2300.
    First I need to select the discount percentage .
    pur_date (15-Jan-12) is between 1-JAN-12 ,31-JAN-12 . So, it will take the dis_id =21 .
    For this dis_id , I am having pur_order in (ABCD,A123) and pur_ctry in (IN) ,these values are matching with purchase_tab information .
    So, discount 20 ,should apply on the amount =2300 .

    case2 : For the purchase pur_id = 12, i need to apply the percentage on pur_amount = 4000.
    First I need to select the discount percentage .
    pur_date (15-mar-12) is between 3-mar-12 ,30-apr-12 . So, it will take the dis_id =23 .
    For this dis_id , I am having pur_order in (XXYY) and pur_size <=20 and pur_quantity <=2 ,these values are matching with purchase_tab information .
    So, discount 15 ,should apply on the amount =4000 .

    I am new to this forum ......,i tried to keep the message in the format ...
    Last edited by sailu; 10-07-11 at 12:06. Reason: it is not formatted

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am new to this forum ......,i tried to keep the message in the format ...

    you did better than most.
    You have a better chance for obtaining functioning solution if you post CREATE TABLE for tables
    & INSERT statements for actual test data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Code:
    create table purchase_tab (pur_id number(10) primary key ,pur_date date,pur_number number(10), pur_order varchar2(30),
    pur_city varchar2(20),  pur_ctry varchar2(20), pur_type varchar2(10), pur_size number(10), pur_quantity number(10), pur_amount number(10));
    
    create table discount_tab(dis_id number(10) primary key, dis_limit_from date, dis_limit_to date, discount number(10));
    
    create table dis_cond_tab ( dis_cond_id number(10) ,  dis_id number(10) ,  dis_cond_type varchar2(30),  dis_value varchar2(10),
    constraint fk1 foreign key(dis_id) references discount_tab(dis_id));
    
    insert into purchase_tab values(11,'15-JAN-12', 1001,' ABCD',' DELHI','IN','card',20, 2,2300);
    insert into purchase_tab values(12,'15-Mar-12', 1002,' XXYY','AGRA','IN',' card',40,  3,4000);
    insert into discount_tab values(21,'1-JAN-12','31-JAN-12', 20);
    insert into discount_tab values(23,'3-MAR-12','30-APR-12', 15);
    insert into dis_cind_tab values(90,21,'pur_order','ABCD');   
    insert into dis_cind_tab values(91,21,'pur_ctry','IN');   
    insert into dis_cind_tab values(92,21,'pur_order','A123');  
    insert into dis_cind_tab values(93,23,'pur_size','20');   
    insert into dis_cind_tab values(94,23,'pur_order','XXYY');   
    insert into dis_cind_tab values(95,23,'pur_quantity','2');

  4. #4
    Join Date
    Sep 2011
    Posts
    20
    Here is a sample code based on the CREATE TABLE & INSERT scripys that you supplied:

    Code:
    select p.pur_id, p.pur_date, p.pur_number, p.pur_amount, d.discount, p.pur_amount*(d.discount/100) as "Amount After Discount"
    from purchase_tab p
    inner join discount_tab d
    on p.pur_date between d.dis_limit_from and d.dis_limit_to
    Here is the results of the above query:
    Code:
    PUR_ID     PUR_DATE      PUR_NUMBER    PUR_AMOUNT  DISCOUNT   Amount After Discount
    11        15-Jan-12         1001         2300        20       460
    12        15-Mar-12         1002         4000        15       600

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    I need to use dis_cond_tab table also , based on the conditions only we can pick the discount .
    In this table, we can enter any number of conditions .

    only sql it's not possible, I think, need to write a procedure/function.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by sailu View Post
    only sql it's not possible, I think, need to write a procedure/function.
    Why not, it is just a bunch of simple EXISTS/IN clauses, something like:
    Code:
      and (P.PUR_ORDER in (
             select c.DIS_VALUE
             from DIS_COND_TAB c
             where c.DIS_COND_TYPE = 'pur_order'
               and c.DIS_ID = d.DIS_ID)
       or not exists ( <previous subquery> ))
    (suppose that non-existence of any condition for given (DIS_ID, DIS_COND_TYPE) shall not filter the row - e.g. missing (23, 'pur_ctry'))
    Using function would lead to shorter SQL probably.

    I just wonder how do you assure that
    We can enter same dis_cond_typt(except pur_size , pur_quantity) with different Values .
    Anyway you should at least assure that DIS_VALUE contains only numeric value for these types, or you will end in comparing those numbers with strings, e.g. 'a'. It may fail (better option) or lead to unpredictable results (when comparing strings, e.g. value '10' is less than '2'). So, at least you should convert DIS_VALUE to number explicitly and sometimes put up with its failure.

    Maybe you will benefit from reading some articles about this design called OTLT:
    OTLT (One True Lookup Table) Systems Engineering and RDBMS
    Tony Andrews on Oracle and Databases: OTLT and EAV: the two big design mistakes all beginners make

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •