mysql query error when using if condition

Question

 SELECT tu.begin_date, tm.MAGAZINE_ID,`MAGAZINE_NAME`, CASE WHEN ts.no_of_issues IS NULL THEN 1 ELSE ts.no_of_issues END AS Subscription_Type, 
 CASE WHEN 
   IF(tu.customer_currency = 'USD', 54 * tu.customer_currency) 
   ELSE IF(tu.customer_currency = 'CAD', 1.0250 * tu.customer_currency) 
   END AS sale_price_inr,
 tu.developer_proceeds as orginal_rate, tu.customer_currency as orginal_currency
 FROM `tbl_itunes_report` tu
 LEFT JOIN tbl_magazine_subscription ts ON ts.subscription_key = tu.sku_key
 LEFT JOIN tbl_magazine_issue ti ON ti.PurchaseKey = tu.sku_key AND ti.OS_SELECT = 0
 LEFT JOIN tbl_magazine tm ON tm.magazine_id = ts.magazine_id
 OR tm.magazine_id = ti.magazine_id
 WHERE `product_type_identifier` LIKE 'IA%'
 AND
 (
     ts.subscription_key IS NOT NULL
     OR ti.PurchaseKey IS NOT NULL
 )
 AND tu.begin_date >= '2012-04-01' AND tu.begin_date <= '2013-04-01'

This is my query. But i excecute this query i got an error that

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ELSE IF(tu.customer_currency = 'CAD', 1.0250 * tu.customer_currency) END A' at line 3

if any one know this please help me. i am new to this

thanks in advance

peterm · Accepted Answer · 2013-04-03 13:35:58Z

2

Change

 ...
 CASE WHEN 
 IF(tu.customer_currency = 'USD', 54 * tu.customer_currency) 
 ELSE IF(tu.customer_currency = 'CAD', 1.0250 * tu.customer_currency) 
 END AS sale_price_inr
 ...

to

...
CASE WHEN tu.customer_currency = 'USD' THEN 54 * tu.customer_currency
     WHEN tu.customer_currency = 'CAD' THEN 1.0250 * tu.customer_currency 
 END AS sale_price_inr
...

answered Apr 3, 2013 at 13:35

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amit · Accepted Answer · 2013-04-03 13:38:44Z

1

There is different syntax for IF statement and IF function, you are using IF function with syntax of IF Statement, please change below line

IF(tu.customer_currency = 'USD', 54 * tu.customer_currency) 
 ELSE IF(tu.customer_currency = 'CAD', 1.0250 * tu.customer_currency)

to

CASE WHEN tu.customer_currency = 'USD' THEN 54 * tu.customer_currency
     WHEN tu.customer_currency = 'CAD' THEN 1.0250 * tu.customer_currency 
 END AS sale_price_inr

OR

IF(tu.customer_currency = 'USD', 54 * tu.customer_currency, 1.0250 * tu.customer_currency)

answered Apr 3, 2013 at 13:38

Amit

1,3858 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql query error when using if condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related