Results 1 to 10 of 10

Thread: help plsql

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: help plsql

    Can anyone please help for plsql code

    PHP Code:

    Create table test
     
    A_from       number(3),
       
    A_To         number(3),
       
    B_from       number(3),
       
    B_To         number(3)
    );
       
    Insert into test values 1020100200);
    Insert into test values 3140210211);



    A_from  A_To    B_from  B_To
    -----------------------------
    10     20     100     200
    31     40     210     211


    I Only want to insert record in table test when
    Case1
    :-
    Assume that there are already two records in table test as shown above.

    New 
    record should be only allowed to insert when :-

    A_from  A_To    B_from  B_To
    -----------------------------
    11     15      100     200       record1
    9       15      100     200       record2
    10      20      201     230       record3                                   
    9       25      100     200       record4 
    record1-- This record should not be allowed since its subset of (10,20)(A_from,A_To)
    record2-- This record should not be allowed since 15 lies between (10,20)(A_from,A_To)
    record3-- This record should be allowed since B_form and B_TO is different.
    201 and 230 are not superset and subset of any records in B_From and B_To.
    record4-- this record should not be allowed...since its a super set of 10 and 20.

    Any help is kindly appreciated

    Thanks
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Are you saying that:
    1) it is OK for the A ranges of 2 records to overlap as long as the B ranges do not, and
    2) it is OK for the B ranges of 2 records to overlap as long as the A ranges do not?

    If so, on insert of a new record you need to ensure that there is no existing record where both the A range and the B range overlap. i.e. the following query should return no rows (where t1 is the new record):

    select 1
    from test t2
    where (t2.a_from <= t1.a_to and t2.a_to >= t1.a_from) -- A ranges overlap
    and (t2.b_from <= t1.b_to and t2.b_to >= t1.b_from) -- B ranges overlap

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thanks for the reply.

    My requirment is No ranges( A OR B) should be overlap.
    if B ranges are changed A shouldn't overlap but can be similar. For example
    PHP Code:
    Existing records
    A_from    A_to     B_from     B_to
    -------------------------------------
    10        20        100          200
    30        40        201          205    


    Records which can be inserted are
    .
    41       45         201          205  
    Above record is allowed since A ranges are changing 
    and
    B ranges can remian same but can't overlap i.e
    41   45  200  206  is not allowed since it is overlapping 201 & 205
    Similarly
    30  40   206  207 is allowed.

    10   15   206  207 is not allowed

    5     9     100   200 is allowed 
    Hope it is now clear.

    Thanks
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    How about a BEFORE INSERT trigger??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thanks for the reply.
    Trigger are always overhead to the system. So better i'll go for Procedure.
    What can be the logic for the PLSQ block?


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I am afraid that your overlap/no-overlap rules are not at all clear - at least to me! Do you have the rules stated in the form of a spec or pseudo-code?

    The examples you have given are confusing (to me):

    1) My requirment is No ranges( A OR B) should be overlap
    - sounds clear enough

    2) if B ranges are changed A shouldn't overlap but can be similar
    What does "similar" mean here? To me, "no overlap" means that there are no common values at all. All the following examples overlap the range 10-20 according to my interpretation:
    a) 10-20
    b) 9-21
    c) 9-10
    d) 20-21
    e) 11-19
    Sounds like you might say some of these were "similar" but didn't "Overlap"?

  7. #7
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Sorry for the inconvenience!!...Sorry i don't have any document or pseudo code.
    Actual Data
    PHP Code:
    rec   a_f   a_t    b_f    b_t    c_f    c_t    
    1     10    20      50     60     5       6
    2     21    30      61     70     21     30

    3     31    40      71     80     31     40
    4     31    40      90     95     31     40
    5     31    40      100   110    31     40
    6     31    40      100   110    41     50  

    7     60     61     100    110   41     50
    8     60     61     100    110   70     80 
    1. There should not be duplicate rows.
    2. Any particular range can repeat, provided it is a distinct rows.

    What does "similar" mean here? To me, "no overlap" means that there are no common values at all. All the following examples overlap the range 10-20 according to my interpretation:

    PHP Code:
    a10-20
    b
    9-21
    c
    9-10
    d
    20-21
    e
    11-19 
    ---Yes, ur correct... but we will only consider range 10 -20 if any one of the ranges in the row are differeing.
    --Please have look on records 3,4,5 and 6.

    Hope this time it is clear!!! still doubt please revert
    Pagnint
    (No need to search web before posting new question)

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, but I haven't really "got it" yet. This reminds me of a card game called "Eleusis", where one player accepts or rejects cards according to a secret rule, and the other has to guess what the rule is!

    Can you not state in words the complete rules you are following, rather than some of the rules and some examples?

    Anyway, to continue the induction process, you say this pair of records is acceptable, despite the fact that the A and B ranges overlap:

    7 60 61 100 110 41 50
    8 60 61 100 110 70 80

    So is overlap allowed only in the sense of identical ranges? i.e. the following pair would not be allowed?

    7 60 61 100 110 41 50
    8 60 62 100 110 70 80

  9. #9
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Yes ur correct overlap only allow in identical cases. Sorry i am weak in explaining things.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about this (adapting from my earlier attempt):
    Code:
    select 1
    from test t2
    where  -- A ranges overlap and are not identical
    (t2.a_from <= t1.a_to and t2.a_to >= t1.a_from and (t1.a_from != t2.a_from or t1.a_to != t2.a_to))
    or     -- B ranges overlap and are not identical
    (t2.b_from <= t1.b_to and t2.b_to >= t1.b_from and (t1.b_from != t2.b_from or t1.b_to != t2.b_to))
    or     -- C ranges overlap and are not identical
    (t2.c_from <= t1.c_to and t2.c_to >= t1.c_from and (t1.c_from != t2.c_from or t1.c_to != t2.c_to))
    (T1 is the new record about to be inserted).

    If that query returns any rows, then you have an invalid pair.

Posting Permissions

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