|
We have only numeric bank account numbers .
|
|
|
|
|
But they're still not numbers. As Bernhard said, you can't add two bank account numbers together.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
chdboy wrote: We have only numeric bank account numbers
The fact that they have numeric digits does not make them numbers.
A street address has numeric digits but that doesn't make it a number.
|
|
|
|
|
Hi,
I am getting the following error on MySQL stored procedure but I can't understand the reason! can any help please..
here is the error followed by full code:
[SQL]
IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
[Err] 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 'IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET' at line 1
full code:
BEGIN
DECLARE param_item_id_v1 int;
DECLARE param_item_id_v2 int;
DECLARE param_file_cutoff_days int;
DECLARE param_days_since_last_visit int;
DECLARE param_account_number BIGINT;
DECLARE param_price_list_id INT;
DECLARE param_v1_fees DOUBLE(11, 5);
DECLARE param_v2_fees DOUBLE(11, 5);
DECLARE param_item_description_v1 VARCHAR(255);
DECLARE param_item_description_v2 VARCHAR(255);
SET param_account_number = (SELECT account_number FROM visits WHERE visit_id = param_visit_id);
SET param_price_list_id = (SELECT price_list_id FROM visits WHERE visit_id = param_visit_id);
SET param_item_id_v1 = (SELECT item_id FROM price_list_items WHERE item_code = param_v1);
SET param_item_description_v1 = (SELECT item_name FROM price_list_items WHERE item_id = param_item_id_v1);
SET param_v1_fees = (SELECT price_list_item_prices.item_price FROM price_list_item_prices INNER JOIN price_list_items ON price_list_item_prices.item_id = price_list_items.item_id WHERE price_list_items.item_code = param_v1 AND price_list_item_prices.price_list_id = param_price_list_id);
SET param_item_id_v2 = (SELECT item_id FROM price_list_items WHERE item_code = param_v2);
SET param_item_description_v2 = (SELECT item_name FROM price_list_items WHERE item_id = param_item_id_v2);
SET param_v2_fees = (SELECT price_list_item_prices.item_price FROM price_list_item_prices INNER JOIN price_list_items ON price_list_item_prices.item_id = price_list_items.item_id WHERE price_list_items.item_code = param_v2 AND price_list_item_prices.price_list_id = param_price_list_id);
SET param_file_cutoff_days = (SELECT system_param_value FROM system_param WHERE system_param_name = 'FILE_CUTOFF_DAYS');
IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET param_days_since_last_visit = DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_no order by created_date DESC LIMIT 1));
IF (param_days_since_last_visit > param_file_cutoff_days) THEN
BEGIN
INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v1, param_v1, param_item_description_v1, param_v1_fees, param_created_user);
END;
ELSE
BEGIN
INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v2, param_v2, param_item_description_v2, param_v2_fees, param_created_user);
END;
END IF;
END;
ELSE
BEGIN
INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v1, param_v1, param_item_description_v1, param_v1_fees, param_created_user);
END;
END IF;
UPDATE visits SET is_received = TRUE, recieved_user = param_created_user, recieved_date = NOW() WHERE visit_id = param_visit_id;
END
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: param_file_no
I don't see this declared as a variable!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
it's passed as parameters. sorry i forgot to metion it. it's like this:
IN param_file_no int, IN param_visit_id int, IN param_v1 int, IN param_v2 int, IN param_created_user int
so what could be the reason for my problem?!
Technology News @ www.JassimRahma.com
|
|
|
|
|
I cannot immediately see an error so what I would suggest is running each assignment statement outside of the stored procedure noting down the results.
Then use what you have noted down as the input for your exists clause.
Keep working through the code in this manner until you find the error - yes, debugging is tedious however the gold at the end of the rainbow is finding the error.
Good luck
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
that's what I did and I got the error mentioned in my first post.
[SQL]
IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
[Err] 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 'IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET' at line 1
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: that's what I did and yet what I am seeing in your code are variables such as visit_id and param_file_no rather than the hard-coded values of these variables.
I don't need to see the values but I am not sure you understand what I was trying to communicate in my previous post - basically what I am saying is run each part of the stored procedure one line at a time noting the results then run the next line - each time running the line outside of the stored procedure.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
that's exactly what I did Christopher.
All lines are giving correct results with no error.
The line I am having problem with is the following:
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
That's the same line mentioned earlier.
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: _noorder by
That's your issue - you are missing a space before the order keyword.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
no it's not. sorry i think it's paste issue. this is the line as it is in MySQL server;
SET param_days_since_last_visit = DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_no order by created_date DESC LIMIT 1));
Technology News @ www.JassimRahma.com
|
|
|
|
|
Replace
Jassim Rahma wrote: LIMIT 1)
with
LIMIT 0,1)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
[SQL]
IF EXISTS(SELECT visit_id FROM visits WHERE file_no = 80016) THEN
BEGIN
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = 80016 order by created_date DESC LIMIT 0,1)));
[Err] 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 'IF EXISTS(SELECT visit_id FROM visits WHERE file_no = 80016) THEN
BEGIN
SET' at line 1
Technology News @ www.JassimRahma.com
|
|
|
|
|
i have a feeling the problem is with the DATEDIFF but i don't know what is it..
Technology News @ www.JassimRahma.com
|
|
|
|
|
hmm
I was right
the problem is with the DATEDIFF but I still don't know what is it..
I just used TO_DAYS instead and everything is working fine now.
SET param_days_since_last_visit = (SELECT TO_DAYS(NOW()) - TO_DAYS(created_date) FROM visits WHERE file_no = param_file_no ORDER BY created_date DESC LIMIT 1);
Thanks,
Jassim Rahma[^]
RMC[^] - Universal Medical Equipments[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Good evening from Brazil. I granted select and update to the user but I still can't run the procedure below. What are the other privileges I need to grant to him?
delimiter
drop procedure if exists sp_optimistic_locking
create procedure sp_optimistic_locking
(
p_from_account int,
p_to_account int,
p_transfer_amount numeric(10,2),
p_status int,
p_message varchar(100)
)
sql security invoker
modifies sql data
begin
declare l_account_balance1 numeric(10,2);
declare l_account_balance2 numeric(10,2);
declare l_account_timestamp1 timestamp;
declare l_account_timestamp2 timestamp;
select balance, account_timestamp into l_account_balance1, l_account_timestamp1
from account_balance where account_id = p_from_account;
if l_account_balance1 >= p_transfer_amount then
start transaction;
-- call long_running_validation(p_from_account);
select balance, account_timestamp into l_account_balance2, l_account_timestamp2
from account_balance where account_id = p_from_account for update;
if (l_account_balance1 <> l_account_balance2 or
l_account_timestamp1 <> l_account_timestamp2) then
rollback;
set p_status = -1;
set p_message = concat('Transaction canceled due to previous update of account ', p_from_account);
else
update account_balance set balance = balance - p_transfer_amount where
account_id = p_from_account;
update account_balance set balance = balance + p_transfer_amount where
account_id = p_to_account;
commit;
set p_status = 0;
set p_message = 'transaction ok';
end if;
else
set p_status = -1;
set p_message = 'Transaction canceled due to account balance less than desired withdrawal';
end if;
select 'Transaction code', p_status;
select 'Transaction message', p_message;
end;
|
|
|
|
|
Good morning from Brazil. I'm running MySQL with root privileges and with the read and write permissions set but I still can't load a file with load_file:
delimiter //
drop procedure if exists sp_loadfile //
create procedure sp_loadfile
(
p_file_name varchar(128)
)
begin
declare my_text text;
set my_text = load_file(p_file_name);
select concat(p_file_name, ' has ', length(my_text), ' bytes.') as 'Output';
end;
//
-rw-rw-rw- 1 eduardo eduardo 5 Set 1 11:04 test.txt
mysql> call sp_loadfile('/var/www/mysql/PL_loadfile/test.txt');
+--------+
| Output |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
|
|
|
|
|
Hi,
I have SQL 2008 express R2 installed.
I have visual studio express 2010 and 2012 also installed.
Windows 8.
I'm trying to create a new table but I get this strange message:
Quote: This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)
I googled the net but didn't find a solution to the problem. (replacing the dsref80.dll of 2012 with the one from 2010)
Did anybody encountered this issue and solved it?
|
|
|
|
|
I have never used database tools in Visual Studio and I see no reason to.
I prefer to just write the SQL statement or use the designer in SSMS.
|
|
|
|
|
I'm using the 'SQL Server Management Studio' (=SSMS).
he error that I get appears while I'm trying to create a table there.
|
|
|
|
|
Ah, then I don't know. Just write some SQL.
"Would you like some help with that?" -- Clippy
|
|
|
|
|
Thanks, but I'm looking to solve this problem so I could have access to the designer.
|
|
|
|
|
Right. I've been using the Express versions too and had no trouble with the designers. SQL Server 2012 Express has been causing other trouble though.
Did you try a repair install?
|
|
|
|
|
OK. So I removed the current installation but right now
I can't install the management studio because of compatibility issues. (Windows 8)
Besides going back to Windows 7, Any advice??
|
|
|
|