CODEWITHSUNDEEP
sqloracle-database
doodlebob
doodlebob

Reputation: 75

Oracle - Concatenate calculated number field with string field

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

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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

Upvotes: 1

Related Questions