1 2 Previous Next 25 Replies Latest reply on Mar 26, 2017 5:50 PM by Stew Ashton

    How to write a SQL to do this?

    PLSQL_GUY

      I have simplified my problem to the below 2 tables.  All columns in these 2 tables are not null columns.

       

      DROP TABLE seasons;
      DROP TABLE rate_steps;

       

      CREATE TABLE seasons
      (
        code  NUMBER PRIMARY KEY,
        start_ NUMBER(3,0) NOT NULL,
        end_   NUMBER(3,0) NOT NULL
      );

      CREATE TABLE rate_steps
      (
        code  NUMBER ,
        start_ NUMBER(3,0) NOT NULL,
        end_   NUMBER(3,0) NOT NULL
      );

       

      Insert into SEASONS (CODE,START_,END_) values (1,5,10);

      Insert into SEASONS (CODE,START_,END_) values (2,11,20);

      Insert into SEASONS (CODE,START_,END_) values (3,21,50);

      Insert into SEASONS (CODE,START_,END_) values (4,51,80);

       

      Insert into rate_steps(CODE,START_,END_) values (1,3,10);

      Insert into rate_steps(CODE,START_,END_) values (1,11,80);

      Insert into rate_steps(CODE,START_,END_) values (2,5,50);

      Insert into rate_steps(CODE,START_,END_) values (2,51,70);

      Insert into rate_steps(CODE,START_,END_) values (2,71,80);

      Insert into rate_steps(CODE,START_,END_) values (3,5,30);

      Insert into rate_steps(CODE,START_,END_) values (3,40,80);

      Insert into rate_steps(CODE,START_,END_) values (4,25,75);

      Insert into rate_steps(CODE,START_,END_) values (4,76,90);

      Insert into rate_steps(CODE,START_,END_) values (5,5,70);

      Insert into rate_steps(CODE,START_,END_) values (5,71,90);

       

      Only  rate code 2 is valid. All other rates are invalid.

       

      The logic is now very simple.

      For a rate to be valid, it's start and end values in rate_steps
      should cover the range 5 to 80 only, CONTINUOUSLY.

      Any other rate is invalid.

      How can we write an SQL like this,

      SELECT DECODE(asfasf, valid-rate, 0, -1) FROM seasons, rate_steps WHERE rates.code= :rate_code....?

       

      PS: The RATES table is the parent table of RATE_STEPS.

        • 1. Re: How to write a SQL to do this?
          Frank Kulash

          Hi,

           

          Thanks for posting the CREATE TABLE and INSERT statements.

           

          Don't forget to post the exact results you want from that sample data, and an explanation of how you get those results from that data..

          • 2. Re: How to write a SQL to do this?
            PLSQL_GUY

            Frank Kulash wrote:

             

            Hi,

             

            Thanks for posting the CREATE TABLE and INSERT statements.

             

            Don't forget to post the exact results you want from that sample data, and an explanation of how you get those results from that data..

            The logic is simple: For a given rate, it is valid only if it's rate_step start and end values are within the season start and end values.

            It has to be continuous without any breaks.

            • 3. Re: How to write a SQL to do this?
              mathguy

              So, if I am reading this correctly, you want a query that will return each code from the RATES table (not included in your table setup) and a number, either 0 or 1, indicating whether that "rate" code is valid or not. And the validity is based only on table RATE_STEPS and has nothing to do with SEASONS. Is that correct?

               

              In your code snippet, you select FROM SEASONS, RATE_STEPS  WHERE RATES.<something> = ...  I don't know how that is supposed to work - how can you have a condition on RATES.<anything> if RATES is not included in the FROM clause?

              • 5. Re: How to write a SQL to do this?
                Frank Kulash

                Hi,

                PLSQL_GUY wrote:

                ...

                The logic is simple:

                ...

                Whether the logic is simple or not, you always need to post the exact results you want from the given sample data.  You still haven't done this.

                • 6. Re: How to write a SQL to do this?
                  James Su

                  select code,connect_by_root(start_) as range_start,end_ 

                  from rate_steps

                  where connect_by_isleaf=1

                        and end_ = (select max(end_) from seasons)

                  start with start_ = (select min(start_) from seasons)

                  connect by code=prior code and start_=prior end_+1 ;

                  • 7. Re: How to write a SQL to do this?
                    BluShadow

                    SQL> select code
                      2  from (
                      3        select r.*
                      4              ,start_-nvl(lag(end_) over (partition by code order by start_),start_-1)-1 as diff
                      5              ,case when start_ between 5 and 80 and end_ between 5 and 80 then 0 else 1 end as chk
                      6        from   rate_steps r
                      7       )
                      8  group by code
                      9  having sum(diff) = 0 and sum(chk) = 0
                    10  order by code
                    11  /

                          CODE
                    ----------
                             2

                    • 8. Re: How to write a SQL to do this?
                      BluShadow

                      And if you want a flag to indicate valid or invalid for each code...

                       

                      SQL> ed
                      Wrote file afiedt.buf

                        1  select code
                        2        ,-sign(sum(diff)+sum(chk)) as valid
                        3  from (
                        4        select r.*
                        5              ,start_-nvl(lag(end_) over (partition by code order by start_),start_-1)-1 as diff
                        6              ,case when start_ between 5 and 80 and end_ between 5 and 80 then 0 else 1 end as chk
                        7        from  rate_steps r
                        8      )
                        9  group by code
                      10* order by code
                      SQL> /

                            CODE      VALID
                      ---------- ----------
                               1        -1
                               2         0
                               3        -1
                               4        -1
                               5        -1

                       

                      Though you didn't specify whether valid is shown by 0 or -1, so I assumed 0

                      • 9. Re: How to write a SQL to do this?
                        James Su

                        If I change one row in OP's data:

                        update rate_steps set start_=6 where start_=5 and code=2;

                         

                        Your query still treats it as valid.

                        • 10. Re: How to write a SQL to do this?
                          BluShadow

                          It does.  Perhaps I misunderstood the requirement... I thought that the range had to be within 5 to 80.  Should it be exactly from 5 to 80?

                           

                          That's the problem with poorly explained questions.

                          • 11. Re: How to write a SQL to do this?
                            BluShadow

                            Re-reading the requirement, if we take the statement literally...

                             

                            For a rate to be valid, it's start and end values in rate_steps

                            should cover the range 5 to 80 only, CONTINUOUSLY.

                             

                            Then the values from 1 to 100 will "cover" the range 5 to 80, so they would be valid as long as the rows are "continuous" in covering the range.

                             

                            So it's not clear at all.  I took it to mean "within" the range 5 to 80, but now re-reading it seems that it could be the opposite, or as you suggest, even has to be exactly from 5 to 80.

                             

                            Poor requirements = non-working answers.

                            • 12. Re: How to write a SQL to do this?
                              James Su

                              OP said it "should cover the range 5 to 80 only" so I guess it should be the exact range, even the wider one (code=1) is not considered as a "cover".

                              • 13. Re: How to write a SQL to do this?
                                Stew Ashton

                                [UPDATE: PLSQL_GUY has now replied to the original question, apologizing and inundating me with points. Strike through my complaints. My aim was not points of course but a fruitful dialog ending in a solution to the problem.]

                                PLSQL_GUY wrote:

                                When you first posted your question, I provided 5 different replies with a combination of working code, ideas and questions.

                                 

                                You ignored all of them.

                                 

                                That is not polite, at all. You have discouraged me from ever trying to help you again.

                                 

                                Regards, Stew Ashton

                                • 14. Re: How to write a SQL to do this?
                                  CarlosDLG

                                  PLSQL_GUY wrote:

                                  ...

                                   

                                  PS: The RATES table is the parent table of RATE_STEPS.

                                   

                                  Which rates table?  There is no rates table in your script.  Is that relationship important?  If so, you need to post that table's creation script too, and explain why it is important.

                                  1 2 Previous Next