CODEWITHSUNDEEP
mysql
Thomas Williams
Thomas Williams

Reputation: 1548

Why isn't the user-define variable working

I am trying to use a subquery in an if statement. If I do the following it works

SELECT `Currency`, IF((SELECT `id` FROM `upload_currency` WHERE `CurrencyText`=`Currency`) > 0,(SELECT `id` FROM `upload_currency` WHERE `CurrencyText`=`Currency`),1) FROM `upload_salesinvoice`

and I get the output 1,1,USD 3,1,EUR 2,1,1,1 which is correct

However if I try to make my query more efficient like this, it gives me all 1's instead.

SELECT `Currency`, IF(@val:=(SELECT `id` FROM `upload_currency` WHERE `CurrencyText`=`Currency`) > 0,@val,1) FROM `upload_salesinvoice`

I will explain a bit more I have a table called upload_currency and in this table I have this layout

id 1, CurrencyText GBP

id 2, CurrencyText EUR

id 3, CurrencyText USD

So my subselect should return 1 for GBP, or 1 for an empty string, 2 for EUR and 3 for USD, but the second query is failing and giving me all 1's

So it looks like my user-defined variable is failing for some reason.

Upvotes: 0

Views: 62

Answers (2)

Solarflare
Solarflare

Reputation: 11106

The reason your query is not working is that you are assigning the value of the comparison to your variable; for MySQL, the assignment will read like

@val:=((SELECT `id` FROM `upload_currency` WHERE `CurrencyText`=`Currency`) > 0)

which is either 1 or 0, not the id. So you will have to add the parentheses and use 

IF( (@val:=(SELECT `id` FROM `upload_currency` WHERE `CurrencyText`=`Currency`)) 
    > 0, @val, 1)

That being said: I think what you are trying to do in a very complicated way is actually simply a left join:

select u.currency, coalesce(c.id,1) 
from upload_salesinvoice u
left join upload_currency c 
on c.CurrencyText=u.Currency

It will be faster too.

Upvotes: 2

MTK
MTK

Reputation: 3580

According to the mysql manual: https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement ...

EDITED :

 SELECT `Currency`, @val := (
   SELECT `id` FROM `upload_currency` 
   WHERE `CurrencyText`=`Currency`
 ), 
 IF(@val > 0,@val,1) FROM `upload_salesinvoice`;

Upvotes: 0

Related Questions