Home » SQL & PL/SQL » SQL & PL/SQL » Newbie error with IF Statement in SELECT (Oracle 10g)
Newbie error with IF Statement in SELECT [message #285472] Tue, 04 December 2007 15:38 Go to next message
icm63
Messages: 21
Registered: December 2007
Junior Member
I get error from toad as : ORA-00907: missing right parenthesis

SELECT A.TRANSACTION_ID,
A.ITEM_QUANTITY,
(IF A.GST_ON_REVENUE <>0
THEN A.DISCOUNT_ON_REVENUE - (A.DISCOUNT_ON_REVENUE/9)
ELSE -A.DISCOUNT_ON_REVENUE END IF) AS DISCOUNT
FROM
ADS.CMS_TRANSACTION_LINES A
WHERE A.TRANSACTION_DATE between to_date('01-NOV-2007','DD-MON-YYYY') and to_date('30-NOV-2007','DD-MON-YYYY')


I dont see what the big deal is, the error is within the IF statement, because when I remove it it works fine, the field DISCOUNT_ON_REVENUE has dollar GST(Sales tax) amounts in it ( double format) and I am doing a forumla to take out the tax. Any ideas ?

Ie

$112.50/9 = $12.5 tax, so non tax amount is $100

[Updated on: Tue, 04 December 2007 15:41]

Report message to a moderator

Re: Newbie error with IF Statement in SELECT [message #285474 is a reply to message #285472] Tue, 04 December 2007 15:44 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If is NOT a valid element of a select clause. Try


SELECT A.TRANSACTION_ID,
A.ITEM_QUANTITY,
decode(A.GST_ON_REVENUE,0,-A.DISCOUNT_ON_REVENUE,A.DISCOUNT_ON_REVENUE - (A.DISCOUNT_ON_REVENUE/9)) DISCOUNT
FROM
ADS.CMS_TRANSACTION_LINES A
WHERE A.TRANSACTION_DATE between to_date('01-NOV-2007','DD-MON-YYYY') and to_date('30-NOV-2007','DD-MON-YYYY')


You could have also used a case clause.
Re: Newbie error with IF Statement in SELECT [message #285476 is a reply to message #285474] Tue, 04 December 2007 15:55 Go to previous messageGo to next message
icm63
Messages: 21
Registered: December 2007
Junior Member
Thanks..

So are you not allowed to use IF in 10g SELECT, is this correct ?.

I assume CASE is supported in 10g, my book says not but thats 8i.
Re: Newbie error with IF Statement in SELECT [message #285477 is a reply to message #285476] Tue, 04 December 2007 16:13 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
correct
Re: Newbie error with IF Statement in SELECT [message #285546 is a reply to message #285476] Wed, 05 December 2007 00:34 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CASE also existed in 8i.

Regards
Michel
Previous Topic: To calculate the no. of entries of transactions in database
Next Topic: how to grant privilege on another schema to a user
Goto Forum:
  


Current Time: Thu Dec 08 22:29:43 CST 2016

Total time taken to generate the page: 0.07405 seconds