|
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??
|
|
|
|
|
Nope, I'm on Win 7.
You could possibly use a virtual Win 7 system. Hopefully someone else will have something more useful.
|
|
|
|
|
So I managed installing Management studio but now I'm trying to create a database and I get:
Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)
It looks like I need to update something but I don't know what...
|
|
|
|
|
That could be an access problem. I forgot exactly when I've seen that.
|
|
|
|
|
|
Excellent! I may need that too.
|
|
|
|
|
edit
I found the source of the problem. I forgot to close the cursor;
Hello. My second SP is printing the last row twice and I don't know what to do about it. The second one is calling the first.
delimiter //
drop procedure if exists sp_overdue_sales //
create procedure sp_overdue_sales()
begin
drop temporary table if exists overdue_sales_tmp;
create temporary table overdue_sales_tmp as select
sales_id, customer_id, sale_date, quantity, sale_value
from sales where sale_status = 'A' limit 10;
end;
//
delimiter //
drop procedure if exists sp_issue_invoice //
create procedure sp_issue_invoice()
begin
declare var_sales_id int;
declare last_sale int default 0;
declare sales_csr cursor for
select sales_id from overdue_sales_tmp;
declare continue handler for not found set last_sale = 1;
open sales_csr;
sales_repeat: repeat
if last_sale then
leave sales_repeat;
end if;
fetch sales_csr into var_sales_id;
select var_sales_id;
until last_sale end repeat;
end;
//
| var_sales_id |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
+--------------+
| var_sales_id |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+
modified 29-Aug-13 11:38am.
|
|
|
|
|
-------------------
1 | 2 | 3 |
-------------------
1|2|3|1|2|3|1|2|3|
-------------------
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
How TO Get data This Format Using Select Query
First is Main Header Like 1 2 3
Or Second Is Sub Header Like 1|2|3 is Sub Column For Each Main Header
|
|
|
|
|
Kailashkumar Raghvani wrote: First is Main Header Like 1 2 3 Or Second Is Sub Header Like 1|2|3 is Sub Column For Each Main Header
I'm not sure, I understand you correctly! You want columns with subcolumns? That's not possible. SQL is not for spreadsheets it's for databases. A column is a column and each row of a query has the same number of columbs. Of course you can combine different queries but you can't group columns to be subcolumns.
|
|
|
|