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

Hi,

use the SQL Scalar Function DIGITS instead of CHAR.
DIGITS is the eqivalent of %EditC(Field: 'X') and CHAR is the equivalent of
%Char().

Assuming both fields are numeric why not simply multiplying Year with 100
and add the month.

Mit freundlichen Grüßen
 
i.A. Birgitta Hauser
 
LUNZER + PARTNER GMBH
Consulting | Software | Service
Carl-Zeiss-Straße 1
63755 Alzenau
 
Tel:         + 49 6023 951-255
Fax:        + 49 6023 951-111
Internet.  www.lp-gmbh.com
              www.rpg-schulung.de
 
--------------------------------------------------------
Handelsregister: Aschaffenburg HRB 4720
Gerichtsstand und Erfüllungsort Alzenau
Ust-IdNr.: DE132093146
Geschäftsführer: Rudolf Gerbert
---------------------------------------------------------
 
"If you think education is expensive, try ignorance"
(Derek Bok)
 
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Fleming, Greg (ED)
Gesendet: Freitag, 3. März 2006 15:48
An: midrange-L@xxxxxxxxxxxx
Betreff: 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.