IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Removing the last byte while unload


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Oct 29, 2009 1:51 pm
Reply with quote

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


Code:


AB.



Desired output is

Code:

AB


I wanted to eliminate that extra byte while I do an unload

How to eliminate that?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 29, 2009 2:56 pm
Reply with quote

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
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Oct 29, 2009 3:03 pm
Reply with quote

Yes Guyc I tried the one above but the output still gave me



Code:

AB.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 29, 2009 4:28 pm
Reply with quote

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
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Oct 29, 2009 5:55 pm
Reply with quote

Thanks Guyc it worked
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts 10 byte RBA conversion DB2 2
No new posts 10 byte RBA conversion -non applicati... JCL & VSAM 1
No new posts Remote Unload of CLOB Columns DB2 6
Search our Forums:

Back to Top