Home » SQL & PL/SQL » SQL & PL/SQL » Putting Various Logic in a Query. (Oracle 9.2, HP-UX)
Putting Various Logic in a Query. [message #283570] Tue, 27 November 2007 08:42 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi All,

I've to update a column value after putting some delimeter in that.For that I've written a procedure which is successfully running.

I'm curious to do this job by a single SQL. I've tried Case and Decode but not able to put the correct logic.
Hence not succeded. Can anyone show me how to do this. I'm using oracle 9.2 under HP-UX environment.


Scenario

Create Table Cinfo(Sno Number(3),Cno Varchar2());

Insert into Cinfo values(1,'81YBQQ040645-001PT');
Insert into Cinfo values(1,'49GP551PT');
Insert into Cinfo values(1,'79HCQA041791-002PT');
Insert into Cinfo values(1,'27GM3657PT');
Insert into Cinfo values(1,'02KADB202183-000GTEW');

Commit;


Logic to be implemented
--------------------------


2nd Delimeter (Note: 2nd Delimeter has to put first, then 1st Delimeter)--------------
Start at Position 3 of the CNO Scan right until the first numeric is found, Place delimiter before the numeric

1st Delimeter-------------
Get character count before the 2nd delimiter, Should be equal to 6, 5,4 or 3
1. If character count =3, insert after character 1 of the CNO
2. If character count=4 AND all 4 are alphabetic, insert a delimiter before the first character
3. If character count =4 AND one of 4 characters is numeric insert a delimiter after character 2
4. If character count=5, insert after character 1 of the CNO
5. If character count =6, insert after character 2 of the CNO

3rd Delimeter-------------
1. Start at the 2nd delimiter
2. Scan right until a '-' or next Alphabet character is reached.
3. REPLACE the '-' with a delimiter OR insert delimiter before the Alpha character CNO suffix is optional
and always prefaced with '-' and next 2-3 numeric characters are the suffix.

4th Delimeter-------------
Start with the 3rd delimiter Scan right until the next Alphabet character. Insert delimiter before that alpha character.
If no CNO suffix is present, the 3rd and 4th delimiters will be consecutive

5th Delimeter-------------
Start at the 4th delimiter Scan right until a numeric character is reached. Insert delimiter before that numeric character.
If the CNO end is reached before a numeric, no delimiter is inserted

Result Expected-----------------

select CNO from Cinfo;

CNO
----
81/YBQQ/040645/001/PT
49/GP/551//PT
79/HCQA/041791/002/PT
27/GM/3657//PT
02/KADB/202183/000/GTEW


Thanks in Advance.
Re: Putting Various Logic in a Query. [message #283640 is a reply to message #283570] Tue, 27 November 2007 16:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is a start. I ran out of time, boss needs work done. You get the idea thought right? Use the with clause to let you build incrementally. Sometimes you need to do a step in which you "extract" extra information out of your dataset so you can continue.

Good luck, Kevin

with
     byteinumerator as (
                         select rownum charpos
                         from dual
                         connect by level <= (select max(length(cno)) from cinfo)
                       )
    ,second_delimiter as (
                           select cinfo.*
                                 ,min(
                                      case
                                           when substr(cinfo.cno,byteinumerator.charpos,1) in ('0','1','2','3','4','5','6','7','8','9') then byteinumerator.charpos
                                      end
                                     ) second_delimiter_pos
                           from cinfo
                               ,byteinumerator
                           where byteinumerator.charpos between 3 and length(cinfo.cno)
                           group by cinfo.sno,cinfo.cno
                         )
    ,second_delimiter_loaded as (
                                    select second_delimiter.*
                                          ,substr(second_delimiter.cno,1,second_delimiter.second_delimiter_pos)||'/'||substr(second_delimiter.cno,second_delimiter.second_delimiter_pos) cno_2
                                    from second_delimiter
                                 )
   ,first_delimiter_load as (
                              select second_delimiter_loaded.*
                                    ,case
                                          when second_delimiter_loaded.second_delimiter_pos = 4 then substr(cno_2,1,1)||'/'||substr(cno_2,2)
                                          when second_delimiter_loaded.second_delimiter_pos = 5 and substr(cno_2,1,1) not in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                and substr(cno_2,1,2) not in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                and substr(cno_2,1,3) not in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                and substr(cno_2,1,4) not in ('0','1','2','3','4','5','6','7','8','9') then '/'||cno_2
                                          when second_delimiter_loaded.second_delimiter_pos = 5 and (
                                                                                                      substr(cno_2,1,1) in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                or    substr(cno_2,1,2) in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                or    substr(cno_2,1,3) in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                or    substr(cno_2,1,4) in ('0','1','2','3','4','5','6','7','8','9')
                                                                                                    ) then substr(cno_2,2,1)||'/'||substr(cno_2,3)
                                          when second_delimiter_loaded.second_delimiter_pos = 6 then substr(cno_2,1,1)||'/'||substr(cno_2,2)
                                          when second_delimiter_loaded.second_delimiter_pos = 7 then substr(cno_2,1,2)||'/'||substr(cno_2,3)
                                    end cno_1
                              from second_delimiter_loaded
                            )
select *
from first_delimiter_load
/


results for this snippet above

       SNO CNO                            SECOND_DELIMITER_POS CNO_2                                                         CNO_1
---------- ------------------------------ -------------------- ------------------------------------------------------------- ---------------------------
         1 49GP551PT                                         5 49GP5/551PT                                                   9/GP5/551PT
         1 27GM3657PT                                        5 27GM3/3657PT                                                  7/GM3/3657PT
         1 79HCQA041791-002PT                                7 79HCQA0/041791-002PT                                          79/HCQA0/041791-002PT
         1 81YBQQ040645-001PT                                7 81YBQQ0/040645-001PT                                          81/YBQQ0/040645-001PT
         1 02KADB202183-000GTEW                              7 02KADB2/202183-000GTEW                                        02/KADB2/202183-000GTEW

5 rows selected.
Re: Putting Various Logic in a Query. [message #283655 is a reply to message #283640] Tue, 27 November 2007 20:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
I am thinking that there is probably a better way to do this with regular expressions, but here is a method with translate and substr and instr and decode and nvl2 and inline views. Since this seems to be all about characters versus numbers, the method was to create a string that translated all numbers to 9's and all characters to A's, so that a substr of the original based on instr of the translation could be used.


-- test data:
SCOTT@orcl_11g> SELECT * FROM cinfo
  2  /

       SNO CNO
---------- -------------------------
         1 81YBQQ040645-001PT
         1 49GP551PT
         1 79HCQA041791-002PT
         1 27GM3657PT
         1 02KADB202183-000GTEW


-- solution:
SCOTT@orcl_11g> SELECT cno1to4
  2  	      || NVL2 (SUBSTR (cno5to6, 1, INSTR (cnoa95to6, '9') - 1), '/', NULL)
  3  	      || SUBSTR (cno5to6, 1, INSTR (cnoa95to6, '9') - 1)
  4  	      || '/'
  5  	      || SUBSTR (cno5to6, INSTR (cnoa95to6, '9')) AS cno
  6  FROM   (SELECT cno1to3 || '/'
  7  		      || SUBSTR (cno4to6, 1, INSTR (cnoa94to6, 'A') - 1) AS cno1to4,
  8  		    SUBSTR (cno4to6, INSTR (cnoa94to6, 'A')) AS cno5to6,
  9  		    SUBSTR (cnoa94to6, INSTR (cnoa94to6, 'A')) AS cnoa95to6
 10  	     FROM   (SELECT SUBSTR
 11  			      (cno1to2, 1,
 12  			       DECODE
 13  				 (LENGTH (cno1to2),
 14  				  3, 1,
 15  				  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 16  				  5, 1,
 17  				  6, 2))
 18  			    || '/' ||
 19  			    SUBSTR
 20  			      (cno1to2,
 21  			       DECODE
 22  				 (LENGTH (cno1to2),
 23  				  3, 1,
 24  				  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 25  				  5, 1,
 26  				  6, 2) + 1)
 27  			    || '/' ||
 28  			    SUBSTR
 29  			      (cno3to6, 1,
 30  			       DECODE (INSTR (cno3to6, '-'),
 31  				 0, INSTR (cnoa93to6, 'A') - 1,
 32  				 INSTR (cno3to6, '-') - 1)) AS cno1to3,
 33  			    SUBSTR
 34  			      (cno3to6,
 35  			       DECODE (INSTR (cno3to6, '-'),
 36  				 0, INSTR (cnoa93to6, 'A'),
 37  				 INSTR (cnO3to6, '-') + 1)) AS cno4to6,
 38  			    SUBSTR
 39  			      (cnoa93to6,
 40  			       DECODE (INSTR (cnoa93to6, '-'),
 41  				 0, INSTR (cnoa93to6, 'A'),
 42  				 INSTR (cnOa93to6, '-') + 1)) AS cnoa94to6
 43  		     FROM   (SELECT SUBSTR (cno, 1, INSTR (cnoa9, '9', 3) - 1)	 AS cno1to2,
 44  				    SUBSTR (cno, INSTR (cnoa9, '9', 3)) 	 AS cno3to6,
 45  				    SUBSTR (cnoa9, 1, INSTR (cnoa9, '9', 3) - 1) AS cnoa91to2,
 46  				    SUBSTR (cnoa9, INSTR (cnoa9, '9', 3))	 AS cnoa93to6
 47  			     FROM   (SELECT cno,
 48  					    TRANSLATE
 49  					      (TRANSLATE (UPPER (cno), '1234567890', '9999999999'),
 50  					      'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'AAAAAAAAAAAAAAAAAAAAAAAAAA')
 51  					      AS cnoa9
 52  				     FROM   cinfo))))
 53  /

CNO
--------------------------------------------------------------------------------
81/YBQQ/040645/001/PT
49/GP/551//PT
79/HCQA/041791/002/PT
27/GM/3657//PT
02/KADB/202183/000/GTEW

SCOTT@orcl_11g> 


-- step by step breakdown:
SCOTT@orcl_11g> -- represent as alphanumeric using A's and 9's:
SCOTT@orcl_11g> COLUMN cno   FORMAT A25
SCOTT@orcl_11g> COLUMN cnoa9 FORMAT A25
SCOTT@orcl_11g> SELECT cno,
  2  	    TRANSLATE
  3  	      (TRANSLATE (UPPER (cno), '1234567890', '9999999999'),
  4  	      'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'AAAAAAAAAAAAAAAAAAAAAAAAAA')
  5  	      AS cnoa9
  6  FROM   cinfo
  7  /

CNO                       CNOA9
------------------------- -------------------------
81YBQQ040645-001PT        99AAAA999999-999AA
49GP551PT                 99AA999AA
79HCQA041791-002PT        99AAAA999999-999AA
27GM3657PT                99AA9999AA
02KADB202183-000GTEW      99AAAA999999-999AAAA

SCOTT@orcl_11g> -- 2nd delimiter:
SCOTT@orcl_11g> COLUMN cno1to2	 FORMAT A15
SCOTT@orcl_11g> COLUMN cno3to6	 FORMAT A15
SCOTT@orcl_11g> COLUMN cnoa91to2 FORMAT A15
SCOTT@orcl_11g> COLUMN cnoa93to6 FORMAT A15
SCOTT@orcl_11g> SELECT SUBSTR (cno, 1, INSTR (cnoa9, '9', 3) - 1)   AS cno1to2,
  2  	    SUBSTR (cno, INSTR (cnoa9, '9', 3)) 	 AS cno3to6,
  3  	    SUBSTR (cnoa9, 1, INSTR (cnoa9, '9', 3) - 1) AS cnoa91to2,
  4  	    SUBSTR (cnoa9, INSTR (cnoa9, '9', 3))	 AS cnoa93to6
  5  FROM   (SELECT cno,
  6  		    TRANSLATE
  7  		      (TRANSLATE (UPPER (cno), '1234567890', '9999999999'),
  8  		      'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'AAAAAAAAAAAAAAAAAAAAAAAAAA')
  9  		      AS cnoa9
 10  	     FROM   cinfo)
 11  /

CNO1TO2         CNO3TO6         CNOA91TO2       CNOA93TO6
--------------- --------------- --------------- ---------------
81YBQQ          040645-001PT    99AAAA          999999-999AA
49GP            551PT           99AA            999AA
79HCQA          041791-002PT    99AAAA          999999-999AA
27GM            3657PT          99AA            9999AA
02KADB          202183-000GTEW  99AAAA          999999-999AAAA

SCOTT@orcl_11g> -- 1st and 3rd delimiters:
SCOTT@orcl_11g> COLUMN cno1to3	 FORMAT A15
SCOTT@orcl_11g> COLUMN cno4to6	 FORMAT A15
SCOTT@orcl_11g> COLUMN cnoa94to6 FORMAT A15
SCOTT@orcl_11g> SELECT SUBSTR
  2  	      (cno1to2, 1,
  3  	       DECODE
  4  		 (LENGTH (cno1to2),
  5  		  3, 1,
  6  		  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
  7  		  5, 1,
  8  		  6, 2))
  9  	    || '/' ||
 10  	    SUBSTR
 11  	      (cno1to2,
 12  	       DECODE
 13  		 (LENGTH (cno1to2),
 14  		  3, 1,
 15  		  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 16  		  5, 1,
 17  		  6, 2) + 1)
 18  	    || '/' ||
 19  	    SUBSTR
 20  	      (cno3to6, 1,
 21  	       DECODE (INSTR (cno3to6, '-'),
 22  		 0, INSTR (cnoa93to6, 'A') - 1,
 23  		 INSTR (cno3to6, '-') - 1)) AS cno1to3,
 24  	    SUBSTR
 25  	      (cno3to6,
 26  	       DECODE (INSTR (cno3to6, '-'),
 27  		 0, INSTR (cnoa93to6, 'A'),
 28  		 INSTR (cnO3to6, '-') + 1)) AS cno4to6,
 29  	    SUBSTR
 30  	      (cnoa93to6,
 31  	       DECODE (INSTR (cnoa93to6, '-'),
 32  		 0, INSTR (cnoa93to6, 'A'),
 33  		 INSTR (cnOa93to6, '-') + 1)) AS cnoa94to6
 34  FROM   (SELECT SUBSTR (cno, 1, INSTR (cnoa9, '9', 3) - 1)	 AS cno1to2,
 35  		    SUBSTR (cno, INSTR (cnoa9, '9', 3)) 	 AS cno3to6,
 36  		    SUBSTR (cnoa9, 1, INSTR (cnoa9, '9', 3) - 1) AS cnoa91to2,
 37  		    SUBSTR (cnoa9, INSTR (cnoa9, '9', 3))	 AS cnoa93to6
 38  	     FROM   (SELECT cno,
 39  			    TRANSLATE
 40  			      (TRANSLATE (UPPER (cno), '1234567890', '9999999999'),
 41  			      'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'AAAAAAAAAAAAAAAAAAAAAAAAAA')
 42  			      AS cnoa9
 43  		     FROM   cinfo))
 44  /

CNO1TO3         CNO4TO6         CNOA94TO6
--------------- --------------- ---------------
81/YBQQ/040645  001PT           999AA
49/GP/551       PT              AA
79/HCQA/041791  002PT           999AA
27/GM/3657      PT              AA
02/KADB/202183  000GTEW         999AAAA

SCOTT@orcl_11g> -- 4th delimiter:
SCOTT@orcl_11g> COLUMN cno1to4	 FORMAT A25
SCOTT@orcl_11g> COLUMN cno5to6	 FORMAT A25
SCOTT@orcl_11g> COLUMN cnoa95to6 FORMAT A25
SCOTT@orcl_11g> SELECT cno1to3 || '/'
  2  	      || SUBSTR (cno4to6, 1, INSTR (cnoa94to6, 'A') - 1) AS cno1to4,
  3  	    SUBSTR (cno4to6, INSTR (cnoa94to6, 'A')) AS cno5to6,
  4  	    SUBSTR (cnoa94to6, INSTR (cnoa94to6, 'A')) AS cnoa95to6
  5  FROM   (SELECT SUBSTR
  6  		      (cno1to2, 1,
  7  		       DECODE
  8  			 (LENGTH (cno1to2),
  9  			  3, 1,
 10  			  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 11  			  5, 1,
 12  			  6, 2))
 13  		    || '/' ||
 14  		    SUBSTR
 15  		      (cno1to2,
 16  		       DECODE
 17  			 (LENGTH (cno1to2),
 18  			  3, 1,
 19  			  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 20  			  5, 1,
 21  			  6, 2) + 1)
 22  		    || '/' ||
 23  		    SUBSTR
 24  		      (cno3to6, 1,
 25  		       DECODE (INSTR (cno3to6, '-'),
 26  			 0, INSTR (cnoa93to6, 'A') - 1,
 27  			 INSTR (cno3to6, '-') - 1)) AS cno1to3,
 28  		    SUBSTR
 29  		      (cno3to6,
 30  		       DECODE (INSTR (cno3to6, '-'),
 31  			 0, INSTR (cnoa93to6, 'A'),
 32  			 INSTR (cnO3to6, '-') + 1)) AS cno4to6,
 33  		    SUBSTR
 34  		      (cnoa93to6,
 35  		       DECODE (INSTR (cnoa93to6, '-'),
 36  			 0, INSTR (cnoa93to6, 'A'),
 37  			 INSTR (cnOa93to6, '-') + 1)) AS cnoa94to6
 38  	     FROM   (SELECT SUBSTR (cno, 1, INSTR (cnoa9, '9', 3) - 1)	 AS cno1to2,
 39  			    SUBSTR (cno, INSTR (cnoa9, '9', 3)) 	 AS cno3to6,
 40  			    SUBSTR (cnoa9, 1, INSTR (cnoa9, '9', 3) - 1) AS cnoa91to2,
 41  			    SUBSTR (cnoa9, INSTR (cnoa9, '9', 3))	 AS cnoa93to6
 42  		     FROM   (SELECT cno,
 43  				    TRANSLATE
 44  				      (TRANSLATE (UPPER (cno), '1234567890', '9999999999'),
 45  				      'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'AAAAAAAAAAAAAAAAAAAAAAAAAA')
 46  				      AS cnoa9
 47  			     FROM   cinfo)))
 48  /

CNO1TO4                   CNO5TO6                   CNOA95TO6
------------------------- ------------------------- -------------------------
81/YBQQ/040645/001        PT                        AA
49/GP/551/                PT                        AA
79/HCQA/041791/002        PT                        AA
27/GM/3657/               PT                        AA
02/KADB/202183/000        GTEW                      AAAA

SCOTT@orcl_11g> -- 5th delimiter (final solution):
SCOTT@orcl_11g> SELECT cno1to4
  2  	      || NVL2 (SUBSTR (cno5to6, 1, INSTR (cnoa95to6, '9') - 1), '/', NULL)
  3  	      || SUBSTR (cno5to6, 1, INSTR (cnoa95to6, '9') - 1)
  4  	      || '/'
  5  	      || SUBSTR (cno5to6, INSTR (cnoa95to6, '9')) AS cno
  6  FROM   (SELECT cno1to3 || '/'
  7  		      || SUBSTR (cno4to6, 1, INSTR (cnoa94to6, 'A') - 1) AS cno1to4,
  8  		    SUBSTR (cno4to6, INSTR (cnoa94to6, 'A')) AS cno5to6,
  9  		    SUBSTR (cnoa94to6, INSTR (cnoa94to6, 'A')) AS cnoa95to6
 10  	     FROM   (SELECT SUBSTR
 11  			      (cno1to2, 1,
 12  			       DECODE
 13  				 (LENGTH (cno1to2),
 14  				  3, 1,
 15  				  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 16  				  5, 1,
 17  				  6, 2))
 18  			    || '/' ||
 19  			    SUBSTR
 20  			      (cno1to2,
 21  			       DECODE
 22  				 (LENGTH (cno1to2),
 23  				  3, 1,
 24  				  4, DECODE (INSTR (cnoa91to2, '9'), 0, 0, 2),
 25  				  5, 1,
 26  				  6, 2) + 1)
 27  			    || '/' ||
 28  			    SUBSTR
 29  			      (cno3to6, 1,
 30  			       DECODE (INSTR (cno3to6, '-'),
 31  				 0, INSTR (cnoa93to6, 'A') - 1,
 32  				 INSTR (cno3to6, '-') - 1)) AS cno1to3,
 33  			    SUBSTR
 34  			      (cno3to6,
 35  			       DECODE (INSTR (cno3to6, '-'),
 36  				 0, INSTR (cnoa93to6, 'A'),
 37  				 INSTR (cnO3to6, '-') + 1)) AS cno4to6,
 38  			    SUBSTR
 39  			      (cnoa93to6,
 40  			       DECODE (INSTR (cnoa93to6, '-'),
 41  				 0, INSTR (cnoa93to6, 'A'),
 42  				 INSTR (cnOa93to6, '-') + 1)) AS cnoa94to6
 43  		     FROM   (SELECT SUBSTR (cno, 1, INSTR (cnoa9, '9', 3) - 1)	 AS cno1to2,
 44  				    SUBSTR (cno, INSTR (cnoa9, '9', 3)) 	 AS cno3to6,
 45  				    SUBSTR (cnoa9, 1, INSTR (cnoa9, '9', 3) - 1) AS cnoa91to2,
 46  				    SUBSTR (cnoa9, INSTR (cnoa9, '9', 3))	 AS cnoa93to6
 47  			     FROM   (SELECT cno,
 48  					    TRANSLATE
 49  					      (TRANSLATE (UPPER (cno), '1234567890', '9999999999'),
 50  					      'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'AAAAAAAAAAAAAAAAAAAAAAAAAA')
 51  					      AS cnoa9
 52  				     FROM   cinfo))))
 53  /

CNO
-------------------------
81/YBQQ/040645/001/PT
49/GP/551//PT
79/HCQA/041791/002/PT
27/GM/3657//PT
02/KADB/202183/000/GTEW

SCOTT@orcl_11g> 

icon7.gif  Re: Putting Various Logic in a Query. [message #283793 is a reply to message #283655] Wed, 28 November 2007 02:58 Go to previous message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Smile
Thanks Kevin and Barbara, You guys are too good.
Barbara, I love the way you exaplain this.

Thanks a lot Guys!!
Previous Topic: Username
Next Topic: find the dependencies of the table
Goto Forum:
  


Current Time: Tue Dec 06 15:55:18 CST 2016

Total time taken to generate the page: 0.08642 seconds