MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Strange (?) results when unloading table

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 614
Topics: 171
Location: Stockholm, Sweden

PostPosted: Wed Nov 28, 2018 7:45 am    Post subject: Strange (?) results when unloading table Reply with quote

This has me stumped (I have a work-around below), but nonethless, I don't understand what's happening.
Basically, I can get specific results when running SPUFI, but different results when running
Code:

RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')


In the example below, the "main" column which is acting "strangely" is T_MODKOD, defined in the table as CHAR(3).

Code:

 t801.t_regnr      -- First 6 characters                 
,char(left(t801.t_modkod, 2))    -- next 2 chars
,case                           
  when t801.t_fslag = '01' then 
    char('PB')                   
  else                           
    char('LB')                   
 end as FORDKL      -- final 2 chars

gives the following unloaded data (note the blank before the PB character on the first record)

Quote:

OND00BMZ PB
OND003BW PB
OND011KG PB
OND013KG PB

whereas the following code (now we get no trailing blank)
Code:

 t801.t_regnr                         
,char(substr(t801.t_modkod, 1, 2))   
,case                                 
  when t801.t_fslag = '01' then       
    char('PB')                       
  else                               
    char('LB')                       
 end as FORDKL

gives
Quote:

OND00BMZPB
OND003BWPB
OND011KGPB
OND013KGPB


Note also the following (in SPUFI)

Code:

   select char(left(t801.t_modkod, 2)||'ABC') as LEFT_2

gives (as expected)

Quote:

---------+
LEFT_2
---------+
BWABC

_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Nov 28, 2018 8:01 am    Post subject: Reply with quote

misi01,

The reason for your results is quite simple. When you use the scalar function LEFT, the output result is a Variable length string. The spaces you see in front is the LENGTH stored in binary. You put the data in hex mode and verify it. Even though you had CHAR after left, you did NOT specify the output length. So specify the length on CHAR also or else it will retain the length as is.

Code:

,char(left(t801.t_modkod, 2))    -- next 2 chars


to
Code:

,char(left(t801.t_modkod, 2),2)    -- next 2 chars 

Check this link which explains the LEFT function

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_left.html

When you used SUBSTR or CHAR fucntions it resulted in fixed length string and hence you don't see the length portion.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
misi01
Advanced


Joined: 02 Dec 2002
Posts: 614
Topics: 171
Location: Stockholm, Sweden

PostPosted: Thu Nov 29, 2018 2:15 am    Post subject: Reply with quote

Thanks for the reply. The "amusing" thing was I did read exactly that URL. I also saw the
Quote:

Example 4: The FIRSTNME column in sample EMP table is defined as VARCHAR(12). Find the first name for an employee whose last name is 'BROWN' and return the first name in a 10-byte string.
SELECT LEFT(FIRSTNME,10)
FROM DSN8A10.EMP
WHERE LASTNAME='BROWN';

What I didn't see was the bit immediately under.
Quote:

This function returns a VARCHAR(10) string that has the value of 'DAVID' followed by 5 blank characters.

_________________
Michael
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group