DB2 hex values in SQL causing problem

97495 pts.
SQL Server
Ok here's one I cannot figure out.
Here are the PO#s that show in our green screen software and they look fine  
RMA 001904          REASON CODE 11 
8075-089          1V
76139-6200        88
But if I write a SQL to extract them the PO# 33-­548504­-D returns as 
33548504D in the grid. NO DASHES.   I'm using a DB2Reader to populate the grid.
Now if I copy and paste from the grid it looks fine
Order# Purchae Order#
128940 5-824101                                  
128942 33­-548504­-D
128944 8075-089          1V                      
128945 76139-6200        88                      
I did a DSPPFM on the PF and noticed the one that acts strange has a different
hex value for the -
I cannot find anything as to why some of the dashes have a hex of 60 and others have a hex of CA.
Is there a chart that can explain why the - has 2 different hex values ?
Here is the hex
+ . . .  . 3 . .  . . + .  . . . 4  . . . .  + . . .  . 5 . .  . . + .  . . . 6     +....3....+....4....+....5....+....6 
F560F8F2 F4F1F0F1 40404040 40404040 40404040 40404040 40404040 40404040 40404040   *5-824101                            *
E2F1F0F7 F9F6F8F2 40404040 40404040 40404040 40404040 40404040 40404040 40404040   *S1079682                            *
F3F3CAF5 F4F8F5F0 F4CAC440 40404040 40404040 40404040 40404040 40404040 40404040   *33-­548504-­D                         *
D9D4C140 F0F0F1F9 F0F44040 40404040 40404040 D9C5C1E2 D6D540C3 D6C4C540 F1F14040   *RMA 001904          REASON CODE 11  *
F8F0F7F5 60F0F8F9 40404040 40404040 4040F1E5 40404040 40404040 40404040 40404040   *8075-089          1V                *
F7F6F1F3 F960F6F2 F0F04040 40404040 4040F8F8 40404040 40404040 40404040 40404040   *76139-6200        88                *

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    First things first, is it a PF or a table? In either case, what are the file and field CCSIDs? Next, is there any way to determine if the different rows came from different sources?
    22,625 pointsBadges:
  • ToddN2000
    I've tried both with same results. The file CCSID =  37. I think the users may be up to their old habits, cutting and pasting from other sources. This was a big issue a few years ago where cutting and pasting from an email that had multiple periods to end a sentence were converted into a single char ellipse. That was causing our green screen program to crash with invalid data.
    97,495 pointsBadges:
  • Splat
    @ToddN2000, I had a similar problem with users copying & pasting comments. I ended up writing a program to go through the comments & convert any character with a hex value less than x'40' to x'40'.
    12,030 pointsBadges:
  • ToddN2000
    I found an old one I wrote about 8yrs ago. The replace goes something like this.
    D IChar           C                   Const(                              
    D                                     X'000102030405060708090A0B0C0D0E0F+ 
    D                                       101112131415161718191A1B1C1D1E1F+ 
    D                                       202122232425262728292A2B2C2D2E2F+ 
    D                                       303132333435363738393A3B3C3D3E3F+ 
    D                                       404142434445464748494A4B4C4D4E4F+ 
    D                                       505152535455565758595A5B5C5D5E5F+ 
    D                                       606162636465666768696A6B6C6D6E6F+ 
    D                                       707172737475767778797A7B7C7D7E7F+ 
    D                                       808182838485868788898A8B8C8D8E8F+ 
    D                                       909192939495969798999A9B9C9D9E9F+ 
    D                                       A0A1A2A3A4A5A6A7A8A9AAABACADAEAF+ 
    D                                       B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF+ 
    D                                       C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF+ 
    D                                       D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF+ 
    D                                       E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF+ 
    D                                       F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF')
    D OChar           C                   Const(                              
    D                                     X'4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A+ 
    D                                       4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A+  
    D                                       4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A+  
    D                                       4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A+  
    D                                       404A4A4A4A4A4A4A4A4A4A4B4C4D4E4F+  
    D                                       504A4A4A4A4A4A4A4A4A5A5B5C5D5E5F+  
    D                                       60614A4A4A4A4A4A4A4A6A6B6C6D6E6F+  
    D                                       4A4A4A4A4A4A4A4A4A797A7B7C7D7E7F+  
    D                                       4A8182838485868788894A4A4A4A4A4A+  
    D                                       4A9192939495969798994A4A4A4A4A4A+  
    D                                       4AA1A2A3A4A5A6A7A8A94A4A4A4A4A4A+  
    D                                       4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A4A+  
    D                                       C0C1C2C3C4C5C6C7C8C94A4A4A4A4A4A+  
    D                                       D0D1D2D3D4D5D6D7D8D94A4A4A4A4A4A+  
    D                                       E04AE2E3E4E5E6E7E8E94A4A4A4A4A4A+  
    D                                       F0F1F2F3F4F5F6F7F8F94A4A4A4A4A4A') 
     I was running it as an on demand process when programs and applications crashed. They are still working fine in the green screen environment but if we have some VB/SQL apps pulling the data from the i-Series, that is where we are having issues.
    When I pull from my VB app using SQL that is where the results in the grid do not show the same results.
     Pull data from order file looking for special char used in the PO#

    SELECT digits(o4ord#)as order#,o4sufx,ohbtky,ohspo#,ohinit,odcflg,row_number() over() FROM oh s
    join o4 on ohord#=o4ord# 
    where ASCII(TRIM(TRANSLATE(ohspo#,' ',
    not in (10,64)
    order by ohord#
    97,495 pointsBadges:
  • ToddN2000
    Ooops, correction to my code above, the 4A's in the Ochar should have been 40... I was playing with it and for got to change it back:
    D OChar           C                   Const(                              
    D                                     X'40404040404040404040404040404040+ 
    D                                       40404040404040404040404040404040+ 
    D                                       40404040404040404040404040404040+ 
    D                                       40404040404040404040404040404040+ 
    D                                       404040404040404040404A4B4C4D4E4F+ 
    D                                       504040404040404040405A5B5C5D5E5F+ 
    D                                       606140404040404040406A6B6C6D6E6F+ 
    D                                       404040404040404040797A7B7C7D7E7F+ 
    D                                       40818283848586878889404040404040+ 
    D                                       40919293949596979899404040404040+ 
    D                                       40A1A2A3A4A5A6A7A8A9404040404040+ 
    D                                       40404040404040404040404040404040+ 
    D                                       C0C1C2C3C4C5C6C7C8C9404040404040+ 
    D                                       D0D1D2D3D4D5D6D7D8D9404040404040+ 
    D                                       E040E2E3E4E5E6E7E8E9404040404040+ 
    D                                       F0F1F2F3F4F5F6F7F8F9404040404040')
    97,495 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: