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

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

I recommend 

Select ((Year * 100) + Month) as Period
>From files

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Grizzly Malchow
Sent: Friday, March 03, 2006 10:05 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL version of %EditC(Field:'X')

Maybe CAST and CONVERT will do the trick. I don't have an example but
google might produce some results.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Fleming, Greg (ED)
Sent: Friday, March 03, 2006 8: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.