1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Range issue

Discussion in 'SQL PL/SQL' started by Bharat, Jul 17, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    I have an table shown below:

    Code (Text):

    SELECT * FROM TEMP_TYPE;

    TYPE        LOW     HIGH    
    --------    ----    ----
    INCLUDE     01      50
    INCLUDE     52      75
    EXCLUDE     01      03
    EXCLUDE     05      20
    EXCLUDE     22      50
    EXCLUDE     53      70
    EXCLUDE     72      75

     
    Now my requirement is I want to get the records where exclude records are not there in include records. Just like we have 1 to 50 in include and we have 1 to 3, 5 to 20 and 22 to 50 in exclude as shown in above result. Now my requirement is to exclude those records from include record and then it has to result 4 and 21.

    And same as we have 53 to 70 and 72 to 75 exclude records. We have to exclude these records from include record and then it has to result as 52 and 71. Why not 51 means it is not included in include record. Just we have 01 to 50 and 52 to 75. So we dont need to get 51 in our result.

    Please help me on this issue.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Anyone please help me on this issue.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Begging is not a proper way to solicit assistance; posting a question once and having to wait for a response is the normal course of action. Remember this is a forum, not a one-on-one interaction, and it may take time for members to understand the question enough to provide a response. I can understand your frustration but such additional posts appear as desperate attempts to force people to respond, thus my opening sentence.

    This would be easier if you weren't so set on having a character format for your numbers, but it can be done nevertheless by rigging the query. One pass at this returns ALMOST the desired results:

    Code (SQL):

    SQL> WITH rn AS (
    2 SELECT rownum r FROM dual
    3 CONNECT BY level <=75
    4 )
    5 SELECT r
    6 FROM rn
    7 WHERE r IN (SELECT to_number(lead(low) OVER (partition BY TYPE ORDER BY low)) - (to_number(lead(low) OVER (partition BY TYPE ORDER b
    y low)) - to_number(high) - 1)
    8 FROM temp_type WHERE TYPE = 'EXCLUDE')
    9 ORDER BY 1
    10 /
     
    R
    ----------
    4
    21
    51
    71
    SQL>
    [FONT=Comic Sans MS][SIZE=3][FONT=Comic Sans MS][SIZE=3][/SIZE][/FONT][/SIZE][/FONT]
    However it returns 51, not 52. Adjusting the query a bit provides:

    Code (SQL):
    SQL> WITH rn AS (
    2 SELECT rownum r FROM dual
    3 CONNECT BY level <=75
    4 )
    5 SELECT CASE WHEN r=51 THEN 52 ELSE r END incl
    6 FROM rn
    7 WHERE r IN (SELECT to_number(lead(low) OVER (partition BY TYPE ORDER BY low)) - (to_number(lead(low) OVER (partition BY TYPE ORDER b
    y low)) - to_number(high) - 1)
    8 FROM temp_type WHERE TYPE = 'EXCLUDE')
    9 ORDER BY 1
    10 /
     
    INCL
    ----------
    4
    21
    52
    71
    SQL>
    but this hardcodes a value in the query that may not exist in other sets of data.

    You may be required to write this in PL/SQL and I simply don't have the time at the moment to write and debug such code.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This can be done with oure SQL without hardoding as follows.

    Code (SQL):
    SQL> SELECT * FROM test123 ORDER BY 1 ,2;

    TYP        LOW HIG
    ---------- --- ---
    EXCLUDE    01  03
    EXCLUDE    05  20
    EXCLUDE    22  50
    EXCLUDE    53  70
    EXCLUDE    72  75
    EXCLUDE    82  95
    INCLUDE    01  50
    INCLUDE    52  75
    INCLUDE    80  99

    9 ROWS selected.

    SQL> WITH A1 AS ( SELECT DISTINCT  TYP, LOW,HIG
      2                FROM TEST123 WHERE  TYP ='INCLUDE'  ) ,
      3      B1 AS ( SELECT level l FROM dual
      4              CONNECT BY level  <= (SELECT  MAX (to_number( HIG))  FROM  TEST123 WHERE  TYP ='INCLUDE' )
      5              AND  level  >=  (SELECT  MIN (to_number(LOW)) FROM  TEST123 WHERE  TYP ='INCLUDE' )  )
      6  SELECT lpad (l,2,'0') X  FROM A1, B1
      7  WHERE b1.l BETWEEN a1.LOW AND a1.HIG
      8  MINUS
      9  SELECT lpad (l,2,'0') X  FROM
     10   (SELECT DISTINCT  TYP, LOW,HIG
     11          FROM TEST123 WHERE  TYP ='EXCLUDE'  )A2,
     12    ( SELECT level l FROM dual
     13      CONNECT BY level <=    (SELECT  MAX (to_number( HIG)) FROM  TEST123 WHERE  TYP ='EXCLUDE' )
     14      AND  level  >=  (SELECT  MIN (to_number(LOW)) FROM  TEST123 WHERE  TYP ='EXCLUDE' )  ) B2
     15  WHERE b2.l BETWEEN a2.LOW AND a2.HIG;

    X
    --
    04
    21
    52
    71
    80
    81
    96
    97
    98
    99

    10 ROWS selected.

    SQL>
     
    Bharat likes this.
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi raj,

    Here its working fine if we have table values as numbers. But if we have characters then this is failing to run the query. Can you please suggest me to do so.

    Code (Text):


    SELECT * from temp_type1;

    Output: -

    INCLUDE 01  50
    INCLUDE 52  75
    EXCLUDE 01  03
    EXCLUDE 05  20
    EXCLUDE 22  50
    EXCLUDE 55  70
    EXCLUDE 72  75
    INCLUDE 80  100
    EXCLUDE 86  96
    INCLUDE 101 ZZZ


     
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    What do you mean by "INCLUDE 101 ZZZ"
    series from '101' to 'ZZZ' ? What kid of series is that ?
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi raj,

    Actually that was varchar2 field and this query is using for FND_FLEX_VALUE_RULE_LINES table. In this table flex_value_low and flex_value_high have such values as shown below:

    INCLUDE 000 ZZZ
    EXCLUDE 002 ZZZ

    So they excluded from 2 to zzz and returns 001 which is nothing but segment1 in gl_code_combinations. This is usually called company name.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been NICE had you provided sample data that more closely matched your production values; solutions presented thus far relied on either numbers or strings convertible to numbers (as you posted in your original question). NOW you throw another condition into the mix and wonder why the answers do not work.

    We CANNOT give accurate answers based on assumptions or misleading sample data. It would be best if you asked your question again with data values that COULD be used rather than made-up data that has no relation to the situation.

    You might get a usable response.
     
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi david,

    Here actual requirement is we have one table called FND_FLEX_VALUE_RULE_LINES which contains some legal entity numbers based on particular company. I would like to find out that legal entity name based on those values. We have a situation like some company contains so many legal entities like UK, AUS, USA and so on. We did some transactions for that company from USA to UK. But here the data is stored in USD currency. So we have got this solution to find out based on that number we can find out the legal entity and through that we can find currency code for that legal entity. So we can convert the rate using that. To do so we have information like FLEX_VALUE_HIGH, FLEX_VALUE_LOW and INCLUDE_EXCLUDE_INDICATOR. So here the scenario is we have values as shown below:

    Code (Text):


    SQL>select flex_value_rule_id, include_exclude_indicator, flex_value_low, flex_value_high from fnd_flex_value_rule_lines where flex_value_rule_id in (10,11,14);

    Output:-

    flex_value_rule_id  include_exclude_indicator  flex_value_low  flex_value_high
    10                               E                     00             06
    10                               I                     00             ZZ
    10                               E                     08             ZZ
    11                               I                     00             ZZ
    11                               E                     00             04
    11                               E                     06             ZZ
    14                               I                     00             ZZ
    14                               E                     00             01
    14                               E                     03             20
    14                               E                     22             24
    14                               E                     27             ZZ



     
    So here flex_value_low is low and flex_value_high is high value. SO I need to exclude the given exlude flagged values from include flagged values. Then the query has to give results as 07 for 10 flex_value_rule_id , 05 for 11 flex_value_rule_id and 02,21,25,26 for 14 flex_value_rule_id.

    This is my exact requirement. This data resides in security profiles for each and every company and their legal entities.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I haven't the time to pursue this further but I have a PL/SQL block that appears to be working:

    Code (SQL):
    SQL> CREATE TABLE fnd_flex_value_rule_lines(
      2                  flex_value_rule_id              NUMBER NOT NULL,
      3                  include_exclude_indicator       varchar2(2),
      4                  flex_value_low                  varchar2(6),
      5                  flex_value_high                 varchar2(6)
      6  );
     
    TABLE created.
     
    SQL>
    SQL>
    SQL> INSERT ALL
      2  INTO fnd_flex_value_rule_lines
      3  VALUES(10,'E','00','06')
      4  INTO fnd_flex_value_rule_lines
      5  VALUES(10,'I','00','ZZ')
      6  INTO fnd_flex_value_rule_lines
      7  VALUES(10,'E','08','ZZ')
      8  INTO fnd_flex_value_rule_lines
      9  VALUES(11,'I','00','ZZ')
     10  INTO fnd_flex_value_rule_lines
     11  VALUES(11,'E','00','04')
     12  INTO fnd_flex_value_rule_lines
     13  VALUES(11,'E','06','ZZ')
     14  INTO fnd_flex_value_rule_lines
     15  VALUES(14,'I','00','ZZ')
     16  INTO fnd_flex_value_rule_lines
     17  VALUES(14,'E','00','01')
     18  INTO fnd_flex_value_rule_lines
     19  VALUES(14,'E','03','20')
     20  INTO fnd_flex_value_rule_lines
     21  VALUES(14,'E','22','24')
     22  INTO fnd_flex_value_rule_lines
     23  VALUES(14,'E','27','ZZ')
     24  SELECT * FROM dual;
     
    11 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> DECLARE
      2
      3  incl_temp NUMBER;
      4  cursor get_incl_vals IS
      5  SELECT rule_id, to_number(lst_excl) lst_excl, to_number(next_excl) next_excl,
      6         (to_number(next_excl) - to_number(lst_excl) - 1) diff
      7  FROM
      8  (SELECT flex_value_rule_id rule_id, lead(flex_value_low) OVER (partition BY include_exclude_indicator ORDER BY flex_value_rule_id, f
    lex_value_low) next_excl,  flex_value_high lst_excl
      9  FROM fnd_flex_value_rule_lines
     10  WHERE include_exclude_indicator = 'E'
     11  ORDER BY flex_value_rule_id)
     12  WHERE lst_excl <> 'ZZ';
     13  BEGIN
     14          FOR i IN get_incl_vals loop
     15                  FOR j IN 1..i.diff loop
     16                          incl_temp:=i.next_excl - j;
     17                          dbms_output.put_line(i.rule_id||'       '||incl_temp);
     18                  END loop;
     19          END loop;
     20  END;
     21  /
    10      7
    11      5
    14      2
    14      21
    14      26
    14      25
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    Bharat likes this.
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This can also done using straight SQl as below. There is some sort of hard coding for 'ZZ' (100) and 'AA' (0)

    Code (SQL):
    SQL> SELECT * FROM test123 ORDER BY 1, 2, 3 ,4;

    RUL TYP LOW HIG
    --- --- --- ---
    10  E   00  06
    10  E   08  ZZ
    10  I   00  ZZ
    11  E   00  04
    11  E   06  ZZ
    11  I   00  ZZ
    14  E   00  01
    14  E   03  20
    14  E   22  24
    14  E   27  ZZ
    14  I   00  ZZ

    11 ROWS selected.

    SQL> WITH A1 AS ( SELECT RUL, TYP,
      2                      TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )) LOW ,
                        TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
      3    4              FROM TEST123 WHERE  TYP ='I' ) ,
      5       B1 AS ( SELECT LEVEL L
      6               FROM dual
      7               CONNECT BY LEVEL  <= (SELECT MAX ( HIG)  FROM  A1 )
      8               AND LEVEL  >= (SELECT MIN  (LOW)  FROM  A1 ) )
      9  SELECT RUL, l NUM  FROM A1, B1
     10  WHERE b1.l BETWEEN a1.LOW AND a1.HIG
     11  MINUS
     12  SELECT RUL, l  FROM
     13    ( SELECT RUL, TYP,
     14             TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )) LOW ,
     15             TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
     16     FROM TEST123 WHERE  TYP ='E'  )A2,
     17    ( SELECT level l
     18      FROM dual
     19      CONNECT BY LEVEL <= (SELECT MAX( TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )))
     20                           FROM  TEST123
     21                           WHERE  TYP ='E'  )
     22      AND  LEVEL  >= (SELECT MIN( TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )))
     23                      FROM  TEST123
     24                      WHERE  TYP ='E'  ) )   B2
     25  WHERE b2.l BETWEEN a2.LOW AND a2.HIG;

    RUL  NUM
    --- ----
    10     7
    11     5
    14     2
    14    21
    14    25
    14    26

    6 ROWS selected.

    SQL>
     
    Bharat likes this.
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Changing my PL/SQL slightly to remove any hard-coded values for the maximum high value:

    Code (SQL):
    SQL> DECLARE
    2
    3 incl_temp NUMBER;
    4 cursor get_incl_vals IS
    5 SELECT rule_id, to_number(lst_excl) lst_excl, to_number(next_excl) next_excl,
    6 (to_number(next_excl) - to_number(lst_excl) - 1) diff
    7 FROM
    8 (SELECT flex_value_rule_id rule_id, lead(flex_value_low) OVER (partition BY include_exclude_indicator ORDER BY flex_value_rule_id, f
    lex_value_low) next_excl, flex_value_high lst_excl
    9 FROM fnd_flex_value_rule_lines
    10 WHERE include_exclude_indicator = 'E'
    11 ORDER BY flex_value_rule_id)
    12 WHERE lst_excl <> (SELECT MAX(flex_value_high) FROM fnd_flex_value_rule_lines);
    13 BEGIN
    14 FOR i IN get_incl_vals loop
    15 FOR j IN 1..i.diff loop
    16 incl_temp:=i.next_excl - j;
    17 dbms_output.put_line(i.rule_id||' '||incl_temp);
    18 END loop;
    19 END loop;
    20 END;
    21 /
    10 7
    11 5
    14 2
    14 21
    14 26
    14 25
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> CREATE VIEW test123
    2 AS SELECT flex_value_rule_id rul, include_exclude_indicator typ, flex_value_low low, flex_value_high hig
    3 FROM fnd_flex_value_rule_lines;
     
    VIEW created.
     
    SQL>
    SQL>
    SQL> WITH A1 AS ( SELECT RUL, TYP,
    2 TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )) LOW ,
    3 TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
    4 FROM TEST123 WHERE TYP ='I' ) ,
    5 B1 AS ( SELECT LEVEL L
    6 FROM dual
    7 CONNECT BY LEVEL <= (SELECT MAX ( HIG) FROM A1 )
    8 AND LEVEL >= (SELECT MIN (LOW) FROM A1 ) )
    9 SELECT RUL, l NUM FROM A1, B1
    10 WHERE b1.l BETWEEN a1.LOW AND a1.HIG
    11 MINUS
    12 SELECT RUL, l FROM
    13 ( SELECT RUL, TYP,
    14 TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )) LOW ,
    15 TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
    16 FROM TEST123 WHERE TYP ='E' )A2,
    17 ( SELECT level l
    18 FROM dual
    19 CONNECT BY LEVEL <= (SELECT MAX( TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )))
    20 FROM TEST123
    21 WHERE TYP ='E' )
    22 AND LEVEL >= (SELECT MIN( TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )))
    23 FROM TEST123
    24 WHERE TYP ='E' ) ) B2
    25 WHERE b2.l BETWEEN a2.LOW AND a2.HIG;
    RUL NUM
    ---------- ----------
    10 7
    11 5
    14 2
    14 21
    14 25
    14 26
     
    6 ROWS selected.
     
    SQL>
    SQL> UPDATE fnd_flex_value_rule_lines
    2 SET flex_value_high = 'QQ' WHERE flex_value_high = 'ZZ';
     
    6 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> DECLARE
    2
    3 incl_temp NUMBER;
    4 cursor get_incl_vals IS
    5 SELECT rule_id, to_number(lst_excl) lst_excl, to_number(next_excl) next_excl,
    6 (to_number(next_excl) - to_number(lst_excl) - 1) diff
    7 FROM
    8 (SELECT flex_value_rule_id rule_id, lead(flex_value_low) OVER (partition BY include_exclude_indicator ORDER BY flex_value_rule_id, f
    lex_value_low) next_excl, flex_value_high lst_excl
    9 FROM fnd_flex_value_rule_lines
    10 WHERE include_exclude_indicator = 'E'
    11 ORDER BY flex_value_rule_id)
    12 WHERE lst_excl <> (SELECT MAX(flex_value_high) FROM fnd_flex_value_rule_lines);
    13 BEGIN
    14 FOR i IN get_incl_vals loop
    15 FOR j IN 1..i.diff loop
    16 incl_temp:=i.next_excl - j;
    17 dbms_output.put_line(i.rule_id||' '||incl_temp);
    18 END loop;
    19 END loop;
    20 END;
    21 /
    10 7
    11 5
    14 2
    14 21
    14 26
    14 25
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> WITH A1 AS ( SELECT RUL, TYP,
    2 TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )) LOW ,
    3 TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
    4 FROM TEST123 WHERE TYP ='I' ) ,
    5 B1 AS ( SELECT LEVEL L
    6 FROM dual
    7 CONNECT BY LEVEL <= (SELECT MAX ( HIG) FROM A1 )
    8 AND LEVEL >= (SELECT MIN (LOW) FROM A1 ) )
    9 SELECT RUL, l NUM FROM A1, B1
    10 WHERE b1.l BETWEEN a1.LOW AND a1.HIG
    11 MINUS
    12 SELECT RUL, l FROM
    13 ( SELECT RUL, TYP,
    14 TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )) LOW ,
    15 TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
    16 FROM TEST123 WHERE TYP ='E' )A2,
    17 ( SELECT level l
    18 FROM dual
    19 CONNECT BY LEVEL <= (SELECT MAX( TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )))
    20 FROM TEST123
    21 WHERE TYP ='E' )
    22 AND LEVEL >= (SELECT MIN( TO_NUMBER(DECODE( LOW ,'AA', 0,LOW )))
    23 FROM TEST123
    24 WHERE TYP ='E' ) ) B2
    25 WHERE b2.l BETWEEN a2.LOW AND a2.HIG;
    TO_NUMBER(DECODE( HIG ,'ZZ', 100,HIG )) HIG
    *
    ERROR at line 3:
    ORA-01722: invalid NUMBER
     
    SQL>
    I'm not making any attempt to compete with anyone, I simply want to provide a generic solution that would work with any maximum value found in the source table.
     
    Bharat likes this.