2663 Issue with SUBSTR usage in a view

Database
Enthusiast

2663 Issue with SUBSTR usage in a view

Hi All,

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

           FROM

                        (

                                                SELECT  Column2 || '_' || Column3 AS COL1,

                                                               Column2,

                                                               Column3,

                                                               Column5,

                                                               Column6

                                                FROM

                                                (

                                                                         SELECT    Column1,

                                                                                         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

                                                                         FROM  <dbname>.<tablename>

                                                ) a

                        ) b

   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:

x_abcdef_4_0

d_3456678_0_0

d_234_1_1

x_yyyy_1_5

g_5678_15_8

n_888854_3_0

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.