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