SQL Embedded in RPG

Hi


I am developing an RPG program (embedded with SQL) to list IFS files names
that contain a specific string.

I am using the IFS_OBJECT_STATISTICS table function.


I must be making a rookie mistake while composing the SQL string & I cannot
identify what it is.

I am getting SQLSTATE = '42703' with SQLCOD = -206 after Prepare and
Declare.

Based on my research it means "An undefined column or parameter name was
detected."


Here is my SQL string:

Select Path_Name

From Table(Qsys2.IFS_Object_Statistics(

Start_Path_Name => '/i3pl/CD/arc/',
Subtree_Directories => 'YES',
Object_Type_List => '*ALLSTMF')) X
Where Lower(Get_Clob_From_File(Path_Name)) like '%00353970985316338146%'



MY_RAW_SQL_STR =
....5...10...15...20...25...30...35...40...45...50...55...60
1 'Select Path_Name From Table(Qsys2.IFS_Object_Statistics(Star'
61 't_Path_Name => '/i3pl/CD/arc/', Subtree_Directories => 'YES''
121 ', Object_Type_List => '*ALLSTMF')) X Where Lower(Get_Clob_Fr'
181 'om_File(Path_Name)) like '%00353970985316338146%' '
241 ' '
301 ' '
361 ' '
421 ' '
481 ' '

After Prepare and Declare ... An undefined column or parameter name was
detected.
SQLSTATE = '42703'
SQLCOD = -000000206.
Class Code 42: Syntax Error or Access Rule Violation
42703 An undefined column or parameter name was detected.

After Close ...
SQLSTATE = '24501'
SQLCOD = -000000501.

After Open ...
SQLSTATE = '26501'
SQLCOD = -000000514.

Class Code 26: Invalid SQL Statement Identifier
26501 The statement identified does not exist.

After Fetch
SQLSTATE = '24501'
SQLCODE = -501
Class Code 24: Invalid Cursor State
24501 The identified cursor is not open.


exec SQL
Set Option Commit = *Chg;

My_Raw_SQL_Str =
'Select Path_Name ' +
'From Table(' +
'Qsys2.IFS_Object_Statistics(' +
'Start_Path_Name => ' +
Quote + Source_Dir + Quote +
', Subtree_Directories => ' +
Quote + 'YES' + Quote +
', Object_Type_List => ' +
Quote + '*ALLSTMF' + Quote +
')' +
') X ' +
'Where Lower(Get_Clob_From_File(Path_Name)) like ' +
Quote + '%' + %Trim(The_Search) + '%' + Quote;

exec SQL
Prepare Dynamic_SQL_Stmt From My_Raw_SQL_Str;

exec SQL
Declare The_Cursor Cursor for Dynamic_SQL_Stmt;

exec SQL
Close The_Cursor;

exec SQL
Open The_Cursor;

doW 1 = 1;

Clear Full_File_Path;

exec SQL
Fetch Next From The_Cursor Into :Full_File_Path;

if %Subst(SQLSTATE : 1 : 2) = '00';


----
Regards,
Mohan Eashver