Multible inserts in one Trigger - return code -206

I have the following trigger:

--#SET TERMINATOR !
CREATE TRIGGER CROSS_REFF_TRIG
AFTER INSERT ON NEW_CATALOG
REFERENCING NEW AS nnn
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason VARCHAR(70);
DECLARE OUT_SQLCODE1 INTEGER;
CALL execute_immediate
('INSERT INTO CROSS_REFERENCE
WITH T1 (QUERY_DESCR) AS
(VALUES( ''CONVERT JOIN IN SUBSELECT - CORELLATED OR NOT CORRELATED
SUBQUERY'' )),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
''||QUERY_DESCR FROM T3,T1',OUT_SQLCODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <> 0
THEN CHAR(OUT_SQLCODE1)
ELSE NULL END;
IF reason IS NOT NULL THEN
SIGNAL SQLSTATE '7500S' (reason);
END IF;
END!

It is working perfect - doing mutible inserts in corresponding groups.

But when i replace
(VALUES( ''CONVERT JOIN IN SUBSELECT - CORELLATED OR NOT CORRELATED
SUBQUERY'' )),
on (SELECT nnn.QUERY_DESC FROM NEW_CATALOG),

The following trigger generating sqlcode -206:

--#SET TERMINATOR !
CREATE TRIGGER CROSS_REFF_TRIG
AFTER INSERT ON NEW_CATALOG
REFERENCING NEW AS nnn
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason VARCHAR(70);
DECLARE OUT_SQLCODE1 INTEGER;
CALL execute_immediate
('INSERT INTO CROSS_REFERENCE
WITH T1 (QUERY_DESCR) AS
(SELECT nnn.QUERY_DESC FROM NEW_CATALOG),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
''||QUERY_DESCR FROM T3,T1',OUT_SQLCODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <> 0
THEN CHAR(OUT_SQLCODE1)
ELSE NULL END;
IF reason IS NOT NULL THEN
SIGNAL SQLSTATE '7500S' (reason);
END IF;
END!

Application raised error with diagnostic text: "-206
Please Help.

--
Message posted via http://www.dbmonster.com

lenygold via DBMonster.com wrote:
> I have the following trigger:
>
> --#SET TERMINATOR !
> CREATE TRIGGER CROSS_REFF_TRIG
> AFTER INSERT ON NEW_CATALOG
> REFERENCING NEW AS nnn
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> DECLARE reason VARCHAR(70);
> DECLARE OUT_SQLCODE1 INTEGER;
> CALL execute_immediate
> ('INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
> (VALUES( ''CONVERT JOIN IN SUBSELECT - CORELLATED OR NOT CORRELATED
> SUBQUERY'' )),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
> QTY_USED FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
> (STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
> ''||QUERY_DESCR FROM T3,T1',OUT_SQLCODE1);
> SET reason =
> CASE WHEN OUT_SQLCODE1 <> 0
> THEN CHAR(OUT_SQLCODE1)
> ELSE NULL END;
> IF reason IS NOT NULL THEN
> SIGNAL SQLSTATE '7500S' (reason);
> END IF;
> END!
>
> It is working perfect - doing mutible inserts in corresponding groups.
>
> But when i replace
> (VALUES( ''CONVERT JOIN IN SUBSELECT - CORELLATED OR NOT CORRELATED
> SUBQUERY'' )),
> on (SELECT nnn.QUERY_DESC FROM NEW_CATALOG),
>
> The following trigger generating sqlcode -206:
>
> --#SET TERMINATOR !
> CREATE TRIGGER CROSS_REFF_TRIG
> AFTER INSERT ON NEW_CATALOG
> REFERENCING NEW AS nnn
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> DECLARE reason VARCHAR(70);
> DECLARE OUT_SQLCODE1 INTEGER;
> CALL execute_immediate
> ('INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
> (SELECT nnn.QUERY_DESC FROM NEW_CATALOG),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
> QTY_USED FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
> (STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
> ''||QUERY_DESCR FROM T3,T1',OUT_SQLCODE1);
> SET reason =
> CASE WHEN OUT_SQLCODE1 <> 0
> THEN CHAR(OUT_SQLCODE1)
> ELSE NULL END;
> IF reason IS NOT NULL THEN
> SIGNAL SQLSTATE '7500S' (reason);
> END IF;
> END!
>
> Application raised error with diagnostic text: "-206
> Please Help.
>
Can you provide the entire error message with Token?

Also does the error occur on CREATE TRIGGER or at run time?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

It happen at Run Time:
Before tesing this TRIGGER i tested Query from trigger body.
CALL execute_immediate
('INSERT INTO CROSS_REFERENCE
WITH T1 (QUERY_DESCR) AS
(SELECT QUERY_DESC FROM NEW_CATALOG
WHERE GROUP_ID = ''SUBSL'' AND QUERY# = 13),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)
||'' ''||QUERY_DESCR FROM T3,T1',?);

It is working Fine.
Error happend only when i use nnn.QUERY_DESC.
Thank's Serge.

lenygold wrote:
>I have the following trigger:
>
>--#SET TERMINATOR !
>CREATE TRIGGER CROSS_REFF_TRIG
> AFTER INSERT ON NEW_CATALOG
> REFERENCING NEW AS nnn
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> DECLARE reason VARCHAR(70);
> DECLARE OUT_SQLCODE1 INTEGER;
> CALL execute_immediate
> ('INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
>(VALUES( ''CONVERT JOIN IN SUBSELECT - CORELLATED OR NOT CORRELATED
>SUBQUERY'' )),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
>QTY_USED FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
>(STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
>''||QUERY_DESCR FROM T3,T1',OUT_SQLCODE1);
> SET reason =
> CASE WHEN OUT_SQLCODE1 <> 0
> THEN CHAR(OUT_SQLCODE1)
> ELSE NULL END;
> IF reason IS NOT NULL THEN
> SIGNAL SQLSTATE '7500S' (reason);
> END IF;
> END!
>
> It is working perfect - doing mutible inserts in corresponding groups.
>
>But when i replace
> (VALUES( ''CONVERT JOIN IN SUBSELECT - CORELLATED OR NOT CORRELATED
>SUBQUERY'' )),
>on (SELECT nnn.QUERY_DESC FROM NEW_CATALOG),
>
>The following trigger generating sqlcode -206:
>
>--#SET TERMINATOR !
>CREATE TRIGGER CROSS_REFF_TRIG
> AFTER INSERT ON NEW_CATALOG
> REFERENCING NEW AS nnn
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> DECLARE reason VARCHAR(70);
> DECLARE OUT_SQLCODE1 INTEGER;
> CALL execute_immediate
> ('INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
> (SELECT nnn.QUERY_DESC FROM NEW_CATALOG),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
>QTY_USED FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
>(STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
>''||QUERY_DESCR FROM T3,T1',OUT_SQLCODE1);
> SET reason =
> CASE WHEN OUT_SQLCODE1 <> 0
> THEN CHAR(OUT_SQLCODE1)
> ELSE NULL END;
> IF reason IS NOT NULL THEN
> SIGNAL SQLSTATE '7500S' (reason);
> END IF;
> END!
>
>Application raised error with diagnostic text: "-206
>Please Help.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

Exact error message?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Sorry Serge i am right now at the meeting. I will mail it after noon

Serge Rielau wrote:
>Exact error message?

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

Here is my last Test:
event:
INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
IN CORELLATED OR NOT CORRELATED SUBQUERY');

ERROR:
INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2
QUERY',13,'HOW TO CONVERT JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnnostic text: "-206"
Explanation:

This error or warning occurred as a result of execution of the
RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger. An
SQLSTATE value that starts with '01' or '02' indicates a warning.

User response:

See application documentation.

sqlcode: -438, +438

sqlstate: application-defined

But when i run trigger body everything is working:
DROP TRIGGER CROSS_REFF_TRIG;
DB20000I The SQL command completed successfully.

INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
IN CORELLATED OR NOT CORRELATED SUBQUERY');

DB20000I The SQL command completed successfully.

Target table check before testing Trigger body:
SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBSEL','CONVERT')
GROUP BY KEY_WORD;
KEY_WORD LAST_GROUP_NUM
---------------- - --------------------------
CONVERT 32
JOIN 64
SUBSEL 13

3 record(s) selected.


CALL execute_immediate
('INSERT INTO CROSS_REFERENCE
WITH T1 (QUERY_DESCR) AS
(SELECT QUERY_DESC FROM NEW_CATALOG
WHERE GROUP_ID = ''SUBSL'' AND QUERY# = 13),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)
||'' ''||QUERY_DESCR FROM T3,T1',?);
Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : 0

Return Status = 0

Target table check AFTER testing Trigger body:
SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBSEL','CONVERT')
GROUP BY KEY_WORD;
KEY_WORD LAST_GROUP_NUM
---------------- -----------------------------------
JOIN 65
SUBSEL 14
CONVERT 33

Why Trigger is not working when trigger body is working????

Serge Rielau wrote:
>Exact error message?

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

lenygold via DBMonster.com wrote:
> Here is my last Test:
> event:
> INSERT INTO NEW_CATALOG
> VALUES
> ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
> IN CORELLATED OR NOT CORRELATED SUBQUERY');
>
> ERROR:
> INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2
> QUERY',13,'HOW TO CONVERT JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY')
> DB21034E The command was processed as an SQL statement because it was not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0438N Application raised error with diagnnostic text: "-206"
> Explanation:
>
> This error or warning occurred as a result of execution of the
> RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger. An
> SQLSTATE value that starts with '01' or '02' indicates a warning.
>
> User response:
>
> See application documentation.
>
> sqlcode: -438, +438
>
> sqlstate: application-defined
>
> But when i run trigger body everything is working:
> DROP TRIGGER CROSS_REFF_TRIG;
> DB20000I The SQL command completed successfully.
>
> INSERT INTO NEW_CATALOG
> VALUES
> ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
> IN CORELLATED OR NOT CORRELATED SUBQUERY');
>
> DB20000I The SQL command completed successfully.
>
> Target table check before testing Trigger body:
> SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
> FROM CROSS_REFERENCE
> WHERE KEY_WORD IN('JOIN','SUBSEL','CONVERT')
> GROUP BY KEY_WORD;
> KEY_WORD LAST_GROUP_NUM
> ---------------- - --------------------------
> CONVERT 32
> JOIN 64
> SUBSEL 13
>
> 3 record(s) selected.
>
>
> CALL execute_immediate
> ('INSERT INTO CROSS_REFERENCE
> WITH T1 (QUERY_DESCR) AS
> (SELECT QUERY_DESC FROM NEW_CATALOG
> WHERE GROUP_ID = ''SUBSL'' AND QUERY# = 13),
> T2(ItemName,MAX_ROW#) AS
> (SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
> FROM CROSS_REFERENCE
> GROUP BY STRIP(KEY_WORD)),
> T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
> (SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS
> QTY_USED FROM T1,T2
> WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE
> (STRIP(QUERY_DESCR),ITEMNAME,''''))) > 0
> GROUP BY ITEMNAME,MAX_ROW#)
> SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)
> ||'' ''||QUERY_DESCR FROM T3,T1',?);
> Value of output parameters
> --------------------------
> Parameter Name : OUT_SQLCODE
> Parameter Value : 0
>
> Return Status = 0
>
> Target table check AFTER testing Trigger body:
> SELECT KEY_WORD,MAX(ROW#) AS LAST_GROUP_NUM
> FROM CROSS_REFERENCE
> WHERE KEY_WORD IN('JOIN','SUBSEL','CONVERT')
> GROUP BY KEY_WORD;
> KEY_WORD LAST_GROUP_NUM
> ---------------- -----------------------------------
> JOIN 65
> SUBSEL 14
> CONVERT 33
>
> Why Trigger is not working when trigger body is working????
>
> Serge Rielau wrote:
>> Exact error message?
>
Pelase don't cut out stuff:
INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSELECT,EXIST,NOT EXIST
','DB2
QUERY',13,'HOW TO CONVERT JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY')
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnnostic text: "-206"
....????.....
You will not get that Explanation stuff. Please do not edit.

Cheers
Serge




--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

I just retested:

INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
IN CORELLATED OR NOT CORRELATED SUBQUERY');

This is all what got:
INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2
QUERY',13,'HOW TO CONVERT JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "-206 ".
SQLSTATE=7500S

SQL0438N Application raised error with diagnostic text: "-206
".

Explanation:

This error or warning occurred as a result of execution of the
RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger. An
SQLSTATE value that starts with '01' or '02' indicates a warning.

User response:

See application documentation.

Serge Rielau wrote:
>> Here is my last Test:
>> event:
>[quoted text clipped - 85 lines]
>>
>>> Exact error message?
>
>Pelase don't cut out stuff:
>INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSELECT,EXIST,NOT EXIST
>','DB2
>QUERY',13,'HOW TO CONVERT JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY')
>DB21034E The command was processed as an SQL statement because it was
>not a
>valid Command Line Processor command. During SQL processing it returned:
>SQL0438N Application raised error with diagnnostic text: "-206"
>...????.....
>You will not get that Explanation stuff. Please do not edit.
>
>Cheers
>Serge
>

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

I gave up on this combo: trigger + SP, and recreate the trigger without it
and it is working perfect.
CREATE TRIGGER CROSS_REFF_TRIG
AFTER INSERT
ON NEW_CATALOG
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
INSERT INTO CROSS_REFERENCE
WITH T1 (GROUP_ID,QUERY#,QUERY_DESCR) AS
(SELECT nnn.GROUP_ID,nnn.QUERY#,nnn.QUERY_DESC FROM NEW_CATALOG),
T2(ItemName,MAX_ROW#) AS
(SELECT DISTINCT STRIP(KEY_WORD),MAX(ROW#)
FROM CROSS_REFERENCE
GROUP BY STRIP(KEY_WORD)),
T3(MAX_ROW#,ITEM_NAME,ITEM_COUNT) AS
(SELECT MAX_ROW#,ITEMNAME AS ITEM_NAME,count(*) AS QTY_USED
FROM T1,T2
WHERE (LENGTH(STRIP(QUERY_DESCR)) - LENGTH(REPLACE(STRIP(QUERY_DESCR)
,ITEMNAME,''))) > 0
GROUP BY ITEMNAME,MAX_ROW#)
SELECT DISTINCT MAX_ROW# + 1,ITEM_NAME ,GROUP_ID ||' ' || CHAR(QUERY#)||'
'||QUERY_DESCR FROM T3,T1;

But if you find what was wrong with this combo please let me know. I used
this SP in other
triggers ans it is worked. Thank you Serge for your time.
Leny. G.







lenygold wrote:
>I just retested:
>
>INSERT INTO NEW_CATALOG
> VALUES
> ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
>IN CORELLATED OR NOT CORRELATED SUBQUERY');
>
>This is all what got:
>INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSELECT,EXIST,NOT EXIST ','DB2
>QUERY',13,'HOW TO CONVERT JOIN IN CORELLATED OR NOT CORRELATED SUBQUERY')
>DB21034E The command was processed as an SQL statement because it was not a
>valid Command Line Processor command. During SQL processing it returned:
>SQL0438N Application raised error with diagnostic text: "-206 ".
>SQLSTATE=7500S
>
>SQL0438N Application raised error with diagnostic text: "-206
>".
>
>Explanation:
>
>This error or warning occurred as a result of execution of the
>RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger. An
>SQLSTATE value that starts with '01' or '02' indicates a warning.
>
>User response:
>
>See application documentation.
>
>>> Here is my last Test:
>>> event:
>[quoted text clipped - 15 lines]
>>Cheers
>>Serge

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1

OK, so the SQLSTATE '7500S' is yours truly raised in the trigger.
(That's what I wanted to find out with my picky questions :-)
That also explains why the -206 didn't have any token.

What I would do is to go into the execute immediate procedure (which I
don't think you posted) and modify it so it doesn't catch the error.
This way you get the real error message from DB2 which should include a
token for the -206. Then take it from there.

Cheer
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab