Hi Sachin,
I appreciate your question about the functionality in TOAD and would also be keen to see the answer to this.
However, I think your code may be more explicit and therefore easier to maintain in the future if you corrected the signs in the body of the query rather than trying to swop the sign at the end.
e.g.
CASE
WHEN ACC.DC_MRK_PKEY = 'C' THEN [remove the minus here]ACC.ACIT_AMT_RC
ELSE [insert minus here]ACC.ACIT_AMT_RC
END
Regards
Trevor
Von: sachinshivraman [mailto:bounce-sachinshivraman@toadworld.com]
Gesendet: Mittwoch, 8. November 2017 16:55
An: toaddatapoint@toadworld.com
Betreff: [Toad Data Point - Discussion Forum] TOAD corrects MUTLIPLY to a negative no to Unary_Minus and later gives error for Missing Right Parenthesis
TOAD corrects MUTLIPLY to a negative no to Unary_Minus and later gives error for Missing Right Parenthesis
Thread created by sachinshivraman
Hello All,
I am using the below code for adding to different results and later multiply the result by -1.
SELECT DISTINCT
ACIT.DOCUMENT_NO,
ACIT.CURR,
( SUM (
CASE
WHEN ACC.ACIT_FUNC_ID = 'IONSIONS'
AND ( ACC.POST_CODE = 'NSZRC'
OR ACC.POST_CODE = 'NSRC')
THEN
CASE
WHEN ACC.DC_MRK_PKEY = 'C' THEN -ACC.ACIT_AMT_RC
ELSE ACC.ACIT_AMT_RC
END
ELSE
0
END)
+ SUM (
CASE
WHEN ACC.POST_CODE = 'CURRDIFF'
THEN
CASE
WHEN ACC.DC_MRK_PKEY = 'D' THEN -ACC.ACIT_AMT_RC
ELSE ACC.ACIT_AMT_RC
END
ELSE
0
END))
* (-1) AS TAX_ACCPT_FX
FROM SAPR3.“ACCT” ACIT
WHERE (ACC.DOCUMENT_NO <> ’ ’ AND ACC.DOCUMENT_NO = ‘0999999994’)
GROUP BY ACC.DOCUMENT_NO, ACC.CURR
However, when I use the VISUALISE tool, it automatically changes the multiplication to the below:
SELECT DISTINCT
ACIT.DOCUMENT_NO,
ACIT.CURR,
( SUM (
CASE
WHEN ACC.ACIT_FUNC_ID = 'IONSIONS'
AND ( ACC.POST_CODE = 'NSZRC'
OR ACC.POST_CODE = 'NSRC')
THEN
CASE
WHEN ACC.DC_MRK_PKEY = 'C' THEN -ACC.ACIT_AMT_RC
ELSE ACC.ACIT_AMT_RC
END
ELSE
0
END)
+ SUM (
CASE
WHEN ACC.POST_CODE = 'CURRDIFF'
THEN
CASE
WHEN ACC.DC_MRK_PKEY = 'D' THEN -ACC.ACIT_AMT_RC
ELSE ACC.ACIT_AMT_RC
END
ELSE
0
END))
* (1 unary_minus ) AS TAX_ACCPT_FX
FROM SAPR3.“ACCT” ACIT
WHERE (ACC.DOCUMENT_NO <> ’ ’ AND ACC.DOCUMENT_NO = ‘0999999994’)
GROUP BY ACC.DOCUMENT_NO, ACC.CURR
And later when the query is run gives an error “ORA-00907: missing right parenthesis”
Is this a TOAD issue? Or
Am i doing something incorrect ?
Appreciate some inputs.
Thanks,
Sachin
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
Münchener Rückversicherungs-Gesellschaft (“Munich Reinsurance Company”) is a reinsurance company organized under the laws of Germany. In some countries, including in the United States,
Munich Reinsurance Company holds the status of an unauthorized reinsurer. Policies are underwritten by Munich Reinsurance Company or its affiliated insurance and reinsurance subsidiaries.
Certain coverages are not available in all jurisdictions.
Münchener Rückversicherungs-Gesellschaft
Aktiengesellschaft in München
Königinstraße 107, 80802 München
Sitz der Gesellschaft: München
Amtsgericht München, HRB 42039
Vorsitzender des Aufsichtsrats: Dr. Bernd Pischetsrieder
Vorstand: Dr. Joachim Wenning, Vorsitzender;
Giuseppina Albo, Dr. Thomas Blunck, Dr. Doris Höpke,
Dr. Torsten Jeworrek, Hermann Pohlchristoph, Dr. Markus Rieß,
Dr. Peter Röder, Dr. Jörg Schneider