|
It means that Initiated (the column) is an int (a number) and 'NA' is a varchar (not a number). It expects you to put a number in a column that is marked as a number. It does not expect you to put in varchars.
|
|
|
|
|
Hi,
I have two select statements that retrieve a couple of rows as follows:
Col 1
-----
A
B
C
D
Col 2
-----
1
2
3
4
However I want to combine these two columns in one select statement to produce:
Col 1 Col 2
----- -----
A 1
B 2
C 3
D 4
Please help
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I recently did an article that included a pivot, maybe it can help you. If you are struggling with the existing examples I can only recommend you keep looking, sooner or later you will find one that makes sense.
|
|
|
|
|
I recently did an article that included a pivot, maybe it can help you. If you are struggling with the existing examples I can only recommend you keep looking, sooner or later you will find one that makes sense.
article
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes,
I've found something but it doesn't work well, maybe you can advise me as I'm not professional when it comes to SQL..
CREATE TABLE TEST(F1 NUMBER(1),F2 NUMBER(1),F3 NUMBER(1))
/
INSERT INTO TEST VALUES(1,2,3)
/
SELECT X.C1 AS "COLUMN NAME", Y.C2 AS "VALUE"
FROM
(SELECT column_name AS C1 from user_tab_columns where table_name = 'TEST') X,
(
SELECT F1 AS C2 FROM TEST
UNION
SELECT F2 FROM TEST
UNION
SELECT F3 FROM TEST
) Y
/
The bloody output is:
COLUMN NAME VALUE
------------------------------ ----------
F1 1
F1 2
F1 3
F2 1
F2 2
F2 3
F3 1
F3 2
F3 3
9 rows selected.
Why is it matching every left value with all the values?? Please help Holmes
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
This worked for me but the column names are not dynamic. You could put then into another table and do a join
<br />
--And you can keep out of my database please<br />
DECLARE @Tbl Table(F1 int,F2 int,F3 int)<br />
<br />
INSERT INTO @Tbl VALUES(1,2,3)<br />
<br />
SELECT *<br />
FROM @Tbl<br />
<br />
<br />
SELECT 'F1' Col, F1 [Value]<br />
FROM @Tbl T <br />
UNION <br />
<br />
SELECT 'F2' Col, F2 [Value]<br />
FROM @Tbl T <br />
UNION <br />
<br />
SELECT 'F3' Col, F3 [Value]<br />
FROM @Tbl T <br />
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi
I've an UPDATE statement inside a stored procedure, when I execute the SqlCommand.ExecuteNonQuery() function I get a return value of -1, but I need to get the # of rows affected by the update statement.
Any Idea how to do this?
Thanks
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Add an OUT parameter to your stored procedure and using it, return the number of affected rows. To resolve it inside the procedure use @@ROWCOUNT.
|
|
|
|
|
Thanks alot, it worked! 
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
declare a variable and, after the update,
set @Var = @@Rowcount<br />
Select @Var Records
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks alot, it worked!
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Or something that can allow us to select column names from a one-row table where a specific column value exists
I mean, I want to get the column names of a table that has for example the value '1' in the first and only row of that table
Example:
A B C
-- -- --
0 0 1
The output should be:
C
Please tell me if you can make this happen from Sql.
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
modified on Saturday, December 13, 2008 3:48 AM
|
|
|
|
|
It could be done but not in 1 select statement.
A combination of a while loop to get the column names from the system info views and a select on the table to test the value of the column could acheive it.
What happens when 2 or more column have a value?
I suspect this is a really badly designed table. I recommend that you look into the data structure if it is possible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey Holms,
Mycroft Holmes wrote: I suspect this is a really badly designed table
No, it's "terribly designed" and no, there's no way I can modify the structure
Mycroft Holmes wrote: What happens when 2 or more column have a value?
It will return those Column names just like rows.. like
A
B
C
Thanks for your help though!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You might look into unpivot, I've never used it though.
Sounds like an 80s legacy system to me 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yah, tell my Holmes, do you know how to transpose columns with rows in a select statement, that should be pretty doable and I see it everywhere when googling but cant apply it
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I m trying to connect two informix server but problem is that DSN continously chages so that i coudnt connect..plz give me solution...
|
|
|
|
|
I m trying to connect two informix server through .Net but problem is that DSN continously chages so that i coudn't connect..plz give me solution...
|
|
|
|
|
What possible business reason could you possibly have that the servers DSN changes. I can understand an occassional change, say once a decade but "constantly" seems a little weird to me.
As you connectin relys on stable properties I see no way this could be acheived. It would require foreknowledge of the servers name/address
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello.
In my DB exist a filed with stName name.
How can I delete rows that have a same stName except one of them with SQL.
Many thanks in advance.
|
|
|
|
|
Ahh did this last week, I used a combination of rank and partition over to put the results of the IDs and their ranking into a CTE and then deleted all the rows with a rank > 1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a WinForm on which i have a gridview. After changing i am making this call
int result=employeeObject.UpdateEmployee(ds.GetChanges());
int UpdateEmployee(Dataset ds)
{
int res=da.Update(ds); <<ds contains modified Rows exception occurs here>>
return res;
}
I am getting folowing Exception.
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
I googled but i am unable to troubleshoot the problem.
|
|
|
|
|
You need to ensure the UpdateCommand is properly set. If you are working with straightforward data and used a SqlCommandBuilder when creating and populating the DataSet, it can usually infer this information. Step into that line and see what the UpdateCommand property of the DataAdapter is.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
What i am doing is.
Populating dataset using SqlDataAdapter.
Then providing this dataset to datagridview.
After doing some changes(deleting a row,changing some cells adding a row) in gridview i get those changes using
ds.GetChanges();
now i am updating the database using adapter's update method.
I am not getting this statement.
You need to ensure the UpdateCommand is properly set.
How can i set the UpdateCommand?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
I'm sorry, my knowledge is very humble when it comes to sql, I didn't know this up until now, thanks Holmes
You are my sql reference here in cp