|
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.
|
|
|
|
|
I would have a look a Pivot Querys[^]
that would be the closest thing that you could do.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
it would be a pivot query or custom solution based in table schema
|
|
|
|
|
im facing problem while making connection with mongol db to nodejs project please can any one help ???
|
|
|
|
|
Please update your question with what the actual problem is, and what you have tried.
We can't read your mind nor your monitor.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
please help me, i have been having sleepless nights on this code
Select max(convert(int,SubString(Employee_ID,PATINDEX('%[0-9]%',Employee_ID),Len(Employee_ID)))) from Employee
this code returns a value of 20, when i used sql server 2012
from a column(varchar()) that contains the following;
emp001
emp005
emp020
emp018
but now, in sql server compact 4.0 column(nvarchar()), the following errors are thrown;
Error Code: 80040E14
Message : Data conversion failed. [ OLE DB status value (if known) = 2 ]
Minor Err.: 26306
Source : SQL Server Compact ADO.NET Data Provider
Num. Par. : 2
i am using vb in vs 2012
please what am i not understanding?
thanks a lot
|
|
|
|
|
Reduce the select statement to its base components, something like 'select max(employee_id) from Employee). Then add in each clause one at a time until it breaks.
Understandable, 'select max(employee_id) from Employee' is not what you want, but it may help find the issue.
Tim
|
|
|
|
|
i found my way out by creating a new column, ID as a int data type,
i then used
Select Max(ID) from Employee
to get the maximum value.
finally i just send a string value of Emp"&ID to Employee_ID Column
Thanks for your Reply
|
|
|
|
|
I have found my way out
|
|
|
|
|
Member 10218952 wrote: please what am i not understanding? Atomicity.
It looks a lot like a formatted field, with a string, and a number that gets padded (for displaying purposes). Each field should hold an atomic value; the example consists of two facts, and should have been split over two fields. That's assuming that "emp" is not a constant.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i found my way out by creating a new column, ID as an int data type,
i then used
Select Max(ID) from Employee
to get the maximum value.
finally i just send a string value of Emp"&ID to Employee_ID Column
Thanks For your Reply
|
|
|
|
|
Which is half of what Eddy suggested, the ID is correct but storing the "Emp" & ID is wrong, this information has no value and is only required in the UI/Reports. It should be created in the output, not stored.
This argument goes out the window if you need to maintain backwards compatibility with a crappy design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: This argument goes out the window if you need to maintain backwards
compatibility with a crappy design.
No it doesn't
You can always keep such stuff in a view, sp or a query.
I'm accepting one argument only for keeping a crappy design, and that is: If it's not broken, don't fix it.
Ooh, it still feels like Monday to me.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
thanks to you all.
guess i will learn more about atomicity.
life became very much easier when i used
Select Max(Employee_ID) from Employee
then using substring to trim the output to taste.
thanks to you all, i really appreciate it
|
|
|
|
|
pssst - see the little green up arrow on the right of the message, give Eddy's rep a tickle and up vote the response (the best way to say thanks)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi. When I try to run the following code, I get the error I wrote in the subject.
delimiter
drop procedure if exists example_cursor;
create procedure example_cursor() reads sql data
begin
-- declare variables
declare var_wage numeric(8,2);
declare var_employee_id int;
declare var_department_id int;
-- declarar done state
declare done int default 0;
-- create cursor for table
declare cur1 cursor for
select employee_id, salary, department_id from employees;
-- set done to 1 if data wasn't found
declare continue handler for not found set done = 1;
-- open cursor
open cur1;
-- start loop
emp_loop:loop
-- get data from cursor
fetch cur1 into var_employee_id, var_wage, var_department_id;
if(done = 1) then
leave emp_loop;
end if;
end loop emp_loop;
-- close cursor
close cur1;
end;
CREATE TABLE `employees` (
`EMPLOYEE_ID` int(11) NOT NULL auto_increment,
`SURNAME` varchar(40) NOT NULL,
`FIRSTNAME` varchar(40) NOT NULL,
`ADDRESS1` varchar(40) NOT NULL,
`ADDRESS2` varchar(40) NOT NULL,
`ZIPCODE` varchar(6) NOT NULL,
`DATE_OF_BIRTH` datetime NOT NULL,
`PHONENO` varchar(12) NOT NULL,
`MANAGER_ID` decimal(8,0) NOT NULL,
`SALARY` decimal(8,0) NOT NULL,
`STATUS` varchar(9) NOT NULL,
`DEPARTMENT_ID` decimal(8,0) default NULL,
`COMMENT_ID` decimal(8,0) default NULL,
`DEPARTMENT_NAME` varchar(40) default NULL,
`ADJUSTED_SALARY` decimal(8,0) default NULL,
`HIRE_DATE` datetime default NULL,
PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
|
Have a look at this PL/SQL cursor example[^].
The example towards the bottom may help(Cursor with a While Loop).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks GuyThiebaut. I Solved the problem even with the warning: I just printed the content with concat.
delimiter
drop procedure if exists sp_exemplo_cursor;
create procedure sp_exemplo_cursor() reads sql data
begin
declare var_salario numeric(8,2);
declare var_empregado_id int;
declare var_departamento_id int;
declare flag_nao_encontrado int default 0;
declare csr cursor for
select employee_id, salary, department_id from employees;
declare continue handler for not found set flag_nao_encontrado = 1;
open csr;
exemplo_loop:loop
fetch csr into var_empregado_id, var_salario, var_departamento_id;
if flag_nao_encontrado = 1 then
leave exemplo_loop;
end if;
select concat(var_empregado_id, ' ', var_salario, ' ', var_departamento_id);
end loop exemplo_loop;
close csr;
end
|
|
|
|