|
???
SELECT Title
FROM Production.Document
WHERE Document LIKE '%vital safety components%'
|
|
|
|
|
Hi Guru's,
I made a basic search filtering query, it searches for the predefine (default) values when user does not place any values on search fields, my search query works fine but not until having the sort option, below is my query and my db structure:
TABLE:
ListingID int
HLCN varchar(6)
HousePrice money
Bathrooms float
Bedrooms int
HomeLocation varchar(50)
ContactName varchar(50)
EmailContact varchar(50)
PhoneContact varchar(50)
Address varchar(100)
SquareFeet varchar(50)
YearBuilt varchar(50)
LotSize varchar(50)
Garage varchar(50)
Notes varchar(255)
QUERY:
declare @sort nvarchar(50)
set @sort ='HomeLocation'
Select
listingID,
HLCN,
HousePrice,
Bedrooms,
Bathrooms,
HomeLocation,
ContactName
from
listings
where
houseprice between 0 and 100000000
and
bathrooms >= 0
and
bedrooms >= 0
and
homelocation like '%' + '' + '%'
order by
case @sort
when 'HomeLocation' then HomeLocation
when 'HousePrice' then HousePrice
end
when I sort by HousePrice it return correct resultm but when I sort by Homelocation it returned
Cannot convert a char value to money. The char value has incorrect syntax. I'm wondering whey It's causing char-to- money conversion error when I'm not passing char values and also this only happens when I'm using HomeLocation as sort.
Please advice,
Thanks
Dom
|
|
|
|
|
I believe it is because the HomeLocation is VARCHAR type while the HomePrice is MONEY type. I try the following and it works (LoginID is VARCHAR, UserID is INT):
DECLARE @sort NVARCHAR(50)
SET @sort ='LoginID'
SELECT * FROM tblUser
ORDER BY
CASE @sort
WHEN 'LoginID' THEN CONVERT(VARCHAR, LoginID)
WHEN 'UserID' THEN CONVERT(VARCHAR, UserID)
END
Eliz.K
www.oin1.com
|
|
|
|
|
Works great Eliz
Thank you
Dom
|
|
|
|
|
Works great Eliz
Thank you
Dom
|
|
|
|
|
Hi,
I am using SQL Server 2005, and I have set up a full text search on my products table.
I am using ASP.NET 1.1. In my search.aspx page I have 4 textboxes used in the search. But only one of the textboxes are required for the search. I'm not sure hot to construct my SELECT statement based on the given criteria because some of the textboxes can be blank.
I have a title textbox where the user can type a couple of words that he wants to find in the book's title. How do I specify this in the SELECT clause, as it will not be an exact match, but a combination of words in the title.
I would appreciated it if someone could get back to me.
Regards
ma se
|
|
|
|
|
need suggestions to this problem
Data is like this(WHEN RETURNING FROM DIFFERENT TABLES)
column A column B
7001 NULL
7002A A
7002AB AB
7003VA VA
7003AV AV
7004ABC ABC
7005XYZ XYZ
NEED TO CONVERT TO LOOK LIKE THIS FOR FUTURE COMBINATIONS
column A column B
7001 NULL
7002A A
7002AB A
7002AB B
7003VA V
7003VA A
7003AV A
7003AV V
7004ABC A
7004ABC B
7004ABC C
ANY HELP WILL BE HELPFUL
|
|
|
|
|
Hello everyone,
I am working on an Windows Application which requires to access a remote database. I would like to be able to obtain the size of the tables before they are used to populate the DataTable in the Windows Application so, the data tranmission is displayed to the user.
I am using "mysql_fetch_array(SHOW TABLE STATUS FROM MyDatabase LIKE 'MyTableName')" command to get the Data_Length of the table. But I am unable to get this working. Can someone be kind enough to tell me how I can get the Table Size (Byte) from Windows Application (C#)?
Thank you very much and have a great day
Khoramdin
|
|
|
|
|
|
I want to display all data from my table except first three colunms. Is there any way to use "select * " query to see all colunms except first three? I'll greatly appreciate any help.
Thanks
|
|
|
|
|
You can do it in front-end programming languages really easily (in .NET you would iterate through the relevent columns returned in a DataReader).
It is really difficult to do within the database - you would have to dynamically construct a select statement based upon the table's column meta data  . Generally, doing "select * from xxxx" or doing an insert without explicitely specifying each column is considered to be bad practice for production code.
|
|
|
|
|
Create a view on the table ignoring or selecting only those columns required
then use "SELECT *" on the query
Regards
KP
|
|
|
|
|
hi all
i have an empoyee table with a column Dateofbith
i need to select the employees whose birthday with in coming 14 days..
how i select it.?
thnks in advance..
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
For SQL-Server it would be something like:
select * from Employee
where datepart(dy, BirthDate) between datepart(dy, GetDate()) and datepart(dy, GetDate()) + 14
or 365 + datepart(dy, BirthDate) between datepart(dy, GetDate()) and datepart(dy, GetDate()) + 14 The "or" condition traps the situation where today is in the last couple of weeks of December and the employee's birthday falls at the start of January. You might need to play with the boundary conditions (i.e. the "365" and "14" constants).
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
hi,
Thank u for ur reply.
I am using MSSQL 2000.
it shows some error ..
wat you mean by 'dy'??
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
You could do this by selecting all records where the dateofbirth was between getdate() and dateadd(dd, getdate(), 14).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you for ur reply..
I think, there is some logic problem in ur reply.
Means
"where the dateofbirth was between getdate() and dateadd(dd, getdate(), 14)"
Suppose an employe 'Sanker' whose DOB is "29/july/1982"
According ur reply,in select query the getdate value is 27/July/2007 and add 14 to it will return 10/aug/2007
do you think employee 'Sanker' will select from ur query??
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
It would.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I got the solution..
i fell this is very simple one..
select EmployeeID,EmployeeName
from
TblEmployeeMaster
where
datediff
(
dd
,convert(datetime,'1900/'+cast(month(getdate()) as varchar)+'/'+cast (day(getdate()) as varchar),111)
,convert(datetime,'1900/'+cast(month(DateOfBirth) as varchar)+'/'+cast (day(DateOfBirth) as varchar),111)
) between 0 and 14
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Hi there,
I have problem with caling my T-SQL scalar function.
SET ANSI_NULLS ON<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
-- =============================================<br />
-- Author: <Author,,Name><br />
-- Create date: <Create Date, ,><br />
-- Description: <Description, ,><br />
-- =============================================<br />
CREATE FUNCTION dbo.func(@param int)<br />
RETURNS bit<br />
AS<br />
BEGIN<br />
DECLARE @ret bit;<br />
<br />
IF EXISTS (SELECT id FROM [dbo].myTmpTab WHERE id=@param)<br />
BEGIN<br />
set @ret=1;<br />
END<br />
ELSE<br />
BEGIN<br />
set @ret=0;<br />
END<br />
RETURN @ret;<br />
END<br />
GO
I want to call it in C# code, but i cant it even call in MSSQL Server Mamagment Studio (SELECT myTmpDB.dbo.func(1)) and i get this msg:
Msg 557, Level 16, State 2, Line 1<br />
Only functions and extended stored procedures can be executed from within a function.
Is there any1 who know how to solve it? Im quite sure it would work on Sybase as i have done smthin similar about year ago 
-------------------
n00b @ pr0grAmiNg
|
|
|
|
|
|
I did it But its useless for me to call function from stored procedure because i cant get my ret value.
-------------------
n00b @ pr0grAmiNg
|
|
|
|
|
Seems to work with SQL-Server 2000 (after I changed the table name to one of mine). Could "func" be a reserved word under 2005?
|
|
|
|
|
names of tabels and function are changed so thats not an issue. Maybe its caused by the fact DB is on MSSQL 2000 Server? Ill do some research this weekend.
-------------------
n00b @ pr0grAmiNg
|
|
|
|
|
Bizarre - I've copied your code exactly and it works fine for me.
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
