|
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
|
|
|
|
|
SQL Server 2008R2.
Do all defaults need a constraint name? Why would you or would you not use a constraint name?
Example of a SSMS created table script:
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyDateColumn] DEFAULT (getdate()) FOR [MyDateColumn]
GO
ALTER TABLE [dbo].[MyTable] ADD DEFAULT ((0)) FOR [MyBitColumn]
GO
Thanks
|
|
|
|
|
From what I read on table constraints[^]
It's to maintain backwards compatibility.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thank you! As I have a tendensy to skim the definitions I miss things.
|
|
|
|
|
It's always a good idea to use constraint names. Makes it easy to disable/drop a constraint.
|
|
|
|
|
Hello,
I am executing the following script:
use[SomeDatabase]
DECLARE @TEST_XML TABLE (Column_XML XML)
DECLARE @VAR VARCHAR(MAX) = 'EXECUTE dbo.sp_GetData 3229';
INSERT @TEST_XML EXEC (@Var)
SELECT * FROM @TEST_XML
any clues as to why I am receiving this error message, "The FOR XML clause is not allowed in a INSERT statement"? How can I get around this?
I really just need to get this data into a temp table.
|
|
|
|
|
iCanDivideByZero wrote: any clues as to why I am receiving this error message Because "dbo.sp_GetData" uses a FOR XML clause.
iCanDivideByZero wrote: How can I get around this? Remove the XML clause.
iCanDivideByZero wrote: I really just need to get this data into a temp table. Than what's the use of using XML?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have one column table with negative and positive values and want to display positive and negative values in different columns using SQL Query.
Column
-10000
-17000
16000
25000
output should be like
A B
-----------------
-10000 16000
-17000 25000
|
|
|
|
|
And how do you determine that 16000 and -10000 belong into the same column, but not 16000 and -25000? What's the rule behind that?
|
|
|
|
|
One of the problems you will have with creating a query to do this is that the column of numbers will most likely not have an equal number of positive and negative values. Even if it does, I don't know of a way to create a single query to create the output you illustrate. The best I could do in this situation is the following which will output the same number of rows as in the original table but simply put a NULL value in the column that does not apply:
SELECT
CASE
WHEN NumberField > 0 THEN NumberField
ELSE NULL
END AS PositiveNumbers,
CASE
WHEN NumberField < 0 THEN NumberField
ELSE NULL
END AS NegativeNumbers
FROM NumbersTable
Of course, you also have to decide what exactly to do with values of zero.
-NP
Never underestimate the creativity of the end-user.
|
|
|
|
|
thanks! that is useful for me.
|
|
|
|
|
With SQL Server 2012?
SELECT A.[Column] A
, B.[Column] B
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column] DESC) RN , [Column] FROM [Table] WHERE [Column]<0)A
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column]) RN , [Column] FROM [Table] WHERE [Column]>0)B
ON A.RN=B.RN
|
|
|
|
|
A good example of CAN be done versus SHOULD be done...
But, well done...
|
|
|
|
|
What? I didn't use CTEs.
|
|
|
|
|
Erk - I mean I like ROW_NUMBER but that is just... I agree with Tim!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am doing some experimenting with using SQL Azure as the data store for a Access 2010 Application. I have created a test database in Access, migrated it to SQL Azure using Microsoft's Server Migration Assistant, and can connect to the migrated Tables both using the links created by the Migration Assistant and using ODBC connections which I have created manually, using the master credentials for the Azure Server. I have created a new login on the Azure server, associated it with a user name for the test database, and given that user read and write privileges on that database. However, when I try to create an ODBC connection with the new login information, the Create New DataSource wizard fails when I 'Finish', saying that "Access to selected database has been denied". Does the limited login/user need some additional privileges, either on the SQL master database or on the test database, or am I missing something else? Almost all the material on the web seems to assume that the developer wants to use the master credentials in this situation (which does, indeed, work), but I don't!
|
|
|
|
|
Hi,
I have a design question that I was hoping a professional could answer. Lets say I have the following one-to-many table relationship between an Account and its Addresses.
create table account (
acctkey int,
acctname varchar(100),
active bit
)
create table acctaddress (
acctkey int,
addkey int,
street1 varchar(100),
isprimary bit
)
An account can have many addresses but only one can be the primary. Does it make sense to include a soft link in the main table to hold the key of the address that is the primary? ie.
create table account (
acctkey int,
acctname varchar(100),
active bit,
primaryaddkey int <
)
This is purely to return the primary address of account (with the best performance in mind). Is this a bad design? Does the need to sync the account and acctaddress tables outweigh the performance advantages?
I would appreciate any thoughts on this.
Thanks
Ryan
|
|
|
|
|
I would go on the side of storing information regarding addresses in the address table rather than the account table.
So have a column on the address table showing it to be the primary address - something like addressContext nvarchar(max) .
In the end it's not a deal-breaker but it does mean that you are de-coupling your data(not sure that is the correct term) by only storing account information in the account table and address information in the address table.
Also don't use varchar(100) as you will run out of room sooner than you think - use nvarchar(max) instead, there will not be much of a performance hit and you are allowing for large addresses.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I'd disagree with the nvarchar(max), I would only ever use nvarchar if it was a requirement to store unicode and I would place a size limit on the address, make it large but don't use max unless you are intending to use it which an address will never do.
If some pillock has a couple of thousand characters in his address then truncate the idiot, not the data, the pillock!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would never do the PrimaryAddKey flag on the Account table, I don't even care about the performance difference (unless you have mega addresses it would be minuscule) it introduces a vulnerability into your schema and is therefore wrong.
I would however create an Account view that included the primary address but then I'm a great beleiver in views
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I actually raised this question because I'm at odds with the design. Thanks for the sanity check
|
|
|
|