I have created a view as below:
REPLACE VIEW <viewdbname>.<viewname> (COL1, COL2, COL3, COL4, COL5, COL6) AS
SELECT COL1 ,
COLumn2 AS COL2,
COLumn3 AS COL3,
COUNT(*) AS COL4,
MAX(CAST(Column5 AS INTEGER)) AS COL5,
MAX(CAST(Column6 AS INTEGER)) AS COL6
SELECT Column2 || '_' || Column3 AS COL1,
POSITION('_' IN column1) AS pos1,
SUBSTR(column1 , 1 , pos1-1) AS Column2,
SUBSTRING(column1 FROM pos1+1 FOR CHARACTER(name)) AS substr1,
POSITION('_' IN substr1) AS pos2,
SUBSTRING(substr1 FROM 1 FOR pos2-1) AS Column3,
SUBSTRING(substr1 FROM pos2+1 FOR CHARACTER(substr1)) AS substr2,
POSITION('_' IN substr2) AS pos3,
SUBSTRING(substr2 FROM 1 FOR pos3-1) AS Column5,
SUBSTRING(substr2 FROM pos3+1 FOR CHARACTER(substr2)) AS Column6
GROUP BY 1, 2, 3;
Using this view, when i perform a "SELECT * FROM <viewdbname>.<viewname>" all the rows are getting displayed. But when i give a WHERE condition like "SELECT * FROM <viewdbname>.<viewname> WHERE COL1 = 'x_yyyy' " on this same view , then i am getting the error: "SELECT Failed. 2663: SUBSTR: string subscript out of bounds in <tablename>.column1".
Even the SELECT part in the view with the WHERE condition as above works fine but when the same WHERE condition is applied on the view, then we are getting the above stated error.
I modified the above query to split the column data using STRTOK and it worked fine but i wanted to understand why the SELECT with WHERE condition on the view is giving 2663 error.
Sample data in column1 column in <dbname>.<tablename> is as below:
Please tell me how the query works differently in a SELECT on a table and the same query with SELECT on a view (causing error) when used with a WHERE condition.