Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Removing the last byte while unload

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Pandora-Box

Moderator


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

PostPosted: Thu Oct 29, 2009 1:51 pm    Post subject: Removing the last byte while unload
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    Post subject:
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

Moderator


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

PostPosted: Thu Oct 29, 2009 3:03 pm    Post subject:
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    Post subject:
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

Moderator


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

PostPosted: Thu Oct 29, 2009 5:55 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. Need help in estimating space of unlo... ashek15 IMS DB/DC 12 Fri Apr 07, 2017 5:11 am
No new posts 2 byte YEAR from Y'DATE2' field tecnokrat DFSORT/ICETOOL 2 Tue Feb 07, 2017 12:05 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us