|
HI,
if i do like that select convert(decimal(18,3),(-0.0268000000000000000)*1000/1000) I am getting -0.027 which is again rounding.can you help me how to truncate the value with out rounding.
|
|
|
|
|
I realize someone already replied, but here is my 2 cents ...
select convert(decimal(18,3),(convert(integer,(-0.0268000000000000000)*1000)))/1000
|
|
|
|
|
select convert(int,convert(decimal(18,5),-0.026880000000000)*100000.0)/100000.0
|
|
|
|
|
DECLARE @val AS VARCHAR(255)
SET @val='-0.026880000000000'
SELECT SUBSTRING(@val,1,CHARINDEX('.',@val)+4)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
|
I have written one VIEW. it returns 10 rows.
Now I need to show only 3rd row data.
Please suggest me.
|
|
|
|
|
If you have to show three rows from View then use query down:
SELECT TOP 3 * FROM viewName
If youhave to show only third row of view then use query:
SELECT TOP 1 * FROM viewName where Columnanme=conditionvalue
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I don't want show all 3 records
one 3rd one ..etc 4th ,5th it could be any one of 1st to 10th..
not all
Let us see....
My view name is TestView. it has only one column customer_name.
now tell me I need to see only 6th row only....
|
|
|
|
|
SELECT TOP 1 * FROM viewName where Columnanme=conditionvalue
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Please look into my previous reply....
|
|
|
|
|
select top 1 * from TestView where customer_name='CustomerNameInRowSix'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Using one of my tables as an example:
select clt_name from (select top 3 clt_name
FROM ClientsNet order by 1) dt
where clt_name not in (select top 2 clt_name FROM ClientsNet order by 1)
Hope that helps.
Tim
|
|
|
|
|
Tim's answer is correct but in certain cases it's more efficient to use following syntax (and in other cases this is unefficient)
select * from (select top 3 * from tablename order by columname) aliasforquery1
except
select * from (select top 2 * from tablename order by columname) aliasforquery2
Mika
|
|
|
|
|
i got something new thanx
|
|
|
|
|
Dear All,
I have a question about joining 2 table.
e.g. table1 contains
user---inv---amount---data2
a --- 001--- 1000 --- dfsdf
st --- 001--- 1200 --- sdfs
b --- 002--- 1300 --- sdf
c --- 004--- 1400 --- sdf
e.g. table2 contains
cid--- data--- FromCountry
001--- acd--- USA
001--- efg --- USA
001--- okk --- USA
002--- cce --- CHI
003--- eer --- UK
003--- err --- UK
004--- edr --- VOC
I would like to construct a table base on table1, and include the field "FromCountry" from table2.
I use
select table1.user, table1.inv, table2.FromCountry
From table1, table2,
Where table1.inv = table2.cid
when joining 2 table, I found there are duplicated records generated...
How can I just get the "FromCountry" from any one of "cid" record in table2? The perfer result on new table is:
user --- inv --- FromCountry
a --- 001 --- USA
st --- 001 --- USA
b --- 002 --- CHI
c --- 004 --- VOC
PLEASE HELP
|
|
|
|
|
here it is:
SELECT DISTINCT table1.[USER],table1.inv,table2.fromcountry
FROM table1,table2
WHERE table1.inv=table2.cid
ORDER BY table1.inv
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
modified on Wednesday, August 13, 2008 8:37 AM
|
|
|
|
|
Hi,
I have employee table.
ID empno salary empname
1 1 5000 sdf
2 2 4000 sdf
3 3 4000 sdfas
4 4 4500 sdf
5 5 5000 k
6 8 1000 TT
I tried to retrieve the non duplicate rows from the using.
Ex: the output should be
ID empno salary empname
1 1 5000 sdf
2 2 4000 sdf
4 4 4500 sdf
6 8 1000 TT
It should not be display the duplicate values in the salary.
Thanks
Sujatha
|
|
|
|
|
SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM
tblName GROUP BY Id HAVING COUNT(Id) = 1))
--> This query gives you a non duplicate rows
SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM
tblName GROUP BY Id HAVING COUNT(Id) > 1))
--> This query gives you a duplicate rows
|
|
|
|
|
Your first return all rows, second query return no rows.
Regards
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
here it is:
SELECT <br />
(SELECT TOP 1 t1.id FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS id ,<br />
(SELECT TOP 1 t1.empno FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empno, <br />
salary,<br />
(SELECT TOP 1 t1.empname FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empname<br />
FROM dbo.TableName<br />
GROUP BY salary<br />
ORDER BY id ASC
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I tried to execute the above query in the msacess. it throws an error msg as
you tried to execute a query that does not include the specified expression 'id' as part of an aggregate function
Kindly help ASAP.
|
|
|
|
|
remove line ORDER BY id ASC
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
It's working.
Thanks a lot.
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi all,
I am using SQL Server 2005.
I wrote a storedprocedure for getting data from data base.
It is compiled successfully but when i run that SP by passing params i getting error.I think my stored procedure is not correct.
My Stored Procedure:
////////////////////////////////////////
USE [ProxyAds]
GO
/****** Object: StoredProcedure [dbo].[USP_AdsReport] Script Date: 08/13/2008 10:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AdsReport](
@specialization smallint = null,@country smallint = null,@state varchar(50)= null,@city varchar(50)= null,@from datetime = null,@to datetime = null,@user varchar(50) = null) as
BEGIN
SET NOCOUNT ON;
DECLARE @query nvarchar(1000)
SET @query =N'SELECT adsCounter.int_AdID,
transAds.str_Name,transAds.str_UserID,adType.str_Description,
adSubtype.str_Description,specialization.str_Description,
adsCounter.tinyint_Duration,adsCounter.str_FocusState,
adsCounter.str_FocusCity,adsCounter.dt_DateDisplayed
FROM ADMIN_ADSCOUNTER AS adsCounter
JOIN TRANS_ADS AS transAds ON adsCounter.int_AdID = transAds.int_AdID JOIN CONFIG_SPECIALIZATION AS specialization ON adsCounter.smallint_Specialization = specialization.smallint_Code
JOIN CONFIG_COUNTRIES AS country ON adsCounter.smallint_CountryCode = country.smallint_Code JOIN CONFIG_ADTYPE AS adType ON transAds.smallint_AdType = adType.smallint_Code JOIN CONFIG_ADSUBTYPE AS adSubtype ON transAds.smallint_AdSubType = adSubtype.smallint_Code WHERE 1=1'
if (@specialization != null OR @specialization != 0)
SET @query = @query + ' AND adsCounter.smallint_Specialization = ' +@specialization
if(Ltrim(Rtrim(@country)) != null OR @country != 0)
SET @query = @query + ' AND adsCounter.smallint_CountryCode ='+@country
if(Ltrim(Rtrim(@state)) != '' OR @state != null)
SET @query = @query + ' AND adsCounter.str_FocusState ='''+@state+''
if(Ltrim(Rtrim(@city)) != '' OR @city != null)
SET @query = @query + ' AND transAds.str_FocusCity ='''+@city+''
if(@from is not null)
SET @query = @query + ' AND transAds.dt_Modifiedon >='+@from
if(@to is not null)
SET @query = @query + ' AND transAds.dt_Modifiedon <='+@to
if(Ltrim(Rtrim(@user)) != '' OR @user != null)
SET @query = @query + ' AND transAds.str_UserID'''+@user+''
print @query
Execute sp_sqlexec @query
END
/////////////////////////////////////////////
Execution in SQL Management Studio:
USE [ProxyAds]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[USP_AdsReport]
@specialization = NULL,@country = 7,@state = NULL,@city = NULL,@from = NULL,@to = NULL,@user = NULL
SELECT 'Return Value' = @return_value
GO
Error :
=======
Conversion failed when converting the nvarchar value 'SELECT adsCounter.int_AdID,transAds.str_Name, transAds.str_UserID,
adType.str_Description,adSubtype.str_Description, specialization.str_Description,adsCounter.tinyint_Duration,adsCounter.str_FocusState,adsCounter.str_FocusCity,adsCounter.dt_DateDisplayed
FROM ADMIN_ADSCOUNTER AS adsCounter JOIN TRANS_ADS AS transAds
ON adsCounter.int_AdID = transAds.int_AdID JOIN CONFIG_SPECIALIZATION AS specialization ON adsCounter.smallint_Specialization = specialization.smallint_Code JOIN CONFIG_COUNTRIES AS country
ON adsCounter.smallint_CountryCode = country.smallint_Code
JOIN CONFIG_ADTYPE AS adType ON transAds.smallint_AdType = adType.smallint_Code JOIN CONFIG_ADSUBTYPE AS adSubtype
ON transAds.smallint_AdSubType = adSubtype.smallint_Code WHERE 1=1 AND adsCounter.smallint_CountryCode =' to data type smallint.
.............Please help me where i went wrong in SP
Thanks in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
