CASE statement oddity

Jason Hughes

CASE statement oddity
I'm running the following SQL statements against DB2 V5 on an OS/390 machine. Forget what PUT level we are at, early 99 sounds right.

I would like to return a charater instead of a number because MS-Access has trouble creating a text file with no spaces between fields when one field is a number.

Here is a sample from the SQL. The first statement returns a number and works. The second is supposed to return a character, but it bombs. Any suggestions on how to fix the second statement?

--------------------------- Command entered ----------------------------
SELECT
(CASE WHEN MALE > 0 THEN 1 ELSE 2 END) AS S6_GENDER
FROM
table1
------------------------------------------------------------------------

S6_GENDER
-----------

0 record(s) selected.

--------------------------- Command entered ----------------------------
SELECT
(CASE WHEN MALE > 0 THEN '1' ELSE '2' END) AS S6_GENDER
FROM
table1
------------------------------------------------------------------------
SQL0332N There is no available conversion for the source code page "0" to the target code page "1252". Reason Code "1". SQLSTATE=57017


Thanks
Jason Hughes
TRW S&ITG



Richard Yevich

Re: CASE statement oddity
(in response to Jason Hughes)
Check the syntax but this would work. Use STRIP to remove leading 0's
if necessary. No time at moment to see what's wrong with failure.

SELECT
DIGITS(CASE WHEN MALE > 0 THEN 1 ELSE 2 END) AS S6_GENDER
FROM table1


--- Jason Hughes <[login to unmask email]> wrote:
> I'm running the following SQL statements against DB2 V5 on an
> OS/390 machine. Forget what PUT level we are at, early 99 sounds
> right.
>
> I would like to return a charater instead of a number because
> MS-Access has trouble creating a text file with no spaces between
> fields when one field is a number.
>
> Here is a sample from the SQL. The first statement returns a
> number and works. The second is supposed to return a character,
> but it bombs. Any suggestions on how to fix the second statement?
>
> --------------------------- Command entered
> ----------------------------
> SELECT
> (CASE WHEN MALE > 0 THEN 1 ELSE 2 END) AS S6_GENDER
> FROM
> table1
>
------------------------------------------------------------------------
>
> S6_GENDER
> -----------
>
> 0 record(s) selected.
>
> --------------------------- Command entered
> ----------------------------
> SELECT
> (CASE WHEN MALE > 0 THEN '1' ELSE '2' END) AS S6_GENDER
> FROM
> table1
>
------------------------------------------------------------------------
> SQL0332N There is no available conversion for the source code page
> "0" to the target code page "1252". Reason Code "1".
> SQLSTATE=57017
>
>
> Thanks
> Jason Hughes
> TRW S&ITG
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>
>
>
>


=====
Regards,
Richard
+====+====+====+====+
[login to unmask email]
__________________________________________________
Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one place.
Yahoo! Shopping: http://shopping.yahoo.com



[login to unmask email]

Re: CASE statement oddity
(in response to Richard Yevich)
I don't know at what PUT level we are but it works in our installation.I
have tried the same query and it is working.
It seems from the SQLSTATE that there is some problem with
SYSIBM.SYSSTRINGS as it says character conversion is not defined.

I think u should check this out.

Thanks

Sanjeev






Jason Hughes <[login to unmask email]>@RYCI.COM> on 12/22/99 09:51:36 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: CASE statement oddity


I'm running the following SQL statements against DB2 V5 on an OS/390
machine. Forget what PUT level we are at, early 99 sounds right.

I would like to return a charater instead of a number because MS-Access has
trouble creating a text file with no spaces between fields when one field
is a number.

Here is a sample from the SQL. The first statement returns a number and
works. The second is supposed to return a character, but it bombs. Any
suggestions on how to fix the second statement?

--------------------------- Command entered ----------------------------
SELECT
(CASE WHEN MALE > 0 THEN 1 ELSE 2 END) AS S6_GENDER
FROM
table1
------------------------------------------------------------------------

S6_GENDER
-----------

0 record(s) selected.

--------------------------- Command entered ----------------------------
SELECT
(CASE WHEN MALE > 0 THEN '1' ELSE '2' END) AS S6_GENDER
FROM
table1
------------------------------------------------------------------------
SQL0332N There is no available conversion for the source code page "0" to
the target code page "1252". Reason Code "1". SQLSTATE=57017

Thanks
Jason Hughes
TRW S&ITG








Sibimon Philip

Re: CASE statement oddity
(in response to ssethi@LOT.TATASTEEL.COM)
Our put level is 9809. The case statement works fine with our DB2 V5.1,
OS/390 V2.6.

thanks
Sibimon



-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 22, 1999 10:10 PM
To: [login to unmask email]
Subject: Re: CASE statement oddity


I don't know at what PUT level we are but it works in our installation.I
have tried the same query and it is working.
It seems from the SQLSTATE that there is some problem with
SYSIBM.SYSSTRINGS as it says character conversion is not defined.

I think u should check this out.

Thanks

Sanjeev






Jason Hughes <[login to unmask email]>@RYCI.COM> on 12/22/99 09:51:36 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: CASE statement oddity


I'm running the following SQL statements against DB2 V5 on an OS/390
machine. Forget what PUT level we are at, early 99 sounds right.

I would like to return a charater instead of a number because MS-Access has
trouble creating a text file with no spaces between fields when one field
is a number.

Here is a sample from the SQL. The first statement returns a number and
works. The second is supposed to return a character, but it bombs. Any
suggestions on how to fix the second statement?

--------------------------- Command entered ----------------------------
SELECT
(CASE WHEN MALE > 0 THEN 1 ELSE 2 END) AS S6_GENDER
FROM
table1
------------------------------------------------------------------------

S6_GENDER
-----------

0 record(s) selected.

--------------------------- Command entered ----------------------------
SELECT
(CASE WHEN MALE > 0 THEN '1' ELSE '2' END) AS S6_GENDER
FROM
table1
------------------------------------------------------------------------
SQL0332N There is no available conversion for the source code page "0" to
the target code page "1252". Reason Code "1". SQLSTATE=57017

Thanks
Jason Hughes
TRW S&ITG













Richard Yevich

Re: CASE statement oddity
(in response to Jason Hughes)
Get them much more current. You should at least be at 9903. The
problem was fixed back in maintenance, along with some other quirks
when using CASE. If you don't get closer to current with
maintenance, you are going to get stung with other problems.

Richard Yevich

--- Jason Hughes <[login to unmask email]> wrote:
> Just a clarification. I was confused about our PUT level. We are
> at 9805.
> I confused our client's PUT level with our own. Does anybody know
> if there
> was a APAR/PTF on this problem?
>
>
> On Thu, 23 Dec 1999 11:13:35 -0500, Philip, Sibimon
> <[login to unmask email]>
> wrote:
>
> >Our put level is 9809. The case statement works fine with our DB2
> V5.1,
> >OS/390 V2.6.
> >
> >thanks
> >Sibimon
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can
>
>
>


=====
Regards,
Richard
+====+====+====+====+
[login to unmask email]
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Jason Hughes

Re: CASE statement oddity
(in response to Sibimon Philip)
Just a clarification. I was confused about our PUT level. We are at 9805.
I confused our client's PUT level with our own. Does anybody know if there
was a APAR/PTF on this problem?


On Thu, 23 Dec 1999 11:13:35 -0500, Philip, Sibimon <[login to unmask email]>
wrote:

>Our put level is 9809. The case statement works fine with our DB2 V5.1,
>OS/390 V2.6.
>
>thanks
>Sibimon