Function in a SQL query. Is it possible?

Question

So, in the previous question I asked, previous question

and didn't get any answer, so I have an idea from the previous question of mine, to make it more compact, so this what I thought, is it possible to call a function in a SQL query?

So this is the condition I had now, I have a function to convert price using API, and I want to sort it by price but the problem is each product have different currency.

Ex:

Product A - USD 1,800

Product B - IDR 1,900,000

Product C - EUR 1,000

So if I just sort by the price from the database, it'll messed up, because the currency are different. So I want to make the all the data from mysql is automatically changed to price in USD (default, here is when the function needed)

$args=array(
 'meta_key' => 'monthly',
 'meta_value' => 'yes',
 'posts_per_page' => 12,
 'paged' => $paged
);
$mam_global_fields = ', price.meta_value'; // I want to change this 
$mam_global_join = " 
 INNER JOIN " . $wpdb->postmeta . " AS currency ON (" . $wpdb->posts . ".ID = currency.post_id AND currency.meta_key = 'monthly_currency')
 INNER JOIN " . $wpdb->postmeta . " AS price ON (" . $wpdb->posts . ".ID = price.post_id AND price.meta_key = 'monthly_price')
";
mam_global_orderby="total_price ASC"; 

...To something like this, but I don't know how to do this, but it's something like this or some way similar like this or my previous question

$mam_global_fields = ', price.meta_value, '. convert_price('price.meta_value', 'currency.meta_value') . ' AS total_price';

I appreciate any kind of helps or ideas

• Bernd Buffen: sorry, you can do it. You can write a very small piece of C-code to write a User-Function (UDF). That you can call like every other implemented MySql function. see : codeguru.com/cpp/data/mfc_database/misc/article.php/c12615/…
• Niet the Dark Absol: Sounds like you may need to re-write the code to something more manageable. It's a daunting task but it will save you a lot of trouble long-term.
• Bernd Buffen: @Khrisna Gunanasurya - i have done it often, to calculate some things or call Web / Rest APIs. I will look for a better tutorial ("hello World"), but i cant found it in this moment. Note the UDF-Interface ist not 100% compatible between MySQL and MariaDB. So look at the correct Doku
• Bernd Buffen: @Khrisna Gunanasurya - Here the MySQL Doku: dev.mysql.com/doc/refman/5.7/en/adding-udf.html
• Ctznkane525: i recommend a local store of the currencies (SOURCE_CURRENCY, DEST_CURRENCY, CURRENCY_DATE, EXCHANGE_RATE)...so you dont make the same call for the same source currency/dest currency and date...because 1. performance and 2. companies charge for the number of calls you do...3. if you do too many...you get denial of service error...only make the call if the row doesnt exist in your local store

Answer 1

Why not use CASE?

SQLFiddle: http://sqlfiddle.com/#!9/fd178a/15/0

Example:

SELECT id, name, CASE currency
 WHEN "USD" THEN price * 1.1
 WHEN "RUB" THEN price * 0.7
 WHEN "CND" THEN price * 2.8
END as price
FROM book
ORDER BY price DESC

Where 1.1, 0.7 and 2.8 are base coeffs for convertions in base / main currency.

UPDATE

... or you can use MySQL JSON type to create something like this:

CREATE TABLE book (id INT PRIMARY KEY, name VARCHAR(60), price REAL, currency VARCHAR(10));
INSERT INTO book VALUES (1, "book 1", 1.20, "USD");
INSERT INTO book VALUES (2, "book 2", 100.0, "CND");
INSERT INTO book VALUES (3, "book 3", 12.20, "RUB");
SET @currency := '{"CND": 0.8, "RUB": 1.7, "JPN": 0.34, "USD": 1.0}';
SELECT id, name, JSON_EXTRACT(@currency, CONCAT('$.', currency)) * price as price
FROM book
ORDER BY price DESC;

SQLFiddle: http://rextester.com/JNLNE4909

• Khrisna Gunanasurya: Ahhh I got your point, so I just need to make the price is checked before the SQL, thanks! It's more simple then using a stored procedure I think

Answer 2

You can use stored function, this function can take currency type and value as input parameter, and return usd value. Then you can call this function in your sql query where you need.

• Khrisna Gunanasurya: I will really appreciate it if you really willing to write me the function
• Kedar Limaye: @KhrisnaGunanasurya- The link below gives sql query for currency conversion.Lot of sql works needs to be done stackoverflow.com/questions/39882140/…
• jose_bacoy: krishna, you will thank us someday IF YOU WRITE your own function. just do it.
• Khrisna Gunanasurya: @âńōŋŷXmoůŜ well I don't asking for someone to write the code, it's him the one that offering to write the function for me. Like I said, I appreciate any kind of helps or ideas. So if someone wants to write the codes for me, of course I'll appreciate it.
• SevincQurbanova: I have seen that you tagged mysql. I work with oracle sql and write stored procedures in pl/sql. I don worked with mysql.