Re: SQL joining with unlike types?

Hi James


Obviously a simple test for equality will not work - hasn't worked in RPG[LE], won't work here.


Is there a set of rules to effect a match? I'll assume not - here are some thoughts -
1. A blank can't match anything numeric
2. Are the 5-character values padded with blanks or with zeroes?
A. If with blanks, you probably need a trim() on the 5-character to compare with a char() on the zoned decimal
B. If with zeroes, you might be able to cast() the zoned to 5 long 0 decimals, then use a DIGITS() on that value


Some combination of those might work 93.5% of the time - just being a little pessimistic there, man!! :)


Cheers
Vern


On Tue, 12 Sep, 2023 at 1:57 PM, James H. H. Lampert via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:


To: midrange systems technical discussion
Cc: jamesl@xxxxxxxxxxxxxxxxx
I need to cross-reference two PFs. One gives territory and user number;
the other gives user number and first and last names.

The problem is that in one of the two files, the user number is a
5-character string (either digits or blank), while in the other, it's an
11-digit zoned decimal.

If I simply try "select terr, sman, first, last from salesp join cont on
sman = cid," I get "Selection error involving field *N."

Is there a way I can do this in SQL?

--
JHHL