Oracle Analytics Cloud and Server

Different Display in Column Prompt

Accepted answer
12
Views
2
Comments

Hi,

I get a different display in default and list of a column prompt:

Choice List:
SELECT CONCAT(CONCAT(cast("Subject Area"."Pur. Fiscal Month" as char) , ' - '), "Subject Area Date"."Pur. Month Name" ) FROM "Subject Area" ORDER BY "Subject Area Date"."Pur. Fiscal Month" FETCH FIRST 65001 ROWS ONLY

Default Value:
SELECT CONCAT(CONCAT(cast("Subject Area"."Pur. Fiscal Month" as char) , ' - '), "Subject Area Date"."Pur. Month Name" ) FROM "Subject Area" WHERE "Subject Area Date"."ROW_WID" = valueof(last_snapshot)

Why do I have empty space between the number and the minus?

Best Answer

  • Federico Venturin
    Federico Venturin ✭✭✭✭✭
    edited August 9 Answer ✓

    Hi @User_OEVVZ ,

    When you cast as char (without specify a length) the tool automatically adds a few blank spaces at the right of the string to fill all the available size. Values in choice list are probably trimmed automatically.

    You could use the TRIM function to get rid of those spaces:

    TRIM(BOTH ' ' FROM cast("Subject Area"."Pur. Fiscal Month" as char))

Answers

  • Thank you @Federico Venturin. It works.

    I was confused because the list values were correct.

    Note: I won't need » both ' ' « since trim is by default both.