Oracle - Concatenate calculated number field with string field

Question

In this case statement below, I want the result to be a string, either 'n days' or 'n weeks' based on the conditions in the statement.

How can I concatenate the number generated from ABS(FLOOR(TRUNC(i.schedule_finish) - TRUNC(sysdate))) with a string (that is, ' days' or ' weeks') to generate a string result?

Everything I've tried has given me an "inconsistent datatypes" error.

CASE
    WHEN ABS(FLOOR(TRUNC(i.schedule_finish) - TRUNC(sysdate))) < 7 THEN ABS(FLOOR(TRUNC(i.schedule_finish) - TRUNC(sysdate)))
    ELSE ABS(FLOOR((TRUNC(i.schedule_finish) - TRUNC(sysdate)) / 7))
END OVERDUE

Thanks

Gordon Linoff · Accepted Answer · 2019-12-18 12:43:32Z

1

Just use ||:

(CASE WHEN ABS(FLOOR(TRUNC(i.schedule_finish) - TRUNC(sysdate))) < 7
      THEN ABS(FLOOR(TRUNC(i.schedule_finish) - TRUNC(sysdate))) || ' days'
      ELSE ABS(FLOOR((TRUNC(i.schedule_finish) - TRUNC(sysdate)) / 7)) || ' weeks'
 END) as OVERDUE

answered Dec 18, 2019 at 12:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

doodlebob Over a year ago

Only had it on one case so one was returning a string and the other a number... My bad. This works. Thanks.

Collectives™ on Stack Overflow

Oracle - Concatenate calculated number field with string field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related