Как добавить десятичные знаки в результат моего запроса Oracle SQL
У меня есть следующий запрос
select oc.card_number,
case opt.oper_type
when 'OPTP0000' then '05-PURCHASE'
when 'OPTP0020' then '06-CREDIT VOUCHER'
else 'other' end as Transaction_Type,
opt.oper_date as "TRANSACTION_DATE",
op.account_number,
case vf.sttl_currency
when '840' then 'USD'
end as "SOURCE_AMOUNT_CURRENCY",
case opt.oper_type
when 'OPTP0000' then -vf.sttl_amount
else vf.sttl_amount
end as "SOURCE_AMOUNT",
op.auth_code,
opt.mcc,
opt.terminal_number as "TERMINAL_ID",
vf.pos_terminal_cap,
vf.pos_entry_mode,
opt.part_key as "CAPTURE_DATE",
opt.oper_amount as "TRANSACTION_AMOUNT",
opt.oper_currency as "TRANSACTION_CURRENCY",
opt.merchant_name,
COALESCE(cc.visa_country_code,opt.merchant_country) merchant_country,
opt.clearing_sequence_num,
opt.clearing_sequence_count,
opt.oper_type
FROM opr_operation opt
INNER JOIN opr_participant op
ON op.oper_id = opt.id
INNER JOIN opr_card oc
ON oc.oper_id = opt.id
INNER JOIN vis_fin_message vf
ON opt.id = vf.id
INNER JOIN com_country cc
ON cc.code= opt.merchant_country
AND op.card_id = vf.card_id
WHERE opt.clearing_sequence_num > 1
AND opt.part_key >= DATE '2024-03-01'
AND opt.msg_type IN ('MSGTPAMC', 'MSGTPACC')
AND op.participant_type = 'PRTYISS'
Что дает следующий результат:
"CARD_NUMBER" "TRANSACTION_TYPE" "TRANSACTION_DATE" "ACCOUNT_NUMBER" "SOURCE_AMOUNT_CURRENCY" "SOURCE_AMOUNT" "AUTH_CODE" "MCC" "TERMINAL_ID" "POS_TERMINAL_CAP" "POS_ENTRY_MODE" "CAPTURE_DATE" "TRANSACTION_AMOUNT" "TRANSACTION_CURRENCY" "MERCHANT_NAME" "MERCHANT_COUNTRY" "CLEARING_SEQUENCE_NUM" "CLEARING_SEQUENCE_COUNT" "OPER_TYPE"
"1234567891234567" "05-PURCHASE" 25-MAR-24 "004000485011" "USD" -2020 "718515" "5734" "TERMID01" "9" "01" 27-MAR-24 2020 "060" "ACQUIRER NAME " "BM" 2 2 "OPTP0000"
Но можно ли заставить столбец F - «SOURCE_AMOUNT» возвращать значение с двумя десятичными знаками?
Итак, в приведенном выше примере -2020 должно отображаться как -20,20.
Я пытался сделать to_char, как показано ниже, но это неправильно и не работает.
case opt.oper_type
when 'OPTP0000' then to_char (coalesce (-vf.sttl_amount,0)999999.99)
else vf.sttl_amount
end as "SOURCE_AMOUNT",
Любая помощь будет здорово, пожалуйста.
Привет @TheNomad, это НОМЕР (22,4), но я не верю, что после запятой когда-либо будет больше двух цифр, поэтому я этого не совсем понимаю.
Ах, это для некоторых валют есть особые случаи. Например, в JPY нет десятичных знаков, а в CLF — 4 десятичных знака. Поэтому, скорее всего, точность равна 4, потому что она предназначена для охвата любой ситуации, даже если внутри вы можете использовать только 2. Для аудита и расследований лучше всего сохранять то, что вы фактически получаете (в вашем теоретическом случае, 4 десятичных цифры, ЕСЛИ вы буду работать с CLF).
Вы на 100% уверены, что ваше требование верно? Я имею в виду, что в 2020 году должно быть 20.20? И что это применимо к каждой записи каждой валюты, а не является каким-то сумасшедшим исключением где-то, зависящим от какого-то другого маркера (например, отрицательные значения в зависимости от OPTP0000)?
Ах, ок, спасибо @TheNomad, это имеет смысл. Играя с разными форматами, я до сих пор не могу заставить десятичную точку отображаться в нужном месте. Я продолжаю получать десятичную точку после суммы. Но мне нужно, чтобы -2020 отображалось как -20,20, но я получаю всевозможные странные результаты, продолжая перемещать букву G. Есть идеи? Просмотр документации с использованием 99G999D99 должен дать мне то, что мне нужно, но в конечном итоге все равно отображается как -2020,00, что неверно.
Да, в конечном итоге все это конвертированные валюты, поэтому все в долларах США. Я могу оставить это как 2020, зная, что это $$cc, но если есть способ сделать это $$.cc, это будет выглядеть лучше для меня.
Давайте продолжим обсуждение в чате.
@headspace — 99G999D99 будет показывать только ненулевые значения после десятичной точки, как сказано в документации. 99G999D00 всегда будет показывать после себя две цифры, даже если они равны нулю; и 99G990D00 также всегда будет показывать ноль перед десятичной дробью для значений меньше 1. Именно это и показал Littlefoot — не знаю, почему вы изменили их обратно на 9. Если вы храните значения в центах/пенсах/что-то еще, вам нужно будет разделить на 100 - но опять же, вы уверены, что это правильно?
Привет @AlexPoole. Я только пытался поиграть со значениями, так как мой вывод был неправильным. Если я выполню 99G999D00, результат будет -2020,00. Итак, исходя из этого, мне нужно будет разделить на 100, как вы упомянули выше, но я не понимаю, как это сделать.
Ответы 2
Примените TO_CHAR с соответствующей моделью формата ко всему CASE выражению, например.
TO_CHAR (
CASE opt.oper_type
WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount, 0)
ELSE vf.sttl_amount
END,
'999G990D00') AS SOURCE_AMOUNT
К этому значению будет применен разделитель тысяч и символ десятичной точки. Проверьте документацию на наличие других возможностей.
Хотя это не имеет отношения к вашему вопросу: старайтесь не использовать двойные кавычки в Oracle; в подавляющем большинстве случаев они не нужны. Поскольку вы использовали их в качестве псевдонимов столбцов/выражений, и все они написаны заглавными буквами с символом подчеркивания между словами, нет никакой разницы между source_amount, SOURCE_AMOUNT, "SOURCE_AMOUNT" и подобными.
Спасибо @littlefoot и обратил внимание на двойные кавычки.
select oc.card_number,
case opt.oper_type
when 'OPTP0000' then '05-PURCHASE'
when 'OPTP0020' then '06-CREDIT VOUCHER'
else 'other' end as Transaction_Type,
opt.oper_date as TRANSACTION_DATE,
op.account_number,
case vf.sttl_currency
when '840' then 'USD'
end as "SOURCE_AMOUNT_CURRENCY",
TO_CHAR (
CASE opt.oper_type
WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount /100, 0)
ELSE vf.sttl_amount /100
END,
'99G999D00') AS SOURCE_AMOUNT,
op.auth_code,
opt.mcc,
opt.terminal_number as TERMINAL_ID,
vf.pos_terminal_cap,
vf.pos_entry_mode,
opt.part_key as CAPTURE_DATE,
TO_CHAR (
CASE opt.oper_type
when 'OPTP0000' THEN COALESCE (-vf.oper_amount /100, 0)
ELSE vf.oper_amount /100
END,
'99G999D00') as TRANSACTION_AMOUNT,
COALESCE(ccu.name,opt.oper_currency) TRANSACTION_CURRENCY,
opt.merchant_name,
COALESCE(cc.visa_country_code,opt.merchant_country) merchant_country,
opt.clearing_sequence_num,
opt.clearing_sequence_count,
opt.oper_type
FROM opr_operation opt
INNER JOIN opr_participant op
ON op.oper_id = opt.id
INNER JOIN opr_card oc
ON oc.oper_id = opt.id
INNER JOIN vis_fin_message vf
ON opt.id = vf.id
INNER JOIN com_country cc
ON cc.code= opt.merchant_country
AND op.card_id = vf.card_id
INNER JOIN com_currency ccu
ON ccu.code= opt.oper_currency
AND op.card_id = vf.card_id
WHERE opt.clearing_sequence_num > 1
AND opt.part_key >= DATE '2024-03-01'
AND opt.msg_type IN ('MSGTPAMC', 'MSGTPACC')
AND op.participant_type = 'PRTYISS'
Хорошо, это работает, чтобы дать мне результат поверх ответа @Littlefoot. Спасибо
Какой тип
sttl_amount? Это НОМЕР или (VAR)CHAR?