Hello,
I'm trying to create a view. The following SELECT works and it does what I need. However, when I try to create a view that returns the value, it fails. Any suggestion?
This works:
WITH FUNCTION getNextValue(v IN PLS_INTEGER) RETURN VARCHAR
AS
digits PLS_INTEGER;
remaining PLS_INTEGER;
secondLetterIndex PLS_INTEGER;
firstLetterIndex PLS_INTEGER;
BEGIN
digits := MOD(v, 10000);
remaining := (v - digits) / 10000;
secondLetterIndex := MOD(remaining, 26);
firstLetterIndex := FLOOR(remaining / 26);
RETURN CHR(ASCII('A') + firstLetterIndex) || CHR(ASCII('A') + secondLetterIndex) || LPAD(digits, 4, '0');
END;
SELECT getNextValue(seq.nextval) AS nextvalue
FROM dual
;
When I do CREATE VIEW view (v) with previous select it fails (sequence not allowed there).
Any idea how I can do that? Function is not mandatory as long as I get the same result... And, we never get to ZZ...
Regards