Databases
StackOverflow2013
Postsdbo
POMysql assign a single column value to a declared value using Select case
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql assign a single column value to a declared value using Select case
    text
    copied!<p>I am stucked with following issue, I ve delcared a varibale (DOUBLE) in a stored procedure, and i need to assign a value (item price) from a table (item) to this declared variable. however, i need to get this value from a select query which uses case inside, the item price could be in 2 columns based on a logic i have to find the correct item price. Please help me solve this as when i execute it gives me a error,</p> <p>Here how is layered,</p> <pre><code> DECLARE no_more_users INT DEFAULT 0; DECLARE user_id INT DEFAULT 0; DECLARE cart_id INT DEFAULT 0; DECLARE cart_item_id INT DEFAULT 0; DECLARE user_gift_id INT DEFAULT 0; DECLARE itemPrice DOUBLE DEFAULT 0.0; SELECT CASE WHEN sale_price=0 OR sale_price IS NULL THEN (price - ( price * discount )) ELSE sale_price END INTO itemPrice FROM item WHERE item_id = p_item_id ; DECLARE checked_in_users CURSOR FOR SELECT DISTINCT ul.user_id FROM user_location ul LEFT JOIN location_section ls ON ul.location_section_id = ls.location_section_id INNER JOIN user u ON ul.user_id = u.user_id INNER JOIN user_profile up ON u.user_id = up.user_id INNER JOIN location_event le ON ul.location_event_id = le.location_event_id WHERE ul.location_id = p_location_id AND ul.location_event_id = p_event_id AND ul.checked_out_on IS NULL AND (ul.checked_in_on BETWEEN le.start_time AND le.end_time ) AND u.status = 1 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_users = 1; OPEN checked_in_users; FETCH checked_in_users INTO user_id; read_loop: LOOP </code></pre> <p>some more code...</p> <p>please note, sale_price, price, and discount are coulmns of item table, The logic is if sale_price is null or value is 0, then i actual sale price should be obtained from price coulmn. Finally what i need is asign corect item price in to previously declared varibale. Any help will be highly appriciated.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload