TROUBLESHOOT 1 ORACLE-SQL

CASE SCENARIO

ITEM_CODE	WIDTH
XXXD080 45KMF-7MM	7

Table above shows two columns, ITEM_CODE and WIDTH.
Create new column called NEW ITEM CODE. New column shows as per
ITEM_CODE but exclude width value(MM) start from dash '-'.
Expected output is below:-
XXXD080 45KMF-7MM   becomes   ==>    XXXD080 45KMF

Current SQL statement is
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('XXXD080 45KMF-7MM')

STEP 1:
Find the part of value that we want to take out.
In this case we want to take out -7MM from ITEM_CODE XXXD080 45KMF-7MM.
Use ORACLE-SQL substr function.

ORACLE-SQL substr format is below:-
SUBSTR(string, start_position, [ length ] )

Let's see how this function works by doing statement below.

Test Statement 1
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1
XXXD080 45KMF-7MM	7	XX

Way to go...
The output only to test how substr function works but it is not what we want.
We need to find out what is actually our start position (in this case start with dash '-').

SUBSTR(A.ITEM_CODE,1,2)
Find out what we suppose to replace value '1' with?

STEP 2:
Find out the start position.
Use ORACLE-SQL instr function.

ORACLE-SQL instr format is below:-
INSTR( source_string, substring [, start_position [, occurrance ] ] )

Let's see how this instr function works by doing statement below.

Test SQL Statement 2
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
---STEP 1
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
---STEP 2
INSTR(A.ITEM_CODE,'-') AS TEST_INS1
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1
XXXD080 45KMF-7MM	7	XX	14

Instr function return value 14 which is the position of dash '-' in the string of 'XXXD080 45KMF-7MM'

XXXD080 45KMF-7MM
Dash position is 14.

How about if the item code got two dash '-'?
Example below:-
O-K205/4.0VF-17MM

Item code shows two dash '-'.
The dash '-' we want to take out is dash with width value(MM).
In this case it is the last position of dash.

If we use the same sql statement the output is below.

Test SQL Statement 2
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
---STEP 1
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
---STEP 2
INSTR(A.ITEM_CODE,'-') AS TEST_INS1
------- FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1
'O-K205/4.0VF-17MM'	17	O-	2

Instr function returns value 2 which is the position of dash '-' in string.
O-K205/4.0VF-17MM
Dash '-' is position number 2.

We need to amend the start position by searching backward.
Use sql below.

Test SQL Statement 2
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
---STEP 1
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
---STEP 2
INSTR(A.ITEM_CODE,'-',-1) AS TEST_INS1
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1
O-K205/4.0VF-17MM	17	O-	13

Instr function returns value 13 which is the position of dash '-' in string.
O-K205/4.0VF-17MM
Dash position number13 after amend the SQL statement.


INSTR(A.ITEM_CODE,'-',-1)

For detail use SQL statement below.

Test SQL Statement 2
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
--------
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
---STEP 1
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
---STEP 2
INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_INS1
-------
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM','XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1
O-K205/4.0VF-17MM	17	O-	13
XXXD080 45KMF-7MM	7	XX	14

INSTR(A.ITEM_CODE,'-',-1,1)

Instr function search backward the dash position and return the first matching sub string.

STEP 3:
Back to substr function.
We know the start position already(step2).

SUBSTR(A.ITEM_CODE,1,2)

Replace value '1' with what we have done with instr function(whole part of instr function in step 2)

Test SQL Statement 3
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
--------
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
--STEP 1.
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
--STEP 2.
INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_INS1,
--STEp 3.
SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1),2) AS TEST_SUB2
-------
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM','XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1	TEST_SUB2
O-K205/4.0VF-17MM	17	O-	13	-1
XXXD080 45KMF-7MM	7	XX	14	-7

SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1),2)
Substr function return value from expected start position.
Anyhow we still need to amend the lengh value
due we want to return all string right after start position.
This can be done by make it default(take out value 2).

SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1))

Test SQL Statement 3
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
--------
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
--STEP 1.
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
--STEP 2.
INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_INS1,
--STEp 3.
SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1)) AS TEST_SUB2
-------
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM','XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1	TEST_SUB2
O-K205/4.0VF-17MM	17	O-	13	-17MM
XXXD080 45KMF-7MM	7	XX	14	-7MM

STEP 4:
Use substr in substr to get the output.
Let's refresh what the final output we want.

Example
Item code is XXXD080 45KMF-7MM
Then we want another column show item code but without width value (-7MM)

Once again use substr function.
We want show item code from first position until find the position we want to take out.

Test SQL Statement 3
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
--------
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
--`STEP 1.
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
--STEP 2.
INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_INS1,
--STEP 3.
SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1)) AS TEST_SUB2,
--STEP 4.
SUBSTR(A.ITEM_CODE,1,INSTR(A.ITEM_CODE,'-',-1,1)) AS NEW_ITEM_CODE
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM','XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1	TEST_SUB2	NEW_ITEM_CODE
O-K205/4.0VF-17MM	17	O-	13	-17MM	O-K205/4.0VF-
XXXD080 45KMF-7MM	7	XX	14	-7MM	XXXD080 45KMF-

--STEP 4.
SUBSTR(A.ITEM_CODE,1,INSTR(A.ITEM_CODE,'-',-1,1))

SQL statement above return output below

NEW_ITEM_CODE
O-K205/4.0VF-
XXXD080 45KMF-

Anyway we do not want the dash '-'.
Amend code below
SUBSTR(A.ITEM_CODE,1,INSTR(A.ITEM_CODE,'-',-1,1)-1)

Test SQL Statement 4
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
--------
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
--STEP 1.
SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
--STEP 2.
INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_INS1,
--STEP 3.
SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_SUB2,
--STEP 4.
SUBSTR(A.ITEM_CODE,1,INSTR(A.ITEM_CODE,'-',-1,1)-1)
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM','XXXD080 45KMF-7MM')

Output:-

ITEM_CODE	WIDTH	TEST_SUB1	TEST_INS1	TEST_SUB2	NEW_ITEM_CODE
O-K205/4.0VF-17MM	17	O-	13	-17MM	O-K205/4.0VF
XXXD080 45KMF-7MM	7	XX	14	-7MM	XXXD080 45KMF

FINAL OUTPUT
Comment out step 1 to 3 column.

Test SQL Statement 4
-----------------------------------------------------------------------------------------------

SELECT
A.ITEM_CODE,
--------
DECODE(TRIM(SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'MM',-1))),'MM',
REPLACE(SUBSTR(A.ITEM_CODE,(INSTR(A.ITEM_CODE,'-',-1)+1)),'MM',''),'NO_WIDTH') AS WIDTH,
--STEP 1.
--SUBSTR(A.ITEM_CODE,1,2) AS TEST_SUB1,
--STEP 2.
--INSTR(A.ITEM_CODE,'-',-1,1) AS TEST_INS1,
--STEP 3.
--SUBSTR(A.ITEM_CODE,INSTR(A.ITEM_CODE,'-',-1,1)) AS NEW_ITEM_CODE,
--STEP 4.
SUBSTR(A.ITEM_CODE,1,INSTR(A.ITEM_CODE,'-',-1,1)-1) AS "NEW ITEM CODE"
FROM ITEM_TABLE A, PM_TABLE B
WHERE
A.F_CODE= B.I_FAC_CD(+) AND A.M_CODE = B.MAIN_CODE(+)
AND A.F_CODE= '87'
AND ITEM_CODE IN ('O-K205/4.0VF-17MM','XXXD080 45KMF-7MM')

Final Output:-

ITEM_CODE	WIDTH	NEW_ITEM_CODE
O-K205/4.0VF-17MM	17	O-K205/4.0VF
XXXD080 45KMF-7MM	7	XXXD080 45KMF

This troubleshoot is a guideline or exposure for beginner regarding SQL-ORACLE statement.
In scenario above we use SUBSTR and INSTR functions provided by ORACLE.
Enjoy your troubleshooting!