Select the records having column value length less than 7 satisfying criteria - Oracle

user_14734627 0
Viewed 302

I have a table TABLE1 having NAME & CODE columns and I have table TABLE2 having NAME, COLUMN1 and COLUMN2 columns. We need to select a records from TABLE1 having the CODE column value which should be between the values of COLUMN1 and COLUMN2 columns.

If the CODE value length is greater than or equal to 7 then we need to check "seventh character" of CODE value

a) If the seventh character is D or S then don't select the record

b) If the seventh character is not D or S ,then check value of CODE column whether the value is between the values of COLUMN1 and COLUMN2 columns of TABLE2, if yes then select the record

If the CODE value length is less than 7, then we need to check whether the CODE value is between the values of COLUMN1 and COLUMN2 columns. If yes, select the record from TABLE1 else don't select

 CREATE TABLE TABLE1 (NAME VARCHAR2(6), CODE VARCHAR2(10));
 
 INSERT INTO TABLE1 VALUES ('JOHN', 'K062');
 INSERT INTO TABLE1 VALUES ('JEFF', 'K08117');
 INSERT INTO TABLE1 VALUES ('KATE', 'K08419');
 INSERT INTO TABLE1 VALUES ('KIWI', 'M991011');
 INSERT INTO TABLE1 VALUES ('TARA', 'S12312D');
 INSERT INTO TABLE1 VALUES ('SOMA', 'T3499XS');
 INSERT INTO TABLE1 VALUES ('RAMA', 'Z043');
 INSERT INTO TABLE1 VALUES ('GEET', '1234567');

 CREATE TABLE TABLE2 (NAME VARCHAR2(6), COLUMN1 VARCHAR2(10), COLUMN2 VARCHAR2(10));
 
 INSERT INTO TABLE2 VALUES ('JOHN', 'K062', 'K062');
 INSERT INTO TABLE2 VALUES ('JEFF', 'K08111', 'K08119');
 INSERT INTO TABLE2 VALUES ('KATE', 'K08419', 'K08419');
 INSERT INTO TABLE2 VALUES ('KIWI', 'M991010', 'M991010');
 INSERT INTO TABLE2 VALUES ('TARA', 'S0000XA', 'S99912S');
 INSERT INTO TABLE2 VALUES ('SOMA', 'T07', 'T3499XS');
 INSERT INTO TABLE2 VALUES ('RAMA', 'Z041', 'Z043');
 INSERT INTO TABLE2 VALUES ('GEET', '1234567', '1234567');

This is the query I have written,

SELECT T1.NAME, T1.CODE
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.NAME = T2.NAME
WHERE SUBSTR(T1.CODE,7,1) NOT IN ('D', 'S')
AND T1.CODE BETWEEN T2.COLUMN1 AND T2.COLUMN2;

If I query above, I was getting other result, what would be the query to get below result?

The result I got:

 NAME        CODE
 -----------------
 GEET        1234567

The result needs to be:

   NAME            CODE
   --------------------
   JOHN            K062
   JEFF            K08117
   KATE            K08419
   RAMA            Z043
   GEET            1234567
oracle11g oracle sql
1 Answers

For rows with a code shorter than 7 characters, the substr will return null. This causes these to be excluded from the results.

To include them, have a null check:

SELECT T1.NAME, T1.CODE
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.NAME = T2.NAME
WHERE ( 
  SUBSTR(T1.CODE,7,1) NOT IN ('D', 'S') OR  
  SUBSTR(T1.CODE,7,1) IS NULL 
)
AND T1.CODE BETWEEN T2.COLUMN1 AND T2.COLUMN2;

NAME    CODE      
JOHN    K062       
JEFF    K08117     
KATE    K08419     
RAMA    Z043       
GEET    1234567
user_14859550
Related