SELECT CAST(B.YEARGIVE AS INTEGER), CAST(C.FISCYEAR AS INTEGER)
FROM
ADVTB950 B,
ADVTBDAT C
WHERE B.YEARGIVE >= C.FISCYEAR - 1;

---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF - IS INVALID


I am trying to compare two CHAR columns. Both columns contain a numeric year value. I tried using CAST but got an error. Any suggestions?

Thanks for any input.

I'd say you're using CAST() in the wrong place.

Can I subtract 1 from a CHAR column without using CAST? Is there a way?

Certainly, you could use INT() if for some reason you hold a grudge against CAST()...

I am willing to use anything. Just want to get this to work..
How would I use INT() here?
Thanks.

Hey Kevin,
Is it that your columns are really defined as CHAR? If so, then you are casting them in the wrong place. you need to cast them in the where clause or as N_I stated jus tuse int. Like:
SELECT CAST(B.YEARGIVE AS INTEGER), CAST(C.FISCYEAR AS INTEGER)
FROM
ADVTB950 B,
ADVTBDAT C
WHERE int(B.YEARGIVE) >= int(C.FISCYEAR) - 1;

Dave

Thank you, Dave and n_i !!
It worked this way with or without CAST.

SELECT B.YEARGIVE, C.FISCYEAR
FROM
ADVTB950 B,
ADVTBDAT C
WHERE INT(B.YEARGIVE) >= INT(C.FISCYEAR) - 1;

Whether you call it CAST or "conversion to integer with INT()" doesn't make any difference in the semantics. Both do the same thing.

So what's your problem with a CAST? I didn't understand that.

Actually, I would say that the table design was not well chosen: if years need to be numerically manipulated, they should be stored as SMALLINT instead of as CHAR(4).

Views could "mask" this as an implicit conversion (until the tables have been redesigned).
So essentially your query could become:
and the "WITH" (view) would disappear when the tables have been redesigned to used SMALLINT instead of CHAR(4) for the year columns.