Toad World® Forums

TOAD corrects MUTLIPLY to a negative no to Unary_Minus and later gives error for Missing Right Parenthesis

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

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