Hello All,
I am trying to do an INSTR (unless someone has a better suggestion) The reference field combines several values that I need to isolate
SELECT MS.IDENTITY,
MS.INVOICE_NO,
MS.CODE_A,
MS.REFERENCE
FROM IFSAPP.MAN_SUPP_INVOICE_POSTINGS MS
WHERE MS.CODE_A IN ('5050','5051')
RESULTS
IDENTITY INVOICE_NO CODE_A REFERENCE
1129E 153347 5051 100230431^1^1^1^
1129E 153347 5051 100230431^13^1^1^
1129E 153347 5051 100230431^28^1^1^
1129E 153347 5051 100230431^47^1^1^
1129E 153347 5051 100230431^52^1^1^
1129E 153347 5051 100230431^54^1^1^
1129E 153347 5051 100230431^57^1^1^
1129E 153347 5051 100230431^63^1^1^
If you see the reference field each of the values need to be isolated between the '^'
Order_No, Line_No, Release_No, Receipt_No
I could figure out how to get the Order_No out with a substr but the field lengths will vary, sometimes the Order_no might be 4 char or as above, 9.
Any suggestions?
I am trying to do an INSTR (unless someone has a better suggestion) The reference field combines several values that I need to isolate
SELECT MS.IDENTITY,
MS.INVOICE_NO,
MS.CODE_A,
MS.REFERENCE
FROM IFSAPP.MAN_SUPP_INVOICE_POSTINGS MS
WHERE MS.CODE_A IN ('5050','5051')
RESULTS
IDENTITY INVOICE_NO CODE_A REFERENCE
1129E 153347 5051 100230431^1^1^1^
1129E 153347 5051 100230431^13^1^1^
1129E 153347 5051 100230431^28^1^1^
1129E 153347 5051 100230431^47^1^1^
1129E 153347 5051 100230431^52^1^1^
1129E 153347 5051 100230431^54^1^1^
1129E 153347 5051 100230431^57^1^1^
1129E 153347 5051 100230431^63^1^1^
If you see the reference field each of the values need to be isolated between the '^'
Order_No, Line_No, Release_No, Receipt_No
I could figure out how to get the Order_No out with a substr but the field lengths will vary, sometimes the Order_no might be 4 char or as above, 9.
Any suggestions?