Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Compare CHAR columns using CAST

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'd say you're using CAST() in the wrong place.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2008
    Posts
    55
    Can I subtract 1 from a CHAR column without using CAST? Is there a way?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Certainly, you could use INT() if for some reason you hold a grudge against CAST()...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2008
    Posts
    55
    Quote Originally Posted by n_i
    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.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  7. #7
    Join Date
    Aug 2008
    Posts
    55
    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;

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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:
    Code:
    WITH tb950 (yeargive) AS
    ( SELECT CAST(yeargive AS smallint) FROM advtb950 ) ,
    tbdat (fiscyear) AS
    ( SELECT CAST(fiscyear AS smallint) FROM advtbdat )
    SELECT B.yeargive, C.fiscyear
    FROM tb950 B INNER JOIN tbdat C ON B.yeargive >= C.fiscyear - 1
    and the "WITH" (view) would disappear when the tables have been redesigned to used SMALLINT instead of CHAR(4) for the year columns.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •