I have a query that I want to run using N/task and...
# suiteqlw
Watz
09/26/2023, 3:53 PMI have a query that I want to run using N/task and export to a CSV because of the size and time it takes to run it.
I have some amount-fields that are divided by 1000 but I still need to keep the decimals.
I notice that the CSV file will get rounded to two decimal places and formatted in the format in my user profile.
I've tried using BUILTIN_RESULT.TYPE_FLOAT, but it still rounds it.
running to_char() on the column works but outputs it as a text enclosed in quotes.
Anyone has any other tricks up their sleeves?
j
Jesper M
09/26/2023, 4:10 PMJust a guess, but ROUND(field, X)?
w
Watz
09/26/2023, 4:21 PMThis is one of the formulas:
Copy code
ROUND(SUM(DECODE(AT.left,'T', 1, -1) * SIGN(TAL.amount) * ABS(NVL(plan_rev.amount, TAL.amount)) ),2)/1000) AS amountWatz
09/26/2023, 4:21 PMSo I round the sum() to 2 decimal places and then I want to divide it by 1000 to get it in thousands.
d
David B
09/26/2023, 9:35 PMcould you just throw it into a
TO_CHARw
Watz
09/27/2023, 5:18 AMYes, I can, but the values are then enclosed with qoutes. I have my doubts that the target system for the file will accept that.
Watz
09/27/2023, 5:26 AMAt the moment, the file is ~2MB, so I can still add a dependant script that loads the file and reformats it to a csv in my desired target format.
d
David B
09/27/2023, 7:27 PMSorry Watz, I don't know how on earth I missed the fact your already mentioned TO_CHAR in your post 🤦