SQL Stored procedure - Case statement behaviour

Rosina Porco

SQL Stored procedure - Case statement behaviour
Hi

We are a DB2 V8 Z/OS



I am trying to understand the behavior of a simple DB2 SQL store procedure

With 2 main statements

CASE/ WHEN / END CASE

And INSERT into



If the CASE statement is first the INSERT does not get executed . the SP ends successfully

If however

The first statement is the INSERT

Followed by CASE /WHEN/END CASE

Both statements are executed



Can someone enlighten me

Why does the process end after the CASE END CASE Statement

I



Ex:

CREATE PROCEDURE SP1 (in att1 char(1);

In att2 integer ;

In att3 char (4);)



P1: BEGIN

case

when (att1 = ' ' and att3 <> ' ' )

then set att1 = 'P';

end case;



insert into RXA.rg_audit (att1b,att2b,att3b)

values (att1,att2,att3

user, current_timestamp);



END P1



Thank you very much





Rosie Porco
Database Administrator
Computing & Communications Services / Service de l'informatique et des communications

University of Ottawa / Université d'Ottawa

Tel: 613-562-5800 x 1397

email: [login to unmask email]














_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Larry Kintisch

Re: SQL Stored procedure - Case statement behaviour
(in response to Rosina Porco)
Hi Rosina,

The INSERT statement you showed us is not
correct: it has three target columns and 5 VALUES expressions.

Aside from that, assignment can't be done to
an IN parameter: make it INOUT. I would have used:
IF (att1 = ' ' and att3 <> ' ' )
then set att1 = ‘P’;
ENDIF

I've never tried this alternative but it should work:

insert into RXA.rg_audit (att1b,att2b,att3b,col4,col5)
values (case
when (att1 = ' ' and att3 <> ' ' )
then ‘P’
else att1
end
,att2,att3
user, current_timestamp);

Hope this helps.
Larry Kintisch, Pres. ABLE Information Services
www.dbindexdesign.com 845-353-0885

At 03:03 PM 12/10/2009, you wrote:
>Hi
>We are a DB2 V8 Z/OS
>
>I am trying to understand the behavior of a simple DB2 SQL store procedure
> With 2 main statements
>CASE/ WHEN / END CASE
>And INSERT into
>
>If the CASE statement is first the INSERT does
>not get executed . the SP ends successfully
>If however
>The first statement is the INSERT
>Followed by CASE /WHEN/END CASE
>Both statements are executed
>
>Can someone enlighten me
>Why does the process end after the CASE END CASE Statement
>I
>
>Ex:
>CREATE PROCEDURE SP1 (in att1 char(1);
> In att2 integer ;
> In att3 char (4);)
>
>P1: BEGIN
>case
> when (att1 = ' ' and att3 <> ' ' )
> then set att1 = ‘P’;
>end case;
>
>insert into RXA.rg_audit (att1b,att2b,att3b)
> values (att1,att2,att3
> user, current_timestamp);
>
>END P1
>
>Thank you very much
>
>
>Rosie Porco
>Database Administrator
>Computing & Communications Services / Service de
>l'informatique et des communications
>University of Ottawa / Université d'Ottawa
>Tel: 613-562-5800 x 1397
>email: <mailto:[login to unmask email]>[login to unmask email]
>
>
>
>
>
>
>
>
> < http://www.idug.org/db2-north-america-conference/index.html >
>IDUG - The Worldwide DB2 User Community!
>
>
>The IDUG DB2-L Listserv is only part of your
>membership in IDUG. If you are not already an
>IDUG member, < http://www.idug.org/register > please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Todd Burch

Re: SQL Stored procedure - Case statement behaviour
(in response to Larry Kintisch)
An assignment can be done to an IN parameter - perfectly legal.

Confusing perhaps to the reader, but legal nonetheless.

Rosina, I don't see any error handlers in that SQL Procedure. You
should use them. When you get to V9, if you aren't already, every
stinking statement is an "SQL" statement, and can set an SQLSTATE and
has the potential to throw an exception.

Todd


On Dec 10, 2009, at 10:02 PM, Larry Kintisch wrote:

Hi Rosina,

The INSERT statement you showed us is not correct: it has three
target columns and 5 VALUES expressions.

Aside from that, assignment can't be done to an IN parameter: make
it INOUT. I would have used:
IF (att1 = ' ' and att3 <> ' ' )
then set att1 = ‘P’;
ENDIF

I've never tried this alternative but it should work:

insert into RXA.rg_audit (att1b,att2b,att3b,col4,col5)
values (case
when (att1 = ' ' and att3 <> ' ' )
then ‘P’
else att1
end
,att2,att3
user, current_timestamp);

Hope this helps.
Larry Kintisch, Pres. ABLE Information Services
www.dbindexdesign.com 845-353-0885

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: SQL Stored procedure - Case statement behaviour
(in response to Todd Burch)
I think its the "end case" perhaps that second "case" is causing some
"confusion" - Just remove the case and correct the insert...hopefully all
will be well!



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




Rosina Porco <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
10.12.2009 21:03
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] SQL Stored procedure - Case statement behaviour






Hi
We are a DB2 V8 Z/OS

I am trying to understand the behavior of a simple DB2 SQL store procedure

With 2 main statements
CASE/ WHEN / END CASE
And INSERT into

If the CASE statement is first the INSERT does not get executed . the
SP ends successfully
If however
The first statement is the INSERT
Followed by CASE /WHEN/END CASE
Both statements are executed

Can someone enlighten me
Why does the process end after the CASE END CASE Statement
I

Ex:
CREATE PROCEDURE SP1 (in att1 char(1);
In att2 integer ;
In att3 char (4);)

P1: BEGIN
case
when (att1 = ' ' and att3 <> ' ' )
then set att1 = ‘P’;
end case;

insert into RXA.rg_audit (att1b,att2b,att3b)
values (att1,att2,att3
user, current_timestamp);

END P1

Thank you very much


Rosie Porco
Database Administrator
Computing & Communications Services / Service de l'informatique et des
communications
University of Ottawa / Université d'Ottawa
Tel: 613-562-5800 x 1397
email: [login to unmask email]









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

Rosina Porco

Re: SQL Stored procedure - Case statement behaviour
(in response to Roy Boxwell)
Thanks.
I realize I could code an IF statement .
I guess my exam was poor .

Basically, I really would like to understand why DB2 ends the processes after the CASE WHEN END CASE and does not continue to execute the rest of the code .

Similarly ,
SP with multiple CASE /when/ end case statement only the first one is executed and the SP completes successfully




Thanks

Rosie Porco



-----Original Message-----
From: Larry Kintisch [mailto:[login to unmask email]
Sent: Thursday, December 10, 2009 11:03 PM
Subject: Re: SQL Stored procedure - Case statement behaviour

Hi Rosina,

The INSERT statement you showed us is not
correct: it has three target columns and 5 VALUES expressions.

Aside from that, assignment can't be done to
an IN parameter: make it INOUT. I would have used:
IF (att1 = ' ' and att3 <> ' ' )
then set att1 = 'P';
ENDIF

I've never tried this alternative but it should work:

insert into RXA.rg_audit (att1b,att2b,att3b,col4,col5)
values (case
when (att1 = ' ' and att3 <> ' ' )
then 'P'
else att1
end
,att2,att3
user, current_timestamp);

Hope this helps.
Larry Kintisch, Pres. ABLE Information Services
www.dbindexdesign.com 845-353-0885

At 03:03 PM 12/10/2009, you wrote:
>Hi
>We are a DB2 V8 Z/OS
>
>I am trying to understand the behavior of a simple DB2 SQL store procedure
> With 2 main statements
>CASE/ WHEN / END CASE
>And INSERT into
>
>If the CASE statement is first the INSERT does
>not get executed . the SP ends successfully
>If however
>The first statement is the INSERT
>Followed by CASE /WHEN/END CASE
>Both statements are executed
>
>Can someone enlighten me
>Why does the process end after the CASE END CASE Statement
>I
>
>Ex:
>CREATE PROCEDURE SP1 (in att1 char(1);
> In att2 integer ;
> In att3 char (4);)
>
>P1: BEGIN
>case
> when (att1 = ' ' and att3 <> ' ' )
> then set att1 = 'P';
>end case;
>
>insert into RXA.rg_audit (att1b,att2b,att3b)
> values (att1,att2,att3
> user, current_timestamp);
>
>END P1
>
>Thank you very much
>
>
>Rosie Porco
>Database Administrator
>Computing & Communications Services / Service de
>l'informatique et des communications
>University of Ottawa / Université d'Ottawa
>Tel: 613-562-5800 x 1397
>email: <mailto:[login to unmask email]>[login to unmask email]
>
>
>
>
>
>
>
>
> < http://www.idug.org/db2-north-america-conference/index.html >
>IDUG - The Worldwide DB2 User Community!
>
>
>The IDUG DB2-L Listserv is only part of your
>membership in IDUG. If you are not already an
>IDUG member, < http://www.idug.org/register > please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L