|
Suppose I have a table tblEmpStop
EmpID Date Reason
--------------------------------------------
1 01/01/2009 Fi
2 03/01/2009 Rs
3 03/01/2009 TS
I want to make a view/query which take from tblEmpStop n replace in Coluem Reason
EmpID Date Reason
--------------------------------------------
1 01/01/2009 Fired
2 03/01/2009 Resigned
3 03/01/2009 Temporary Stop
I use Ms SQL Server 2000 with source code:
SELECT EmpID, [Date], IF Reason = Fi, Fire; elseif Reason = Rt, Retired; elseif Reason = Rs, Resigned AS ReasonStop
FROM dbo.tblEmpStop
but it did not work...
How can i do what i want above ?
Thanks you before hand
Best regard,
Sovann
VB.Net
|
|
|
|
|
Its easiest to use CASE[^]. So your query would be like:
SELECT EmpID,
[Date],
CASE Reason
WHEN 'Fi' THEN 'Fire'
WHEN 'Rt' THEN 'Retired'
...
END AS Reason
FROM dbo.tblEmpStop
|
|
|
|
|
Thanks your for your help.
But when i run it show message like:
The Query Designer does not support the CASE SQL construct.
But it was work... 
Thanks you again..
VB.Net
|
|
|
|
|
I get the next error message from MS SQL 2000:
"There is already an object named '#Teste' in the database."
DECLARE @x int
SET @x = 5
IF @x > 1
SELECT IDField1
INTO #Teste
FROM Table1
ELSE
SELECT IDField2
INTO #Teste
FROM Table1
(...)
The snippet code above is just an example. I cannot use another temporary table name because the following code must reference #Teste only.
Do you know a solution ?
Thanks
Marcello Turnbull
|
|
|
|
|
Where is your create and drop statement for the temp table? Last I heard a temp table was supposed to be local to the scope of the procedure and it was an error to not global to the entire db. Also, you can look into the possibility of using the new table variable type in SQL Server.
|
|
|
|
|
You seem to use a local temporary table. When such table is created it exists until you either:
- disconnect from the database
- drop the table using DROP TABLE command
So now you create the table in your SELECT ... INTO statement and if you don't drop it somewhere later, you encounter that error when you run the code block the second time.
|
|
|
|
|
I agree there is no DROP statement but it is not enough to fix the problem.
Thanks
Marcello Turnbull
|
|
|
|
|
Don't quite understand. Your problem is that you try to create a table that already exists. So you must either drop it or modify your code so that you don't create the table if it already exists.
|
|
|
|
|
After you answer I added the DROP TABLE in the beginning of the code but it has not worked. I must not modify the code following the snippet.
Thank you
Marcello Turnbull
|
|
|
|
|
Could you post the whole code? Also do you receive the same error or something else?
|
|
|
|
|
DROP TABLE #Teste
DECLARE @x int
SET @x = 5
IF @x > 1
SELECT IDField1
INTO #Teste
FROM Table1
ELSE
SELECT IDField2
INTO #Teste
FROM Table1
(...)
Buddy, the error message is the same.
Thanks
Marcello Turnbull
|
|
|
|
|
That's odd. For some reason the parser interpets that the table is going to be created twice (of course that's not gonna happen since you have IF ... ELSE structure).
However, could you use the following:
DROP TABLE #Teste;
CREATE TABLE #Teste (
IDField1 int
);
DECLARE @x int;
SET @x = 5;
IF (@x > 1)
BEGIN
INSERT INTO #Teste
SELECT IDField1
FROM Table1;
END;
ELSE
BEGIN
INSERT INTO #Teste
SELECT IDField1
FROM Table1;
END;
|
|
|
|
|
Buddy
I apologize for I could not reply yesterday but it was holiday here 
Snapshots[^]
It worked so thank you very much
Marcello Turnbull
|
|
|
|
|
You're welcome 
|
|
|
|
|
I'm looking for some views/ideas on the following problem that I currently face:
We have a SQL 2000 Application with all the back end code written in SQL, controlled by a VB GUI.
Due to the nature of the app we have fairly frequent code changes to make.
These changes are implemented by a third party, as I do not have access to the production systems.
At the moment I generate lots of .sql files for the various tables, views, sp's etc that are changing and they are just run through QA one at a time.
My problem is finding a more reliable way of deploying these changes, as lots of files means one could be missed etc or ran twice which might present 'non' error errors.
Do people use batch files and the command line? or are third party tools the norm?
|
|
|
|
|
I believe that how changes are gathered varies a lot.
But in your case, why do you separate different modifications to different files instead of gathering all modifications to a single file? That way you could ensure that everything is run (only once) and that the execution order is correct (sometimes this can be an issue for example with foreign keys, triggers etc). This script cold be run using osql.
|
|
|
|
|
I'm trying to connect to my sql 2000 database "mydatabase" using JDBC for SQL Server 2000, but I failed.
I'm using IReport to connect to the database and I added the driver for sql server 2000. I made sure it runs under mixed mode (i.e: windows and sql server authentication)
I created a login user (ASPNET) user and granted permissions to him on the database (without any passwords).
The url that I use for connection is jdbc:microsoft:sqlserver://localhost:1433/mydatabase
username:ASPNET and I don't enter any passwords.
error: Login failed for ASPNET. The user is not associated with a trusted sql server connection.
When I don't enter any username or password, he tries to connect using my computer's credentials but also fails.
I don't know what's wrong.
I tried everything but I failed to connect to it.
I even installed sql server 2000 sp3 on the computer but to no avail.
Am I missing something?
How am I supposed to connect to the database?
sorry for taking so long..I hope someone could help me out.
modified on Monday, February 23, 2009 7:27 AM
|
|
|
|
|
I think what you're seeing here is a trusted connection issue. You're not connecting to Sql Server using a username and password but instead using your windows account. You'll need to add the domain account as a user to SqlServer.
Also, its worth just making sure your account isn't locked (3 wrong log-ins etc) as this produces exactly what you describe.
Regards,
Rob Philpott.
|
|
|
|
|
ok, I've just created a login user with my computer name and its domain and I specified the authentication as Windows Authentication coz otherwise a password will be required and I set the default database as "mydatabase".
This time the error is: cannot establish connection. Cannot open database requested by login. Login failed for user domain/username
Should I restart sql server or what?
|
|
|
|
|
I think its your domain and the logged on account you want to add, not the computer name. You'll then need to add that user to the Sql Server so they can access the server, and add the permission to the database you're using as well.
Regards,
Rob Philpott.
|
|
|
|
|
oh I'm sorry, I meant to say the logged on account (mine is the same as the computer name) I'm sorry for mixing things up.
Anyways, I did that, but still login failed.
Any ideas why??
|
|
|
|
|
hi
i have a report viewer containe parameter, i tried to get the parameter values from a web service but i could not because the web service return a sxd:schema not dataset. So i decide to pass those values into the parameter from regular aspx page and it worked, but i still have small problem
the dropdown list is selecting all values sperated by ','
what should i do to prevent this
thank you
|
|
|
|
|
Since this is ASP related problem, I believe that you would get much better help in ASP.NET forum[^]
|
|
|
|
|
Good Day All
i have a table that carries a Field that has data like this
10101010101010
Now the Function that adds this 1's and 0's is working like this. If its selected insert "1" else "0", So if i can interpret the above if will be
1 3 5 7 9 11 13
the only thing that am interested in is "1's", if i can display their Position in a SQL Query i will be happy
i want to take these Values from a SQl table and Bind them to a Gridview. Remember the Number of Zeor's can increase , but the Limit is 63 including the "1's" and "0's".
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
If possible, I think you should change the structure and use a child table to represent those values. It would be then far easier to handle the data.
However, if you want to have all 63 possible values in columns where the correspinding bit is on, you could use something like:
select convert(int, substring(data.field, 1, 1)) * 1,
convert(int, substring(data.field, 2, 1)) * 2,
convert(int, substring(data.field, 3, 1)) * 3,
convert(int, substring(data.field, 4, 1)) * 4,
convert(int, substring(data.field, 5, 1)) * 5,
convert(int, substring(data.field, 6, 1)) * 6,
convert(int, substring(data.field, 7, 1)) * 7,
convert(int, substring(data.field, 8, 1)) * 8
...
from (select convert(varchar(63), 10101010101010)
+ replicate('0', 63-len(convert(varchar(63), 10101010101010))) as field
) data
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
