RE: SQL version of %EditC(Field:'X')

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

The answer to the question in your subject line is: the DIGITS function.

However, as you describe your problem, I'd use this:

Select YYYYFld * 100 + MMFld from myfile


Heck, try both ways:

Select digits(YYYYfld) concat digits(MMfld) as charYYYYMM,
       YYYYfld * 100 + MMfld as numYYYYMM
from myfile   


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
> Fleming, Greg (ED)
> Sent: Friday, March 03, 2006 9:48 AM
> To: midrange-L@xxxxxxxxxxxx
> Subject: SQL version of %EditC(Field:'X')
> 
> I've got a 4 digit year in one field and a 2 digit month in 
> another, and
> I want to concatenate them in an SQL query for comparison purposes.
> 
> The ultimate result should be numeric, but I figured I would have to
> convert them to character, concatenate them, then convert bact to
> numeric.
> 
> Something like: Select Int(Char(Year) || Char(Month))
> 
> But that results in junk.  If I take out the INT, I get something like
> '2004  12' or '2005   1'.  The fields are defined as 4 and 2 digit, so
> I'm not sure why there are blanks between the Year and Month in the
> report, and then I've still got the leading zero on the month 
> to contend
> with as well.
> 
> To make sure my single digit months get the leading zero, in 
> RPG I would
> use the %EditC Bif with the 'X' edit code. 
> 
> Is there a way to do this in SQL ? 
> 
>  
> 
>  
> 
>  
> 
> Greg Fleming
> 
> Programmer/Analyst
> 
> Everglades Direct, Inc.
> 
>  
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>