Home » SQL & PL/SQL » SQL & PL/SQL » please sole it
please sole it [message #604079] Mon, 23 December 2013 22:43 Go to next message
lavkumar78
Messages: 9
Registered: June 2013
Location: delhi
Junior Member

One column in item

ITEM_CODE
919680010
919680010-1
919680010-1/95
919680010-1/90
919680010-1/75
919680013-4/20
419560297/10
419560298/15

i want to get out put in item code thes how can i pick this item code

ITEM CODE
919680010
919680010-1
919680013-4
419560297
419560298
Re: please sole it [message #604082 is a reply to message #604079] Mon, 23 December 2013 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: please sole it [message #604090 is a reply to message #604082] Mon, 23 December 2013 23:46 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
It seems that you want to list all distinct values on the basis of '/'.

Assumptions:
1. There will be only one '/' in your data set.
2. You want distinct of all those values which are obtained before each '/' in your data set.

The below solution might help you.
WITH myTab AS
(
SELECT '919680010' item FROM dual
UNION ALL
SELECT '919680010-1' FROM dual
UNION ALL
SELECT '919680010-1/95' FROM dual
UNION ALL
SELECT '919680010-1/90' FROM dual
UNION ALL
SELECT '919680010-1/75' FROM dual
UNION ALL
SELECT '919680013-4/20' FROM dual
UNION ALL
SELECT '419560297/10' FROM dual
UNION ALL
SELECT '419560298/15' FROM dual
)
SELECT DISTINCT SUBSTR(item, 1, DECODE(INSTR(item, '/') -1, -1, LENGTH(item), INSTR(item, '/') -1)) FROM myTab;


However, this is just a solution but more optimal solutions will be provided by some other senior members
Smile
Re: please sole it [message #604091 is a reply to message #604079] Mon, 23 December 2013 23:47 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
SQL> WITH DATA AS(
  2  SELECT '919680010' ITEM_CODE FROM dual UNION ALL
  3  SELECT '919680010-1' FROM dual UNION ALL
  4  SELECT '919680010-1/95' FROM dual UNION ALL
  5  SELECT '919680010-1/90' FROM dual UNION ALL
  6  SELECT '919680010-1/75' FROM dual UNION ALL
  7  SELECT '919680013-4/20' FROM dual UNION ALL
  8  SELECT '419560297/10' FROM dual UNION ALL
  9  SELECT '419560298/15' FROM dual)
 10  SELECT CASE
 11            WHEN REGEXP_LIKE(ITEM_CODE, '-') AND REGEXP_LIKE(ITEM_CODE, '/') THEN
 12             SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
 13            WHEN REGEXP_LIKE(ITEM_CODE, '/') THEN
 14             SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
 15            ELSE
 16             ITEM_CODE
 17         END ITEM_CODE
 18    FROM DATA
 19   GROUP BY CASE
 20               WHEN REGEXP_LIKE(ITEM_CODE, '-') AND
 21                    REGEXP_LIKE(ITEM_CODE, '/') THEN
 22                SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
 23               WHEN REGEXP_LIKE(ITEM_CODE, '/') THEN
 24                SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
 25               ELSE
 26                ITEM_CODE
 27            END;
 
ITEM_CODE
--------------
919680010
419560297
919680010-1
919680013-4
419560298
Previous Topic: Select clause issue
Next Topic: Delete primary key in case of parent-child relation
Goto Forum:
  


Current Time: Thu Apr 25 01:33:07 CDT 2024