I have created this function:
CREATE FUNCTION f_check_code_tables ()
RETURNS integer
BEGIN
DECLARE v_table_name varchar(30);
DECLARE i_total_rows integer;
DECLARE i_table_rows integer;

DECLARE code_control CURSOR FOR
SELECT table_name,
nbr_mandatory_rows + nbr_user_defined_rows
AS total_rows
FROM a1_code_control;

OPEN code_control;
code_control_loop:
LOOP
FETCH NEXT code_control INTO v_table_name,
i_total_rows;
IF (SQLCODE = 100) THEN
LEAVE code_control_loop;
END IF;

IF (v_table_name = 'a1_translation') THEN
BEGIN
SELECT COUNT(*)
INTO i_table_rows
FROM dba.v_table_name
WHERE language = 'EN';
IF (@@error <> 0) THEN
SELECT -1
FROM sys.dummy;
RETURN(-1);
END IF;
END;

ElSE

BEGIN
SELECT COUNT(*)
INTO i_table_rows
FROM dba.v_table_name;
IF (@@error <> 0) THEN
SELECT -1
FROM sys.dummy;
RETURN(-1);
END IF;
END;

END IF;

END LOOP code_control_loop;

CLOSE code_control;

RETURN(1);

END
;

When I test this Function by: exec f_check_code_tables ;
I get the following results:
Could not execute statement.
Table 'v_table_name' not found
SQLCODE=-141, ODBC 3 State="42S02"
Line 1, column 1
(Continuing after error)
The table I am testing DOES exist; I think that I am not
passing a parameter from the cursor to my SQL correctly.
Please advise.
Murray Sobol

You're using v_table_name for both a table and a variable. Try rewriting the
function with a different name for that variable.

Richard

<murray_sobol (AT) dbcsmartsoftware (DOT) com> wrote

Murray,

if you want to use a table name that is not hard-coded but given as a
parameter or read from some table, you have to use dynamic SQL. Cf. the
EXECUTE IMMEDIATE statement, such as

EXECUTE IMMEDIATE WITH RESULTSET ON
'SELECT COUNT(*) FROM ' || myTableName;

v_table_name might be such a table name parameter.

HTH
Volker

murray_sobol (AT) dbcsmartsoftware (DOT) com schrieb:

I changed the Function as follows:
IF (v_table_name = 'a1_translation') THEN
BEGIN
EXECUTE IMMEDIATE WITH RESULT SET ON 'SELECT
COUNT(*) INTO i_table_rows FROM ' || v_table_name ;
IF (@@error <> 0) THEN
RETURN(-1);
END IF;
END;

ElSE

BEGIN
EXECUTE IMMEDIATE WITH RESULT SET ON 'SELECT
COUNT(*) INTO i_table_rows FROM ' || v_table_name ;
IF (@@error <> 0) THEN
RETURN(-1);
END IF;
END;

END IF;

IF (i_total_rows <> i_table_rows) THEN
Print('Table_Name= ' || v_table_name);
Print('Expected Nbr of Rows= ' || i_total_rows);
Print('Actual Nbr of Rows= ' || i_table_rows);
RETURN(-99);
END IF;

It now compiles with no errors.
When I test it using this SQL:
exec f_check_code_tables;
I get this error:
Could not execute statement.
Variable 'i_table_rows' not found
SQLCODE=-260, ODBC 3 State="42000"
Line 1, column 1
(Continuing after error)
Also, in the first condition, I need to add the clause:
'WHERE language = 'EN'' to the SQL statement.
I have tried various combinations, but all produce compile
errors.
Please advise.
Murray

Try this:

CREATE FUNCTION f_check_code_tables()
RETURNS integer
BEGIN
DECLARE i_table_rows integer;

for l1 as code_control cursor for
SELECT table_name as v_table_name,
nbr_mandatory_rows + nbr_user_defined_rows
AS i_total_rows
FROM a1_code_control
do
execute immediate
with result set off
'SELECT COUNT(*) INTO i_table_rows FROM DBA.' || v_table_name ||
if v_table_name = 'a1_translation' then
' WHERE language = ''EN'''
endif;
IF (@@error <> 0) THEN
RETURN(-1);
END IF;
end for;
RETURN(1);
END

I'm not sure what checking @@error is intended to do.

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




murray_sobol (AT) dbcsmartsoftware (DOT) com wrote: