Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql

Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql

Convert returned values

I have a query where one of the fields returns a 0 or 1. I'd like the
zero's to show on the report as a "No" and the one's be a "Yes". Does
anyone know how to do this?
Thanks,
ColinIn Expression Editor
=IIF(Fields!ColumnName=1,"Yes","No")
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Colin" wrote:
> I have a query where one of the fields returns a 0 or 1. I'd like the
> zero's to show on the report as a "No" and the one's be a "Yes". Does
> anyone know how to do this?
> Thanks,
> Colin
>
>

Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END

Convert report in PDF Format

Hi,

I have three differents sub-reports, each one inside a table object.

Each table has a property "page break-before" enable.

When I execute the view-report in HTML 4.0 format, the reports are executed with success, but when I export to PDF format, the property "page break-before" inserts a blank page at the end of each sub-report. How can I do to suppress the blank-pages when exporting to PDF format ?

Hi Nitini-

Without the .rdl I can't be sure, but here are few things to check. In general be aware that the behavior for defining pages is different between HTML and PDF. PDF has a strict restriction on where page breaks are defined, while HTML can be broken (essentially) at any point.

(1) The page break is defined at the table vs. the table group level.
(2) Blank space - often times unintentionally having blank space can lead to extra pages (as blacnk space is preserved).
(3) Note that page breaks in subreports are not used.
(4) Overlapping items, HTML will move items that are overlapping, PDF will not.

Hope that helps,
-JonHP

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0000013)

Select * from A

------
1.3 e-05

I am getting '1.3 e-05' values because datatype is real so that is why I am getting but want to get '0.0000013' values. Please let me know what have to that for that.

Please let me know asap.

Regards,
M Jain
NJ,USAYou can convert "REAL" into "NUMERIC" first and then
convert to varchar.

like,

select convert(varchar, convert(NUMERIC(30,20), id)) from A

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0013)

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me knowHEY NJ!

isn't it "Jersey"?

Doesn't this work?

USE Northwind
GO

CREATE TABLE A ([ID] Real)
GO

INSERT INTO A ([ID]) values(0.0013)

SELECT CONVERT(varchar(45), [ID]) FROM A
GO
DROP TABLE A
GO|||Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.000013)

select * from a

Ouput
------
1.13 e05

But I want '0.000013' to display so that is why I have written below Query.

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.00001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me know

-- Is any function using that I can remove added/padded zero suffix.

Current Expected result.
0.00001300 to 0.000013|||Did you try what I gave you?|||You use 2 different numbers:

0.0013 and 0.000013 - which one is it ?

If it is the first then just change the decimal from 6 to 4.|||Does the precision matter if you're going to varchar?

And I still want to know

What exit?

Convert real => decimal (3,2)

Folks,
How can I convert a real value to a decimal value? As long as the real
value is not zero I am getting an arithmetic overflow error.
I tried both convert and cast.
Does anyone have an idea?
Cheers
StephanzHI Thiere
there is no need of type costing ...it is implecit type costing betweer
real and decimal numbers.
_
________________________________________
________
"Stephan Zaubzer" wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz
>|||Hmm - i can only duplicate if the real has more than 1 digit to the left
of the decimal... (implicit or explicit conversions)
e.g., 1.234 converts fine, but 12.34 does not
Stephan Zaubzer wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz|||On Thu, 22 Sep 2005 19:11:47 +0200, Stephan Zaubzer wrote:

>Folks,
>How can I convert a real value to a decimal value? As long as the real
>value is not zero I am getting an arithmetic overflow error.
>I tried both convert and cast.
>Does anyone have an idea?
Hi Stephan,
As Trey already indicated: you will get this error for data that is
above 9.99 or below -9.99. In the notation "decimal(3,2)", the first
number (3) is the TOTAL number of positions; the second number (2) is
the number if digits after the decimal point. That leaves only one digit
for the integer part.
If your values range from -999.99 to 999.99, use DECIMAN(5,2) instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

convert question please help

hi experts,
i have coordinates stored in a database in form of 452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 so always take the first number then put the dot then the rest and with the second one take the first 2 numbers put the dot and then the rest, i realized that if i did it with the comma instead of the dot and then convert these 2 in string then it will change the comma to the dot automatically, the result can be also a string.
any help will be very appreciated.

thanksHey mrjoka,

Try this. Hope its what you were lookign for.

Cheers,
Justin

-----------------------
-- Declare variables
declare @.number_1 int
declare @.number_1_len int
declare @.number_2 int
declare @.number_2_len int

-- initialise variables
set @.number_1 = 452031
-- get length of number_1
set @.number_1_len = (select len(@.number_1))
set @.number_2 = 5222500
-- get length of number_2
set @.number_2_len = (select len(@.number_2))

-- Format result
select @.number_1 as old_format, CAST(left(@.number_1, 1) as varchar) + '.' + CAST(right(@.number_1, @.number_1_len-1) as varchar) as new_format
select @.number_2 as old_format, CAST(left(@.number_2, 2) as varchar) + '.' + CAST(right(@.number_2, @.number_1_len-2) as varchar) as new_format|||It's Thanksgiving, so I'm feeling especially NZDF today! Enjoy!-- ptp 20071122 see http://www.dbforums.com/showthread.php?t=1624685

DECLARE @.c VARCHAR(50)

SET @.c = '452031 5222500'

SELECT
Stuff(Left(@.c, CharIndex(' ', @.c)), 2, 0, '.')
, LTrim(Stuff(SubString(@.c, PatIndex('% [0-9]%', @.c), 50), 4, 0, '.'))-PatP|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanks|||hi experts,

Oh, stop it!

Regards,|||you stop it

never mind i found it myself with some string manipulation.

thanks for the help|||Oh geez, I must remember to insert those humour tags next time, that is, if I can remember where I've left them.

Regards,|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanksNo, but if you can post the CREATE TABLE statement, and three or more rows of data, then I could probably "wing it" for you.

The general idea would be to write an UPDATE statement that used your source column name instead of the @.c in my example to set the values for the two new columns using the code that I posted. Once you post the schema and a few rows of data, it should only take a minute to cook up and test.

-PatP|||I'm guessing that he means 452031 5222500 are the two values from the columns X and Y, and he wants a "dot" inserted after the first digit of X and after the second digit of Y. I think.
That Stuff function will work like this:

SELECT STUFF(X, 2, 0, '.') AS X, STUFF(Y, 3, 0, '.') AS Y
FROM location

But if you are going to Update that back into columns of type float, you'll probably need a cast.

Is that what you mean mrjoka?|||452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 update daTable
set x = x / 10000
, y = y / 10000|||set x = x / 10000
, y = y / 10000

That's assuming there are always 6 digits in X and 7 digits in Y.|||mrjoka said:

the result can be also a string
and:
i want just column X and Y to be converted

So its kinda unclear whether he wants to update or not|||That's assuming there are always 6 digits in X and 7 digits in Y.no it isn't :) :)|||no it isn't :) :)Eh?

-PatP|||yes!! another canadian!!

i should have said "no it isn't, eh!!"|||A somewhat belated congrats on 10K, BTW!

I must have missed something though... I think that a-dam has a point, in that Mr. Joka wants to shear two digits off the left side, not N digits from the right side. I think that you also need one more zero on your second divisor.

-PatP|||"shear two digits off the left side" would mean that 452031 and 5222500 would become 45.2031 and 52.22500 and he wanted 4.52031

my interpretation was that the decimal should be moved 5 positions to the left, so from 452031 and 5222500 to 4.52031 and 52.22500

"move n positions to the left" is the same as "divide by nth power of ten"

you are absolutely right, i did not count to 5 correctly

the revised query isupdate daTable
set x = x / 100000
, y = y / 100000

my "no it isn't" comment was in response to "That's assuming there are always [x] digits"

and of course "move n positions to the left" or "divide by nth power of ten" works just as correctly on, say, a three-digit number like 937

:)|||Without the schema, we'll never know just what Mr Joka has now... Without knowing what he's got now, we can choreograph the angels in their dance, but they'll still be confined to the head of the pin.

Mr Joka, set the angels free! In other words, please give us the schema and the sample data I requested many posts ago.

-PatP|||in post #1 he said they are float|||in post #1 he said they are float...and displayed examples of them as one unformatted string!

...and described the output as possibly being a string too!

-PatP|||actually he said "i want to change them" and of course there is no output from an UPDATE

and he said "the result can be also a string" which i took to mean it's optional

:)

Convert question

Hi,
I am importing data form a text file and I have a problem with a column
format
In the file, the numbers are are stored with this format: -54.565,49
Now I have to change the format to -54565.49 to store this value into a
decimal using DTS
Could you help me ?
ThanksDid you transpose the ',' and the '.' ? Is the original format really
-54.565,49 or -54,565.49?
"Javier" wrote:

> Hi,
> I am importing data form a text file and I have a problem with a colum
n
> format
> In the file, the numbers are are stored with this format: -54.565,49
> Now I have to change the format to -54565.49 to store this value into a
> decimal using DTS
> Could you help me ?
> Thanks
>
>|||Yes, the format is -54.565,49
The values are stored in a text file and I can not change it
Thanks
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:7853108E-79F4-4C23-805A-EEE5EDE2781C@.microsoft.com...
> Did you transpose the ',' and the '.' ? Is the original format really
> -54.565,49 or -54,565.49?
>
> "Javier" wrote:
>|||Hi
It may work if you change the regional settings, although you may want to
make the solution non-region specific! One way would to use and activeX
transformation and treat the field as a string. You can then use the replace
function in your script to remove the thousand delimiter and replace the
commas with a decimal point.
John
"Javier" <jvillegas@.elsitio.net> wrote in message
news:erY%23rClCGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am importing data form a text file and I have a problem with a column
> format
> In the file, the numbers are are stored with this format: -54.565,49
> Now I have to change the format to -54565.49 to store this value into a
> decimal using DTS
> Could you help me ?
> Thanks
>|||Using T-SQL, you could use a statement like...
Select select replace(replace(<Column Name>,'.',''),',','.')
The inside Replace function changes the decimal point to nothing at
all. The outside function changes the comma to a decimal point. The
order of operations is very important here.

Convert Query access cross tab to Query SQL Server 2005

Please help me

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

thank you

If you use SQL Server 2005,

Code Snippet

Declare @.PreparedStatement as varchar(1000);

Declare @.Query as varchar(1000);

Select @.PreparedStatement = ',[?]', @.Query ='';

Select @.Query = @.Query + Replace(@.PreparedStatement,'?',Year) From oilpalm Group By Year;

Select @.Query = Substring(@.Query,2,Len(@.Query))

Exec ('Select * from oilpalm PIVOT( SUM(Block) for Year In (' + @.Query + ')) as Pvt')

|||i'm sorry, but i sill don't understand with than query

Convert Punctuation to Spaces?

Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> Hi,
> I have a table of text. I need to search for whole words within this
text...
> For example, I need to be able to search for records that contain 'dog'
but
> not return 'hotdog' or 'dogma' for example.
> I am doing this by throwing a space around both the records in the table
and
> the search word like this:
> WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')
> The problem is that punctuation needs to be stripped out of the text so
that
> it will still find "...walking the dog."
> Is there a way to update, converting a certain set of characters into
> another character (i.e. a space) and/or to do the same thing during the
word
> search query itself?
> Thanks!

Assuming you have MSSQL 2000, you could write a UDF to remove all
punctuation characters from a string, but then you'd end up with this:

WHERE dbo.fn_RemovePunc(MyColumn) LIKE '% ' + @.SearchString + ' % '

That will probably cause a performance issue, because the UDF will be
invoked once per row during queries, although you could create a computed
column using the UDF and index it.

However, perhaps a better solution here would be to look at using full-text
indexing? The CONTAINS() predicate can do what you need, and is much more
powerful than LIKE.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:406e7002$1_1@.news.bluewin.ch...
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>> <CUT>For example, I need to be able to search for records that contain
'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
> The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

That helped tons. I got the basic "CONTAINS" predicate to work, but do not
get any results when I add "FORMSOF" into the mix. Do you see the problem
with the following?

WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
@.SearchIncludes) ')

All of the examples I found seemed to have a space and single quotes around
the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
the space or the single quotes.

Thanks!|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:dtjcc.16960$lt2.8344@.newsread1.news.pas.earth link.net...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:406e7002$1_1@.news.bluewin.ch...
> > "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> > news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> >> <CUT>For example, I need to be able to search for records that contain
> 'dog'
> >> but not return 'hotdog' or 'dogma' for example.
> >> <CUT>
> > The CONTAINS() predicate can do what you need, and is much more
> > powerful than LIKE.
> That helped tons. I got the basic "CONTAINS" predicate to work, but do not
> get any results when I add "FORMSOF" into the mix. Do you see the problem
> with the following?
> WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
> @.SearchIncludes) ')
> All of the examples I found seemed to have a space and single quotes
around
> the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
> the space or the single quotes.
> Thanks!

This may help:

http://oldlook.experts-exchange.com...Q_20711909.html

Fulltext is quite a specialized area, and it seems to have a number of
quirks, so you may want to consider posting questions in
microsoft.public.sqlserver.fulltext - you'll probably get a better response.

Simon|||> This may help:
>
http://oldlook.experts-exchange.com...Q_20711909.html
> Fulltext is quite a specialized area, and it seems to have a number of
> quirks, so you may want to consider posting questions in
> microsoft.public.sqlserver.fulltext - you'll probably get a better
response.

Thanks!|||>"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>><CUT>I need to be able to search for records that contain 'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
"Simon Hayes" <sql@.hayes.ch> replied in message
news:406e7002$1_1@.news.bluewin.ch...
>perhaps a better solution here would be to look at using full-text
> indexing? The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

Thanks Simon. The syntax needed is:

In SQL:
-- In the declarations or parameters:
@.Variable varchar(256) = 'FORMSOF(INFLECTIONAL,"word")'

-- Then, in the WHERE clause:
CONTAINS (TableName, @.Variable)

If passing the string from VB to a stored procedure, prepare the string in
VB with:
TheVariable= "'FORMSOF(INFLECTIONAL,""" & TheVariable & """)'"

Cheers!!

Convert problem

I have a 'datetime' column and I want to select the count
of today's dates and insert into another table column with
integer type (Only the number as a count). I have the
following query:
Select count(*)from dbo.stats
where convert(varchar(12),accessdate, 112) = convert
(varchar(12),GETDATE(),112) AND accessdate <> ''
I get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'select count(*)
from dbo.stats
where convert(varchar(12),started, 112) = convert(varchar
(12),GETDATE(),112)
AND accessdate <> '' to a column of data type int.
How do I fix this problem.
Thanks.> Select count(*)from dbo.stats
--^
Missing space here?
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
Did you mean
AND accessdate IS NOT NULL
? If it is a datetime column, it can't be equal to '' (empty string) since
it is *not* a string at all.
Also, if you want to make use of an index on the column, it would probably
be more efficient to say this:
WHERE accessdate >= {fn CURDATE()}
AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
AND accessdate IS NOT NULL
A|||The space is a typo error only in the post. Query works
even with '' alone (Without inserting) and the column is
not indexed column since there will be more than 1 value
for the same day............
>--Original Message--
>> Select count(*)from dbo.stats
>--^
>Missing space here?
>> where convert(varchar(12),accessdate, 112) = convert
>> (varchar(12),GETDATE(),112) AND accessdate <> ''
>Did you mean
>AND accessdate IS NOT NULL
>? If it is a datetime column, it can't be equal to ''
(empty string) since
>it is *not* a string at all.
>Also, if you want to make use of an index on the column,
it would probably
>be more efficient to say this:
>WHERE accessdate >= {fn CURDATE()}
> AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
> AND accessdate IS NOT NULL
>A
>
>.
>|||> The space is a typo error only in the post.
Then can you show your actual table structure, some sample data, and the
actual query you use (rather than transposing!!!!), then someone can try and
reproduce your error? The error actually doesn't fit with the query you've
shown; I'm guessing you are trying to execute this from EXEC or
sp_executeSQL.
> not indexed column since there will be more than 1 value
> for the same day...
Not sure what that has to do with indexing. Unique index, maybe. But you
can certainly index columns that contain duplicates, and you certainly
*should* consider indexing columns that will be used frequently in exact or
range queries, such as the one you are writing...|||Mike,
Maybe you are having problems with quotes. The error message suggests
that SQL-Server is interpreting your entire query as a varchar value.
Maybe you are building the query in a front-end tool and using single
quotes to delimited the SQL statement. In that case, you need to escape
the quotes of the statement so they end up in the final string.
Hope this helps,
Gert-Jan
Mike wrote:
> I have a 'datetime' column and I want to select the count
> of today's dates and insert into another table column with
> integer type (Only the number as a count). I have the
> following query:
> Select count(*)from dbo.stats
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
> I get the following error:
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'select count(*)
> from dbo.stats
> where convert(varchar(12),started, 112) = convert(varchar
> (12),GETDATE(),112)
> AND accessdate <> '' to a column of data type int.
> How do I fix this problem.
> Thanks.

CONVERT Problem

I am trying to convert values into a scientific notation format so that 0.7130000 would be displayed as 71.3E -01.
I tried "Select convert(float, MeanValue) as Mean from Values" to no avail.
Any ideas?
Karl
What you're looking for is really a string format -- a floating pointnumber can be displayed by the client however it sees fit (i.e.scientific notation or not). Can you do the formatting on theclient side? There is no easy way I know of to format floats inscientific notation in T-SQL.
|||

There are more than one mean and SQL Server doesnot know the mean you are looking for. I will post some samples but if you send me an email I will send you the papers on Descriptive Statistics. In the master look for three System stored procedures sp_frequencies, sp_median and sp_means run a search for them in the BOL(books online). I would also browse the T-SQL books by Ken Henderson at my local book store. Hope this helps.

SELECT harmonic_mean=
COUNT(*)/SUM(1.0/N_Of_Employees)
FROM dbo.CustomerSummary

harmonic_mean
-------
4.4220114749932304

SELECT geometric_mean=
POWER(10.0000, SUM(LOG10(N_Of_Employees*1.0))/COUNT(*))
FROM dbo.CustomerSummary

geometric_mean
-----
4.8608

|||Adam,
Thanks. I did a search and I think I am getting closer. I just tried:
Dim MyDouble As Double = 5700000000000
lblMsg.Text = MyDouble.ToString("E")
And I got it so I am going to play with it some more.
Karl

Convert Problem

Hello,

I have a stored procedure in SQL Server 2000 and I am trying to do something like this for example:
declare @.s varchar(50), @.i smallint

if isnumeric(@.s) = 1
begin
select @.i = convert(smallint, @.s)
if @.@.error <> 0
print 'error'
end
else
print 'No error'

The problem is what can I do if the value of @.s is '12.35'? This generates the 'Syntax error converting the varchar value to a column of data type smallint.' because the numeric value is actually decimal and the error is not caught by the @.@.error check.
The only alternative I can think of is to convert @.s to decimal the convert again but this is not suitable as this is for validation purposes and if the value is decimal I need to identify it and ignore it.

Is there any way to check for or handle this scenario?

Thanks for any help.
ACWhy are you checking to see if a VARCHAR(50) field contains a number?
But hey, I'm sure you know what you're doing...

if isnumeric(@.s) = 1
begin
select @.i = convert(smallint, @.s)
end
if @.@.error <> 0
print 'error'
end
else begin
print 'No error'
end|||declare @.s varchar(50), @.i smallint

set @.s = '12.35'

if isnumeric(@.s) = 1
begin
select @.i = floor(@.s) -- round(@.s, 10, 0)
if @.@.error <> 0
print 'error'
end
else
print 'No error'

select @.i|||Please note that the differences in the statement set up.

I have separated the Isnumeric into it's own If.|||Thanks for the input Peso.
However, this is not what I want to do. For validation purposes I want to catch this value if it is invalid and ignore it, not attempt to convert it to a valid value.|||Thanks georgev but this doesn't make a difference.
The error returns before the process gets to checking @.@.error.

Please note that the differences in the statement set up.

I have separated the Isnumeric into it's own If.|||I'd suggest using:DROP FUNCTION dbo.IsInt
GO
-- ptp 20070913 Test to see if a string could be an MS-SQL integer

CREATE FUNCTION dbo.IsInt(
@.pcArg NVARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE
@.result INT
, @.work BIGINT

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = 0 -- Invalid character(s)
ELSE IF 22 < DataLength(@.pcArg) SET @.result = 0 -- Impossibly long
ELSE IF @.pcArg LIKE '[0-9]' SET @.result = 1 -- Singleton digit
ELSE IF @.pcArg LIKE '[-+]%[-+]%' SET @.result = 0 -- Multiple signs
ELSE
BEGIN -- Clean input
SET @.work = Convert(BIGINT, @.pcArg)
IF @.work BETWEEN -2147483648 AND 2147483647
SET @.result = 1 -- Passed range check
ELSE
SET @.result = 0 -- Out of range
END

RETURN @.result
END
GO

SELECT c, dbo.IsInt(c)
FROM (SELECT '99999999999999999999' AS c
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS zThis probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

-PatP|||Thanks Pat, I should be able to use some pattern matching.

I'd suggest using:DROP FUNCTION dbo.IsInt
GO
-- ptp 20070913 Test to see if a string could be an MS-SQL integer

CREATE FUNCTION dbo.IsInt(
@.pcArg NVARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE
@.result INT
, @.work BIGINT

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = 0 -- Invalid character(s)
ELSE IF 22 < DataLength(@.pcArg) SET @.result = 0 -- Impossibly long
ELSE IF @.pcArg LIKE '[0-9]' SET @.result = 1 -- Singleton digit
ELSE IF @.pcArg LIKE '[-+]%[-+]%' SET @.result = 0 -- Multiple signs
ELSE
BEGIN -- Clean input
SET @.work = Convert(BIGINT, @.pcArg)
IF @.work BETWEEN -2147483648 AND 2147483647
SET @.result = 1 -- Passed range check
ELSE
SET @.result = 0 -- Out of range
END

RETURN @.result
END
GO

SELECT c, dbo.IsInt(c)
FROM (SELECT '99999999999999999999' AS c
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS zThis probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

-PatP|||I was surprised by your response, then realized that I'd written the function for INT and you wanted SMALLINT. You only need to change one line to:IF @.work BETWEEN -32768 AND 32767and you're "good to go" for SMALLINT testing.

Sorry about that oversight!

-PatP|||I think that I've found a better answer. This function checks a string argument to see if it can be an MS-SQL integer value, and it returns a string with a character for each data type that the argument could be. This won't win any speed prizes, but it avoids at least two of the pitfalls that I found in the prior function and it provides more functionality too.DROP FUNCTION dbo.IntegerTypes
GO
-- ptp 20070913 Return which types of MS-SQL integer the argument could be

CREATE FUNCTION dbo.IntegerTypes(
@.pcArg NVARCHAR(39)
)
RETURNS VARCHAR(8)
AS BEGIN
DECLARE
@.result VARCHAR(8)
, @.work NUMERIC(38)

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = '' -- Invalid character(s)
ELSE IF @.pcArg NOT LIKE '%[0-9]%' SET @.result = '' -- No digits
ELSE IF @.pcArg LIKE '[-+0-9]%[-+]%' SET @.result = '' -- Sign after sign or digit
ELSE IF 78 < DataLength(@.pcArg) SET @.result = '' -- Impossibly long
ELSE IF 78 = DataLength(@.pcArg) AND @.pcArg NOT LIKE '[-+]%' SET @.result = ''
ELSE
BEGIN -- Clean input
SET @.result = 'N'
SET @.work = @.pcArg
IF @.work BETWEEN -9223372036854775808 AND 9223372036854775807 SET @.result = @.result + 'B' -- BIGINT
IF @.work BETWEEN -2147483648 AND 2147483647 SET @.result = @.result + 'I' -- INT
IF @.work BETWEEN -32768 AND 32767 SET @.result = @.result + 'S' -- SMALLINT
IF @.work BETWEEN 0 AND 255 SET @.result = @.result + 'T' -- TINYINT
END

RETURN @.result
END
GO

SELECT c, dbo.IntegerTypes(c)
FROM (SELECT '9999999999999999999999999999999999999999' AS c
UNION ALL SELECT '99999999999999999999999999999999999999'
UNION ALL SELECT '+99999999999999999999999999999999999999'
UNION ALL SELECT '-99999999999999999999999999999999999999'
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS z-PatP|||WHERE Col1 NOT LIKE '%[^0-9]%'|||WHERE Col1 NOT LIKE '%[^0-9]%'SELECT Replicate('9', 99)-PatP

convert positive and negative numbers

I have a sales report where sales dollars are negative and credits and returns show as positive numbers. I would like to change the negative numbers to positive numbers and the positive numbers to negative numbers.
Thank you for any help.Originally posted by DeannaUTI
I have a sales report where sales dollars are negative and credits and returns show as positive numbers. I would like to change the negative numbers to positive numbers and the positive numbers to negative numbers.

Thank you for any help.

Hi,

1. Convert negative value to positive

abs(myvar)

2. Convert positive value to negative

myvar - (myvar * 2)

yours friendly,
K.Babu

Convert Password hash to clear text

I have sql server password hash table. How can i decrypt these hash
values to get password details in SQL.
THanks
A hash cannot be decrypted because different source values can yield the
same hash value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mubin The DON" <mubin.shaikh@.gmail.com> wrote in message
news:1163581072.931026.168830@.b28g2000cwb.googlegr oups.com...
>I have sql server password hash table. How can i decrypt these hash
> values to get password details in SQL.
> THanks
>

Convert Password hash to clear text

I have sql server password hash table. How can i decrypt these hash
values to get password details in SQL.
THanksA hash cannot be decrypted because different source values can yield the
same hash value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mubin The DON" <mubin.shaikh@.gmail.com> wrote in message
news:1163581072.931026.168830@.b28g2000cwb.googlegroups.com...
>I have sql server password hash table. How can i decrypt these hash
> values to get password details in SQL.
> THanks
>

Convert Password hash to clear text

I have sql server password hash table. How can i decrypt these hash
values to get password details in SQL.
THanksA hash cannot be decrypted because different source values can yield the
same hash value.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mubin The DON" <mubin.shaikh@.gmail.com> wrote in message
news:1163581072.931026.168830@.b28g2000cwb.googlegroups.com...
>I have sql server password hash table. How can i decrypt these hash
> values to get password details in SQL.
> THanks
>

Convert Oracle Trigger to MS SQL Server 2000

I am new to MS SQL and trying to get the syntax for the following trigger
correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
null ProjectSpec.ProjectValueFlag and update this sum to
ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
FOR INSERT, UPDATE, DELETE
AS
UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
(SELECT SUM(ProjectSpec.ProjectSpecValue)
FROM ProjectSpec
WHERE ProjectValueFlag IS NOT NULL
AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
AND ProjectSpec.ProjectID = :NEW.ProjectID)
WHERE ProjectInfo.ProjectID = :NEW.ProjectID--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the inserted dataset. See the BOL article "Using the inserted and
deleted Tables" for more info.
Note: Most relational DB designers frown on putting calculated values
in tables, 'cuz it can cost so/too much to maintain (data layer). The
calculated value is, usually, only of interest when a query is run
(display layer). There are exceptions - accounting dbs: "closed"
months/years - it would be faster to read the monthly total from a pre
computed column than to read & calculate the column's data. Storage of
calculated values depends on your db usage (and willingness to violate
Normalization).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQje7cIechKqOuFEgEQLJXgCfUlbviSdhvBND
2nMVBVCyxOiq+gAAoLzn
IpSs8fhllDl6qE91JzvF8DD3
=bizF
--END PGP SIGNATURE--
Jason wrote:
> I am new to MS SQL and trying to get the syntax for the following trigger
> correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
> null ProjectSpec.ProjectValueFlag and update this sum to
> ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
> CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
> FOR INSERT, UPDATE, DELETE
> AS
> UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
> (SELECT SUM(ProjectSpec.ProjectSpecValue)
> FROM ProjectSpec
> WHERE ProjectValueFlag IS NOT NULL
> AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
> AND ProjectSpec.ProjectID = :NEW.ProjectID)
> WHERE ProjectInfo.ProjectID = :NEW.ProjectID|||Like the MGFoster said, I would frown upon such things, but you could
probably do something like:
CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
FOR INSERT, UPDATE, DELETE
AS
UPDATE ProjectInfo
SET ProjectInfo.SpecValueTotal =
(SELECT SUM(ProjectSpec.ProjectSpecValue)
FROM ProjectSpec
WHERE ProjectValueFlag IS NOT NULL
AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
--I don't think you need join to the new value here
-- AND ProjectSpec.ProjectID = :NEW.ProjectID
)
WHERE ProjectInfo.ProjectID in (select inserted.projectId
from inserted
union all
select
deleted.projectId
from deleted)
This will recalculate the sum for any rows that have been either deleted,
updated, or inserted, since the key will show up in the inserted table for
insert and update, and in deleted for update or delete. You want to include
all rows, not just new ones, so I commented out the one row.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:534AA117-3A7C-49C9-9F7D-1FAACD4C2A9C@.microsoft.com...
>I am new to MS SQL and trying to get the syntax for the following trigger
> correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
> null ProjectSpec.ProjectValueFlag and update this sum to
> ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
> CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
> FOR INSERT, UPDATE, DELETE
> AS
> UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
> (SELECT SUM(ProjectSpec.ProjectSpecValue)
> FROM ProjectSpec
> WHERE ProjectValueFlag IS NOT NULL
> AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
> AND ProjectSpec.ProjectID = :NEW.ProjectID)
> WHERE ProjectInfo.ProjectID = :NEW.ProjectID

Convert Oracle proc. to SQL Server Procedure

Hi,
Can any one change this oracle proc. to SQL Server procedure.

Any help will be appreciated.

PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);

IF Contr_tot_all = 0 THEN

Select Contr_type into sContractType from ae_contract where db_contract = db_id;

IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;

ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;be patient i'm gonna try to work this tonight

but in the meantime if you could provide some table ddl, that would be good.|||Some information about [http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm] task.

HTH

Convert Oracle 8.1.7 database to SQL 2005 sizing estimates

We are planning on converting a Oracle 8.1.7 database to SQL 2005 Standard Edition. How can we calculate the disk space requirements needed to convert the database. Any help is appreciated.

TIA,

Michelle

If it's built and indexed the same way, it will probably be very close to the size of the original Oracle database.

-Ryan / Kardax

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.
|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

convert or transfer access mdb file to sql server

what's the easiest way to do this. i have 5 tables within the mdb file
i tried the "easiest way" shown in a website but it does not work.
the way i did it was on access(2007) > database tools tab > sql server button
it gave me an error when it ask for an login ID
i do not have any password/id..

please help
thanksIf you do not have a logon to the SQL server then you do not have the permissions to do anything to it - simple eh?|||He could be set up with windows authentication or mixed-mode security, which would not require a password.|||it's microsoft sql server management
when i open it.
it will show a box to connect.

server type is Database Engine
Server name is Mackenzo
auth. is windows auth

yes i have a username without a password which is mackenzo/mackenzie

what can i do??

Convert or cast HexaDecimal to Bigint

Hi ,

I have a hexadecimal string value. I want to convert it to Bigint in Sql Server 2005.

The Hexadecimal value is '0x000000000000000F'.

How is it possible to convert into Bigint.

Please help me

Thanks in advance

Srinivas

SELECT CONVERT(bigint, 0x000000000000000F)|||

There are many ways to convert a string with hexadecimal value to integer value. Below is one technique using a table of numbers:

declare @.t varchar (30)
select @.t = '000000000000000F'
select sum(
case lower( substring( reverse(@.t), number , 1 ) )
when '0' then 0
when '1' then 1
when '2' then 2
when '3' then 3
when '4' then 4
when '5' then 5
when '6' then 6
when '7' then 7
when '8' then 8
when '9' then 9
when 'a' then 10
when 'b' then 11
when 'c' then 12
when 'd' then 13
when 'e' then 14
when 'f' then 15
when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
end * power( cast(16 as bigint), number - 1 )
)
from Numbers n
where number between 1 and len( @.t )
go

Note that it doesn't handle conversions into negative bigint values but that can be done easily.

|||

Hi ,

I think directly using Cast or Convert will not work.

The Bigint value corresponding to Hexadecimal value ('0x000000000000000F') is 1000000002.

I require a function which would take Hexadecimal value as input parameters and return me a big int value.

Thanks for the help.

Srinivas Govada

|||Then create a user-defined function with the code given by Umachandar and pass the hex string to that function and get the calculated bigint value as the return value from the function.|||

Hi

What does number refer to and what is there in table Numbers.

Please provide structure of table numbers and records in table.

Regards,

Srinivas Govada

Convert on Oracle

All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
ThanksAre you trying to get the first 4 characters of a string? If so, try looking
for functions like SUBSTRING or LEFT in PL/SQL
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
Thanks|||Hi,
You can use the below query in Oracle,
select substr('sqlserver',1,4) from dual;
output
--
sqls
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OrumP5E$DHA.2180@.TK2MSFTNGP09.phx.gbl...
> Are you trying to get the first 4 characters of a string? If so, try
looking
> for functions like SUBSTRING or LEFT in PL/SQL
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
> All
> How do I perform select convert(char(4), 'abcdefg') on
> Oracle...?
> Thanks
>

Convert on Oracle

All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
ThanksAre you trying to get the first 4 characters of a string? If so, try looking
for functions like SUBSTRING or LEFT in PL/SQL
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
Thanks|||Hi,
You can use the below query in Oracle,
select substr('sqlserver',1,4) from dual;
output
--
sqls
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OrumP5E$DHA.2180@.TK2MSFTNGP09.phx.gbl...
> Are you trying to get the first 4 characters of a string? If so, try
looking
> for functions like SUBSTRING or LEFT in PL/SQL
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
> All
> How do I perform select convert(char(4), 'abcdefg') on
> Oracle...?
> Thanks
>

convert of code from access to sql server

Hi
give code is in access wann to convert into SQL Server help me plz
SELECT PPCR_tbl_Form.PPCRID, PPCR_tbl_Form.StatusID,
DateValue(IIf(DLookUp("PPCRID","QS_PPCR_Approved_Latest","[PPCRID]= " &
PPCR_tbl_Form.PPCRID) Is
Null,[MinRevDate],QS_PPCR_Approved_Lates
t.LatestApprovalDate)) AS
FromDate, DateValue(IIf([PPCR_tbl_Form.StatusID] In
(14,16,17),[MaxRevDate],Now())) AS ToDate
FROM (PPCR_tbl_Form INNER JOIN QS_PPCR_MaxMin_RevDate ON
PPCR_tbl_Form.PPCRID=QS_PPCR_MaxMin_RevDate.PPCRID) LEFT JOIN
QS_PPCR_Approved_Latest ON
PPCR_tbl_Form.PPCRID=QS_PPCR_Approved_Latest.PPCRID
WHERE (((PPCR_tbl_Form.StatusID)<>1))
ORDER BY PPCR_tbl_Form.PPCRID;Sorry. You are going to have to provide DDL and what you are trying to do.|||"san" <skrupareliya@.gmail.com> wrote in message
news:1141430689.221902.287910@.j33g2000cwa.googlegroups.com...
> Hi
> give code is in access wann to convert into SQL Server help me plz
>
>
> SELECT PPCR_tbl_Form.PPCRID, PPCR_tbl_Form.StatusID,
> DateValue(IIf(DLookUp("PPCRID","QS_PPCR_Approved_Latest","[PPCRID]= " &
> PPCR_tbl_Form.PPCRID) Is
> Null,[MinRevDate],QS_PPCR_Approved_Lates
t.LatestApprovalDate)) AS
> FromDate, DateValue(IIf([PPCR_tbl_Form.StatusID] In
> (14,16,17),[MaxRevDate],Now())) AS ToDate
> FROM (PPCR_tbl_Form INNER JOIN QS_PPCR_MaxMin_RevDate ON
> PPCR_tbl_Form.PPCRID=QS_PPCR_MaxMin_RevDate.PPCRID) LEFT JOIN
> QS_PPCR_Approved_Latest ON
> PPCR_tbl_Form.PPCRID=QS_PPCR_Approved_Latest.PPCRID
> WHERE (((PPCR_tbl_Form.StatusID)<>1))
> ORDER BY PPCR_tbl_Form.PPCRID;
>
Hit the Books Online.
Replace IIF with CASE.
Replace DLookup with a scalar subquery.
Replace Now() with GetDate().
David

Convert nvarchar values to integer

I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.

example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70

as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.

Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.

Thanks

Cast the values as decimal, i.e.: (You don't want integers, that would lose the portion after the decimal.)

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 0001028.99 )
INSERT INTO @.MyTable VALUES ( 222.00 )
INSERT INTO @.MyTable VALUES ( 0000190.89 )
INSERT INTO @.MyTable VALUES ( 2708.99 )
INSERT INTO @.MyTable VALUES ( 00000-50.99 )
INSERT INTO @.MyTable VALUES ( -109.79 )

SELECT MyValues = cast( MyValue AS decimal(10,2))
FROM @.MyTable

MyValues

1028.99
222.00
190.89
2708.99
-50.99
-109.79

convert nvarchar to int

i have got a table

id name pagenumber(nvarchar)

1 gas pg:231-123

2 dff pg:323-123

i need to copy data from this table to another with page number as

id name pagenumber(int)

1 gas 231

2 dff 323

help me

here it is,

Create Table #data (

[id] Varchar(100) ,

[name] Varchar(100) ,

[pagenumber] Varchar(100)

);

Insert Into #data Values( '1','gas','pg:231-123');

Insert Into #data Values( '2','dff','pg:323-123');

select id,name,substring(pagenumber,charindex(':',pagenumber) +1, charindex('-',pagenumber)-charindex(':',pagenumber)-1)

From

#data

|||

Question to 'nasrene',

Are ALL PageNumber values in the form of 'pg:n%-n%'

No space after the colon, alway a dash separating two sets of characters, no other variations?|||

yes all values n form pg.n%-n%

eg:

pg.23-123.

pg.1-23.

pg. 2-34.

for some there are spacesafter pg.

|||

gets error

Invalid length parameter passed to the substring function.
values eg:

pg.23-123.

pg.2-123.

pg. 2-12.

|||

It now appears that the character after 'pg' is a period. Previously it was a colon.

If it is a period, replace the colon in Manivannan's with a period.

select id,name,substring(pagenumber,charindex(':',pagenumber) +1, charindex('-',pagenumber)-charindex(':',pagenumber)-1)

From

#data

[/quote/

|||i replaced but same error|||

Contrary to your earlier assertion, some of your data does NOT follow the pattern [ pg.n%-n% ].

The error is due to one or more rows NOT containing a dash [-].

Try this query to find the rows that do not have a dash.

SELECT *

FROM MyTable

WHERE MyColumn NOT LIKE '%-%'

|||

THERE are three rows that dont contain a '-'

so what to do

|||

This variation 'should' handle your situation.

All that is required is that the Page Number contain the characters 'pg'. It doesn't matter if there is a colon, a period, a dash, or only the first group of numbers.

Code Snippet

DECLARE @.Data table
( [ID] varchar(20) ,
[Name] varchar(20) ,
[PageNumber] varchar(20)
);

INSERT INTO @.Data VALUES ( '1', 'Gas', 'pg.231-123' );
INSERT INTO @.Data VALUES ( '2', 'Oil', 'pg.323-123' );
INSERT INTO @.Data VALUES ( '3', 'Benzene', 'pg.323' );
INSERT INTO @.Data VALUES ( '4', 'Oxygen', 'pg 323-123' );
INSERT INTO @.Data VALUES ( '5', 'Lubricant', 'pg:323-123' );
INSERT INTO @.Data VALUES ( '6', 'Friction', 'pg. 23' );

SELECT
ID,
Name,
Page = ltrim(substring(PageNumber,(charindex('pg',PageNumber)+3), (isnull(len(PageNumber)-(charindex('pg',PageNumber)+2),0))))
FROM @.Data;

ID Name Page
-- -- --
1 Gas 231-123
2 Oil 323-123
3 Benzene 323
4 Oxygen 323-123
5 Lubricant 323-123
6 Friction 23

|||

need the answer as

id name page

1 gas 231

2 oil 323

etc

|||

This should correct that:

DECLARE @.Data table
( [ID] varchar(20) ,
[Name] varchar(20) ,
[PageNumber] varchar(20)
);

INSERT INTO @.Data VALUES ( '1', 'Gas', 'pg.231-123' );
INSERT INTO @.Data VALUES ( '2', 'Oil', 'pg.323-123' );
INSERT INTO @.Data VALUES ( '3', 'Benzene', 'pg.323' );
INSERT INTO @.Data VALUES ( '4', 'Oxygen', 'pg 323-123' );
INSERT INTO @.Data VALUES ( '5', 'Lubricant', 'pg:323-123' );
INSERT INTO @.Data VALUES ( '6', 'Friction', 'pg. 23' );

SELECT
ID,
Name,
Page = ltrim(substring(PageNumber,(charindex('pg',PageNumber)+3), (isnull(len(PageNumber)-(charindex('-',PageNumber)),0))))

FROM @.Data;

/*

ID Name Page
-- -- --
1 Gas 231
2 Oil 323
3 Benzene 323
4 Oxygen 323
5 Lubricant 323
6 Friction 23

*/

|||

I DONT GET THE CORRECT ANSWER

THE ANSWER I GOT WAS 231-,323- ETC

NEED THE ANSWER AS 231,323

|||

The code supplied immediately above provides the data in the form you requested -UNLESS there is something about the data that you have not clearly communicated. (Carefully examine the sample data included to find out if there is something different about how your data is stored.)

I suggest that you copy the code above to a new query window, and execute it. You 'should' then see that the resulset is as you requested. Then you may substitute your table/column names and 'tweak' it until it works for you.

|||

pp.79-103.
pp.63-78.
pp.45-62.
pp.229-254.
pp.147-164.
pp.75-81.
pp.35-56.
pp.23-33.
pp.5-21.
pp.5-13.
pp.268-282.
pp.257-267.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:
> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
> >I have imported a dbf table into mssql in a char format 19720628.
> >
> >
> > How can I convert nvarchar to datetime?
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:
> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
> > DECLARE @.str varchar(10)
> >
> > SET @.str = '19720628'
> >
> > SELECT CONVERT(datetime, @.str, 101)
> >
> >|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
> > Thanks!
> >
> > How can I convert all the rows in a column using the method?
> >
> >
> > "GlennThomas5" wrote:
> >
> > > DECLARE @.str varchar(10)
> > >
> > > SET @.str = '19720628'
> > >
> > > SELECT CONVERT(datetime, @.str, 101)
> > >
> > >|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?
Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?
|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>
|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)
|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>
|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
>|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
>|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.