Multible inserts in one Trigger - return code -206

[lenygold via DBMonster.com]

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_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
''||QUERY_DESCR FROM T3,T1',OUT_SQLC ODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <0
THEN CHAR(OUT_SQLCOD E1)
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_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
''||QUERY_DESCR FROM T3,T1',OUT_SQLC ODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <0
THEN CHAR(OUT_SQLCOD E1)
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

[Serge Rielau]

Re: Multible inserts in one Trigger - return code -206

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_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
''||QUERY_DESCR FROM T3,T1',OUT_SQLC ODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <0
THEN CHAR(OUT_SQLCOD E1)
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_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
''||QUERY_DESCR FROM T3,T1',OUT_SQLC ODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <0
THEN CHAR(OUT_SQLCOD E1)
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

[lenygold via DBMonster.com]

Re: Multible inserts in one Trigger - return code -206

It happen at Run Time:
Before tesing this TRIGGER i tested Query from trigger body.
CALL execute_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
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_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
>QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
>(STRIP(QUERY_D ESCR),ITEMNAME, ''''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
>''||QUERY_DESC R FROM T3,T1',OUT_SQLC ODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <0
THEN CHAR(OUT_SQLCOD E1)
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_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
>QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
>(STRIP(QUERY_D ESCR),ITEMNAME, ''''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
SELECT MAX_ROW# + 1,ITEM_NAME ,''SUBSL'' ||'' '' || CHAR(13)||''
>''||QUERY_DESC R FROM T3,T1',OUT_SQLC ODE1);
SET reason =
CASE WHEN OUT_SQLCODE1 <0
THEN CHAR(OUT_SQLCOD E1)
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.aspx/ibm-db2/200808/1

[Serge Rielau]

Re: Multible inserts in one Trigger - return code -206

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

[lenygold via DBMonster.com]

Re: Multible inserts in one Trigger - return code -206

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.aspx/ibm-db2/200808/1

[lenygold via DBMonster.com]

Re: Multible inserts in one Trigger - return code -206

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

ERROR:
INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSE LECT,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','SUBSE LECT,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(RO W#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBS EL','CONVERT')
GROUP BY KEY_WORD;
KEY_WORD LAST_GROUP_NUM
---------------- - --------------------------
CONVERT 32
JOIN 64
SUBSEL 13

3 record(s) selected.


CALL execute_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
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(RO W#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBS EL','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.aspx/ibm-db2/200808/1

[Serge Rielau]

Re: Multible inserts in one Trigger - return code -206

lenygold via DBMonster.com wrote:

Here is my last Test:
event:
INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSE LECT,EXIST,NOT EXIST ','DB2 QUERY',13,'HOW TO CONVERT JOIN
IN CORELLATED OR NOT CORRELATED SUBQUERY');
>
ERROR:
INSERT INTO NEW_CATALOG VALUES ('SUBSL','SUBSE LECT,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','SUBSE LECT,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(RO W#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBS EL','CONVERT')
GROUP BY KEY_WORD;
KEY_WORD LAST_GROUP_NUM
---------------- - --------------------------
CONVERT 32
JOIN 64
SUBSEL 13
>
3 record(s) selected.
>
>
CALL execute_immedia te
('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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS
QTY_USED FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE
(STRIP(QUERY_DE SCR),ITEMNAME,' '''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
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(RO W#) AS LAST_GROUP_NUM
FROM CROSS_REFERENCE
WHERE KEY_WORD IN('JOIN','SUBS EL','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','SUBSE LECT,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

[lenygold via DBMonster.com]

Re: Multible inserts in one Trigger - return code -206

I just retested:

INSERT INTO NEW_CATALOG
VALUES
('SUBSL','SUBSE LECT,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','SUBSE LECT,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','SUBSE LECT,EXIST,NOT EXIST
>','DB2
>QUERY',13,'H OW 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.aspx/ibm-db2/200808/1

[lenygold via DBMonster.com]

Re: Multible inserts in one Trigger - return code -206

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,nn n.QUERY#,nnn.QU ERY_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#,ITE M_NAME,ITEM_COU NT) AS
(SELECT MAX_ROW#,ITEMNA ME AS ITEM_NAME,count (*) AS QTY_USED
FROM T1,T2
WHERE (LENGTH(STRIP(Q UERY_DESCR)) - LENGTH(REPLACE( STRIP(QUERY_DES CR)
,ITEMNAME,''))) 0
GROUP BY ITEMNAME,MAX_RO W#)
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','SUBSE LECT,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','SUBSE LECT,EXIST,NOT EXIST ','DB2
>QUERY',13,'H OW 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=750 0S
>
>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.aspx/ibm-db2/200808/1

[Serge Rielau]

Re: Multible inserts in one Trigger - return code -206

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