|
View previous topic :: View next topic
|
| Author |
Message |
Kakis Ziliaskoudis
New User
Joined: 15 Jan 2021
Posts: 4
Location: Sweden
|
|
|
|
Hi!
I'm trying to create an internal autonomous stored procedure for logging my application progress. ^_^
At my job we have always used External Cobol SP, but I want to try something new.
There are always different errors and I'm currently struggling with -628 "The clauses are mutually exclusive" - even when I comment out almost all code. Why?
I'm also having problems with VARCHAR as parameters. I read in the docs that varchar can only be used with LANGUAGE C, but I really want SQL and varchar, since its the target columns datatype. What is good/best practice to handle this?
Can you spot more errors?
I'm on a Db2 on z/os, Function Level V12R1M501
Translations:
Nyckelord - Keyword
Fritext - Free text
Longfritext - "Long as in many characters"-free text
Thanks in advance
// Kakis
PS. I have 25 yrs of MS SQL Server Development, and just started with Mainframe/Db2/Cobol. Maybe it shows in my code?
| Code: |
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
-- 00010000
CREATE PROCEDURE SYSPROC.LOGGA 00020012
( 00030000
IN NYCKELORD CHAR(10), -- VARCHAR(10), 00040021
IN FRITEXT CHAR(30), -- VARCHAR(30), 00050021
IN LONGFRITEXT CHAR(50) --VARCHAR(5000), 00060022
) 00070000
LANGUAGE SQL 00080000
AUTONOMOUS 00090000
WLM ENVIRONMENT DSAACCEPT 00110017
BEGIN 00120000
DECLARE LV_PROGLOGID INTEGER; 00130010
WITH LV_PROGLOGID(ID) AS 00131018
( 00132018
SELECT PROGRESSLOGID AS ID 00140018
FROM FINAL TABLE 00160018
( 00170018
INSERT INTO B40.PROGRESSLOG (NYCKELORD, FRITEXT) 00180018
VALUES (NYCKELORD, FRITEXT) 00190021
) 00200018
); 00201018
IF LONGFRITEXT IS NOT NULL THEN 00220018
INSERT INTO B40.EXTRADATA (PROGRESSLOGID, LONGFRITEXT) 00230018
VALUES (:LV_PROGLOGID, LONGFRITEXT); 00240018
END IF 00250018
END# 00260001
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -628, ERROR: THE CLAUSES ARE MUTUALLY EXCLUSIVE
DSNT418I SQLSTATE = 42613 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSMS1 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 5 0 0 -1 433 2905 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000005' X'00000000' X'00000000' X'FFFFFFFF'
X'000001B1' X'00000B59' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 28
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 44
******************************** Bottom of Data ******************************** |
|
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
Two things looks as extremely strange in this SQL part.
1. WITH cannot be used without corresponding main clause SELECT. For myself, I always consider any WITH as a sort of the "local VIEW definition", which is defined for the SELECT main clause only.
2. The SELECT clause (defined here as the part of WITH) cannot include its inner INSERT clause. Where did you find these syntax rules?
I recommend, before putting everything into the stored procedure, - just run multiple tests on your complex SQL code (even using simple SPUFI). After you find that your specific SQL inventions really work as expected, you can start incorporating it into your stored procedure. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| You are creating temp table as LV_PROGLOGID and ID being the col. You cannot have INSERT within WITH. If you want to have ID to be Inserted then that INSERT should be outside of WITH. |
|
| Back to top |
|
|
Kakis Ziliaskoudis
New User
Joined: 15 Jan 2021
Posts: 4
Location: Sweden
|
|
|
|
I agree on the strangeness. I have now stripped my code down to
| Code: |
CREATE PROCEDURE LOGGA
(
IN NYCKELORD CHAR(10), -- VARCHAR(10),
IN FRITEXT CHAR(30), -- VARCHAR(30),
IN LONGFRITEXT CHAR(50) --VARCHAR(5000),
)
WLM ENVIRONMENT DSAACCEPT
BEGIN
END
#
|
But I still get
| Code: |
SQLCODE = -628, ERROR: THE CLAUSES ARE MUTUALLY EXCLUSIVE
SQLSTATE = 42613 SQLSTATE RETURN CODE |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Kakis Ziliaskoudis wrote: |
I agree on the strangeness. I have now stripped my code down to
| Code: |
CREATE PROCEDURE LOGGA
(
IN NYCKELORD CHAR(10), -- VARCHAR(10),
IN FRITEXT CHAR(30), -- VARCHAR(30),
IN LONGFRITEXT CHAR(50) --VARCHAR(5000),
)
WLM ENVIRONMENT DSAACCEPT
BEGIN
END
#
|
But I still get
| Code: |
SQLCODE = -628, ERROR: THE CLAUSES ARE MUTUALLY EXCLUSIVE
SQLSTATE = 42613 SQLSTATE RETURN CODE |
|
Your problem is not in the stored procedure, but in misunderstanding of SQL concepts.
I repeat again:
Run the SQL code itself out of the stored procedure (simplified to run autonomously), and using any simple tool like SPUFI.
Only when your SQL is running successfully by itself, you can start incorporating it into your stored procedure, or whatever else. |
|
| Back to top |
|
|
Kakis Ziliaskoudis
New User
Joined: 15 Jan 2021
Posts: 4
Location: Sweden
|
|
|
|
First I want to thank you for your replies. They have been helpful.
Problem one: Between the declaration of SP and the body I had to specify
| Code: |
LANGUAGE SQL --- IMPORTANT
DETERMINISTIC -- -- NEED TO BE SPECIFIED
AUTONOMOUS -- IMPORTANT
WLM ENVIRONMENT FOR DEBUG MODE DSAACCEPT -- NEED TO BE SPECIFIED
|
You asked about where the "WITH" came from. Some references:
stackoverflow.com/questions/25221966/db2-storing-results-from-final-table-clause
stackoverflow.com/questions/51619437/what-is-the-final-table-equivalent-for-merge
They have now been removed.
This is what I have in my body so far:
| Code: |
DECLARE LV_PROGLOGID INTEGER;
SELECT MAX(ID) INTO LV_PROGLOGID
FROM FINAL TABLE
(
INSERT INTO B40.PROGRESSLOG (NYCKELORD, FRITEXT)
VALUES (NYCKELORD, FRITEXT)
);
IF LONGFRITEXT IS NOT NULL THEN
INSERT INTO B40.EXTRADATA (PROGRESSLOGID, LONGFRITEXT)
VALUES (LV_PROGLOGID, LONGFRITEXT);
END IF;
|
Now I'm dealing with PRIVELIGE problems. But thats something for my DBA.
Again! Thank you all for your help! |
|
| Back to top |
|
|
|
|
