|
SELECT Work_Requests_1.id, Work_Requests_1.work_req_name, Work_Requests_1.description, Work_Requests_1.req_name,
Work_Requests_1.cost_center,
CASE [type] WHEN 'Inst' THEN 'Installation' WHEN 'Dein' THEN 'Deinstall' WHEN 'Powe' THEN 'Power' WHEN 'Rout' THEN 'Routing' WHEN 'Fire' THEN
'Firewall' WHEN 'IPRq' THEN 'IP Request' WHEN 'DHCP' THEN 'DNS/DHCP' WHEN 'Hire' THEN 'New Hire' WHEN 'Tick' THEN 'Trouble Ticket' WHEN 'Asst'
THEN 'Asset Management' WHEN 'Spec' THEN 'Spectrum Allocation' WHEN 'Othr' THEN 'Other' ELSE 'Not Specified' END AS type,
Work_Requests_1.type AS lookup_Type, Work_Requests_1.location, CONVERT(VARCHAR, CONVERT(DATETIME, Work_Requests_1.compdate, 107),
107) AS compdate, Work_Requests_1.phone, Work_Requests_1.email, CONVERT(VARCHAR, CONVERT(DATETIME, Work_Requests_1.sendate, 107),
107) AS sendate, CONVERT(VARCHAR, CONVERT(DATETIME, Work_Requests_1.wishdate, 107), 107) AS wishdate,
CASE status WHEN 'INP' THEN 'In Progress' WHEN 'COM' THEN 'Completed' WHEN 'HOL' THEN 'On Hold' WHEN 'CAN' THEN 'Cancelled' END AS status,
Work_Requests_1.status AS lookup_Status, REPLACE
((SELECT dbo.Work_Requests_Staff.name
FROM dbo.Work_Requests_Staff INNER JOIN
dbo.Work_Requests ON dbo.Work_Requests_Staff.alias = dbo.Work_Requests.lead
WHERE (dbo.Work_Requests.id = dbo.Work_Requests.id)), 'na', 'NOT Assigned') AS lead, Work_Requests_Staff_1.alias, REPLACE
((SELECT Work_Requests_Staff_1.name
FROM dbo.Work_Requests_Staff AS Work_Requests_Staff_1 INNER JOIN
dbo.Work_Requests AS Work_Requests_1 ON Work_Requests_Staff_1.alias = Work_Requests_1.contact
WHERE (Work_Requests_1.id = Work_Requests_1.id)), 'na', 'NOT Assigned') AS contact
FROM dbo.Work_Requests AS Work_Requests_1 INNER JOIN
dbo.Work_Requests_Staff AS Work_Requests_Staff_1 ON Work_Requests_1.lead = Work_Requests_Staff_1.alias
When I execute the above query it is returning an error:Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery used as an expression.
Rajee
|
|
|
|
|
Sub Queries can only return one value try using SELECT DISTINCT or use TOP 1 if approapiate
|
|
|
|
|
Greetings everybody:
I need to know how to export a database query to a .CSV file instead of to a MS Excel file.
The code is as follows to export to MS Excel, but how do I export to a .CSV file?
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryFinalResult", strOutputFilePath, True
|
|
|
|
|
you could just use string builder and build the thing yourself.
|
|
|
|
|
hi all
i need help to write a query
I have a table with the coloumns SNo ,ProductId and brandId
SNo is given as identity true
now want to retrive a ProductID whose SNo is maximum value
thank u.
siri
|
|
|
|
|
Hi....here is the query........
SELECT ProductID from tablename where SNo = (SELECT MAX(SNO) from tablename)
Regards,
Sandeep Kumar.V
|
|
|
|
|
|
Welcome........
Regards,
Sandeep Kumar.V
|
|
|
|
|
Hi all,
I've got the following function:
CREATE FUNCTION Test(....)
RETURNS @ReturnTable TALBE
(
.................
)
AS
BEGIN
DECLARE @MyTable TABLE
(
.............
)
INSERT INTO @MyTable
SELECT ......
FROM AnotherTable1 Inner Join AnotherTable2 .......
INSERT INTO @ReturnTable
SELECT ......
FROM
(
SELECT ......
FROM @MyTable
WHERE .......
UNION
SELECT ......
FROM @Table
WHERE ......
)
RETURN
END
When i call this function, an error message appears "Must declare the scalar variable "@MyTable"."
Please help me work out this problem
Thanks so much
|
|
|
|
|
vuthaianh wrote: CREATE FUNCTION Test(....)
RETURNS @ReturnTable TALBE
There's a typo here.
vuthaianh wrote: SELECT ......
FROM @Table
WHERE ......
Where is @Table declared?
Paul Marfleet
|
|
|
|
|
Hi pmarfleet,
Sorry, i made a typing mistake
@Table must be @MyTable
But the error does still appear.
Thanks again
|
|
|
|
|
I'm still not sure what your problem is, but it may have something to do with how you are using the table variable in your queries. You haven't posted all your code so I can't say for sure, but have a look at this link[^].
Paul Marfleet
|
|
|
|
|
Thanks a lot. The link you gave to me is very useful. I already sovled my problem.
Thanks again.
|
|
|
|
|
i am a biggner in .net and database..
i have herard that sql 2005 is better than 2000
i would like to know what are the additional features in 2005 that are not in sql 2000
|
|
|
|
|
http://msdn2.microsoft.com/en-us/library/ms345147.aspx
SSK.
Anyone who says sunshine brings happiness has never danced in the rain.
|
|
|
|
|
I am having some difficulty with an MSSQL insert that contains Slovakian, Croatian, Hungarian and other Eastern European characters.
I am using SQL Server Standard Edition Version 8. The Server Collation is Latin1_General_CI_AS as is the Database I am using. The fields I am using are ntext and nvarchar which I understand store data as Unicode.
The problem I am encountering is that if I paste the text directly into the table field using Enterprise Manager, then all the accented characters are correctly displayed and saved. If I insert them through via an SQL statement using Enterprise Manager (or from the website the database is connected to) then the majority of these characters do not retain their accents, etc.
However, *some* of the characters still retain their accents when inserted through the insert statement, such as š (an accented "s" in case there are display issues!), but others like ů lose them.
Changing the collation to Slovakian resolved this problem, but as the database already holds a very large amount of data containing Western European characters I am wary of making such a change should there be any knock on effects.
Is there any way I can get the SQL statement to insert these characters without altering collations, etc? I am just bemused why I can paste them directly into the table using cut and paste but not through an SQL Insert.
Help would be greatly appreciated.
|
|
|
|
|
I have no experience in this area, however I did find a Microsoft article that seems to cover the area click here.
I hope this is of some help...
You always pass failure on the way to success.
|
|
|
|
|
If using a SQL statement, ensure that you use the N prefix on any literal strings, par exemple:
INSERT INTO table VALUES ('š') -- BAD!
INSERT INTO table VALUES (N'š') -- good!The N tells SQL Server that yes, this is a National (i.e. Unicode) character string.
The reason that š works but ů doesn't is that the former is on Western European Windows codepage 1252[^] but the latter isn't. Changing the collation to Slovakian changes to codepage 1250[^] which does have this value.
You should be using parameters with your SQL statements on a website, which should not have this problem if you are using the correct types for the parameters (i.e. adVarWChar for ADO, SqlDbType.NVarChar for ADO.NET SQL Server provider).
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thank you both for answering this, you've been a great help and the characters are now displaying correctly.
Many thanks!
|
|
|
|
|
Hi All
I want to know is there any extended procedure in sql to find out by which authentication mode sql server is connected.I want to use it in c# application .
Thanks in advance
Regards
DilipRam
|
|
|
|
|
How can you use this without having to authenicate in the first place.
SQL has two modes Windows or Windows and SQL
|
|
|
|
|
Hello Everyone,
Can anybody tell me how to get top two records from the database in SQL Server 2000
For Example: I've ten records then it should return record no. 9 and 10
Thnx
|
|
|
|
|
select top 2 itemnumber
from stocktable
order by itemnumber desc
You always pass failure on the way to success.
|
|
|
|
|
first,thank for willing help me to solve my question, Q1 and Q2.
Q1. I knew "sp_who2" is using to detect who is using database.But , i have not idea that how i write at first?
Q2. Where should i put my script?
Thank again.
-- modified at 5:37 Friday 26th October, 2007
|
|
|
|
|
First of all, read the forum guidelines about naming your posts. Your subject line isn't helpful.
I don't understand your question. Do you want to run an ad-hoc SQL script that calls the SP sp_who2. If you are using Management Studio, open up a new query window for your database and execute it from there. If you are using the SQL 2000 client tools, fire up Query Analyser and do the same.
Paul Marfleet
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
