CODEWITHSUNDEEP
sqloracle-databasesplitoracleforms
user7403515
user7403515

Reputation:

Get comma separated data in oracle forms

I have a field orderno having data in format

"PUR/0012/17-18","PUR/0013/17-18"

I want to fetch distinct orderno which are separated by comma

Query I used is 

SELECT REGEXP_SUBSTR('PUR/0012/17-18','PUR/0013/17-18','[^,]+', 15, LEVEL) 
FROM dual
CONNECT BY REGEXP_SUBSTR('PUR/0012/17-18','PUR/0013/17-18', '[^,]+', 15, LEVEL) IS NOT NULL )

But it is showing error.

Upvotes: 1

Views: 157

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Two things I can think of.

The first one is obvious - query you wrote is invalid. Should be

SQL> SELECT REGEXP_SUBSTR('PUR/0012/17-18,PUR/0013/17-18','[^,]+', 1, LEVEL) result
  2  FROM dual
  3  CONNECT BY REGEXP_SUBSTR('PUR/0012/17-18,PUR/0013/17-18', '[^,]+', 1, LEVEL) IS NOT NULL;

RESULT
-----------------------------
PUR/0012/17-18
PUR/0013/17-18

SQL>

Once you fix it, but if it still doesn't work, is the fact that you didn't mention Oracle Forms version you use; maybe it is one of versions which doesn't support regular expressions. If that's the case, you should create a stored function which would return a desired result into a form. As all the calculation is performed in the database, it'll work.

Upvotes: 1

Related Questions