Joined: 02 Dec 2002 Posts: 618 Topics: 172 Location: Stockholm, Sweden
Posted: Wed Nov 28, 2018 7:45 am Post subject: Strange (?) results when unloading table
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
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Wed Nov 28, 2018 8:01 am Post subject:
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.
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
Joined: 02 Dec 2002 Posts: 618 Topics: 172 Location: Stockholm, Sweden
Posted: Thu Nov 29, 2018 2:15 am Post subject:
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.
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
