cbudoris
Active Member
Just thought I'd post this SQL solution here in case anybody else has this problem. I was trying to join RPPO.F03B11 to NEDOCO.F1501B. The RPPO is an 8 alpha field, while the NEDOCO is numeric. I first tried to CAST the RPPO as numeric, but got an invalid number error. Because this is the customer ledger, that field can be used to store a variety of data, sometimes including customer invoice numbers which contain alphanumeric characters- thus you'll get the error when trying to convert it to numeric datatype. So I tried to opposite way, casting the numeric as a string. Now I was getting an invalid datatype error, so instead of string I used VARCHAR. That, and trimming the leading zeros off the RPPO field, ended up working:
select rpkco, rpan8, rppo, rpglc, nedoco,
rpaap/100 as openamount, nesic from proddta.f03b11
inner join proddta.f1501b on cast(nedoco as varchar (8)) = ltrim(rppo, '0') where (rpaap < 100.01 and rpaap > -100.01) and
rpaap <> 0
order by rppo
The F1501B is the lease master in the real estate module, but this should work for any DOCO or numeric field in general.
Hope this helps!
Chris
select rpkco, rpan8, rppo, rpglc, nedoco,
rpaap/100 as openamount, nesic from proddta.f03b11
inner join proddta.f1501b on cast(nedoco as varchar (8)) = ltrim(rppo, '0') where (rpaap < 100.01 and rpaap > -100.01) and
rpaap <> 0
order by rppo
The F1501B is the lease master in the real estate module, but this should work for any DOCO or numeric field in general.
Hope this helps!
Chris
