IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Summing on Mixed Case Literals


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Roy Ware

New User


Joined: 19 Sep 2013
Posts: 14
Location: USA

PostPosted: Tue Jan 03, 2017 10:18 pm
Reply with quote

I'm being held hostage by two database tables who refuse to compromise. One table has the City Name in Mixed Case letters: El Paso, for example. The second has the City Name in Uppercase letters: EL PASO.

The goal is to summarize the dollar amount field for each city, regardless of the case, with the mixed case being the desirable output, but showing the uppercase name in case a "matching" lowercase is not available.

Here's the data:
Code:
Mattoon                277.86
MATTOON                179.02
Charleston             801.13
POPLAR GROVE           354.03


The desired output is:
Code:
Mattoon                 456.88
Charleston              801.13
POPLAR GROVE            354.03


Of course, nothing like this has been attempted here before, so there aren't any examples and the SyncSort manual isn't much help. Any suggestions?
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10697
Location: italy

PostPosted: Tue Jan 03, 2017 10:26 pm
Reply with quote

just overlay/build an additional field with the content of the key upper/lower cased for matching
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7312
Location: Inside the Matrix

PostPosted: Tue Jan 03, 2017 10:54 pm
Reply with quote

As enrico said, "normalise" the name in a temporary extension. SORT on that name. You'll also need to discriminate between upper/mixed, so that you can favour the mixed when both are present (a second field to include an indicator for mixed/upper so you can "outsort" the upper).

Exactly how you do that depends on the quality of your data. If you can have embedded typos...?

Whether SyncSort has TRAN= or some other built-in way to convert case is another issue.
Back to top
View user's profile Send private message
Roy Ware

New User


Joined: 19 Sep 2013
Posts: 14
Location: USA

PostPosted: Tue Jan 03, 2017 11:40 pm
Reply with quote

Yes, the field can have embedded typos - why can't we hold the front-ends to the same standards we have to match? - but Enrico's suggestion is going to work great!
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 -> SYNCSORT

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts usage of CASE in WHERE clause DB2 10
No new posts COBOL -DB2 SQL code to have GROUP BY ... DB2 21
No new posts Summing records conditional statements. SYNCSORT 13
No new posts Query on summing up the fields DFSORT/ICETOOL 10
No new posts RFE: DB2 support for mixed case names. DB2 0

Back to Top