View previous topic :: View next topic
|
Author |
Message |
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Hi,
When I run this query through batch
Code: |
SELECT
CASE
WHEN 1 = 1
THEN
( SELECT SUBSTR('ABC', 1, 2)
FROM SYSIBM.SYSDUMMY1
)
END
FROM SYSIBM.SYSDUMMY1
WITH UR;
|
I am getting the output as
Desired output is
I wanted to eliminate that extra byte while I do an unload
How to eliminate that? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
I guess that is not really what you're doing, but I'll go with the example given :
when you don't have an else in your case instruction, the output column will be nullable. Thus adding an additional byte for the null indicator.
try adding else ' '
and maybe you'll need a cast , but I'm not sure about that.
Code: |
SELECT
cast(
CASE
WHEN 1 = 1
THEN
( SELECT SUBSTR('ABC', 1, 2)
FROM SYSIBM.SYSDUMMY1
)
else ' '
END
as char(2))
FROM SYSIBM.SYSDUMMY1
WITH UR; |
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Yes Guyc I tried the one above but the output still gave me
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
SELECT
CAST(
COALESCE(
CASE WHEN 1 = 1
THEN ( SELECT SUBSTR('ABC', 1, 2) FROM SYSIBM.SYSDUMMY1
) END
,' ')
AS CHAR(2))
FROM SYSIBM.SYSDUMMY1
WITH UR; |
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Thanks Guyc it worked |
|
Back to top |
|
|
|