|
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
|
|
|
|
|
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.
|
|
|
|