April 11, 2014 at 2:00 pm
DID is a varchar(20) and i cant seem to get past the above error msg. Thx.
if (@OrderBy = 'Posteddate')
Begin
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + did ' + ' ' + @OrderType + ') '
End
else
Begin
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + did ' + ' ' + @OrderType + ') '
END
The one below works fine if ID is an int.
if (@OrderBy = 'LastModified')
Begin
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + id ' + ' ' + @OrderType + ') '
End
else
Begin
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + cast(id as varchar(100)) ' + ' ' + @OrderType + ') '
END
April 11, 2014 at 3:24 pm
The error occures when you build your dynamic query or when you execute it? What datatype is @OrderType? Assuming it's a VARCHAR, this
DECLARE @OrderBy VARCHAR(20) = 'Posteddate'
DECLARE @OrderType VARCHAR(20) = 'OrderType'
PRINT 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + did ' + ' ' + @OrderType + ') '
will result in this:
RowNum = ROW_NUMBER() OVER (ORDER BY Posteddate + did OrderType)
If Posteddate a datetime2 then you probably will see that error upon execution of @sql.
--Vadim R.
April 11, 2014 at 3:55 pm
What datatype is @OrderType? Assuming it's a VARCHAR - correct.
I changed the PostedDate datatype back to DateTime, and decided to try to convert the DID string to an int.
Companydid is a varchar(20).
When i use:
SELECT CAST(companydid AS INT) FROM cb
or
SELECT CONVERT(INT, companydid) FROM cb
i get the following error msg.
"Conversion failed when converting the varchar value 'CHN4297773M82WK6J14' to data type int."
Any suggestions on how to convert a string to an int ?
Thx
April 11, 2014 at 4:07 pm
You can't convert string that contains non-numeric characters to INT. If you need to concatenate then you have to convert other datatypes to varchar.
What are you trying to achive? Are you going to sort on both columns first by PostedDate and then by CompanyDID? If so, simply separate by comma:
ORDER BY PostedDate, CompanyDID
--Vadim R.
April 11, 2014 at 4:32 pm
I even tried this
"Syntax: PARSE ( string_value AS data_type [ USING culture ] )"
SELECT top 10 PARSE(companydid as int) from cb
without success.
What i want to do is change from a 'STRING ID' to an 'INT ID', but so far i cannot find a way to do so.
Thx
April 11, 2014 at 7:33 pm
It would help if you would post the DDL for the table, some sample data for the table, and the expected results based on the sample data.
Please remember that we can't see what you see. You need help us help you.
April 12, 2014 at 7:26 am
PostedDate Field – datatype - Datetime
2014-04-10 00:00:00.000
2014-03-10 00:00:00.000
2014-02-10 00:00:00.000
2014-01-10 00:00:00.000
2013-12-10 00:00:00.000
2013-11-10 00:00:00.000
2013-10-10 00:00:00.000
2013-09-10 00:00:00.000
2013-08-10 00:00:00.000
2013-07-10 00:00:00.000
CompanyDID Field – varchar(20) – Datatype
FHN4297773M82WK6J14
FHN4297773M82WK6J14
F8D33G78FQVXYHWV67X
F8A0FY6ZSBV7RPS9SVJ
FHT2JB6FTZFW61ZCDNW
F8C1MZ6RS45GV1YYNBB
FHR7NT6GGKHCVBT63SR
F8H77J70FFB41YYGRSS
F7G7JJ77NVMSHQ4FCTZ
FHN2T072MQFGWW5ZBR6
i want to order the data based on PostedDate + CompanyDID so i can sort asc or desc.
The issue is that i cant convert CompanyDID to an int, so adding to Posteddate gives an error.
Using Order by Posteddate, CompanyDID does not work.
--if (@OrderBy = 'posteddate')
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + did ' + ' ' + @OrderType + ') '
--End
--else
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + did ' + ' ' + @OrderType + ') '
--END
--if (@OrderBy = 'Posteddate')
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + did ' + ' ' + @OrderType + ') '
--End
--else
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + did ' + ' ' + @OrderType + ') '
--END
The two above scenarios fail.
This is the code, but to simplify testing i only provided posteddata and companydid.
Begin
WITH results AS
(
select
id,
jobtitle as title,
company as companyname,
jobdetailsurl as url,
DescriptionTeaser as description,
posteddate,
locationcity as city,
locationstate as state,
RowNum = ROW_NUMBER() OVER (ORDER BY posteddate + did desc) FROM dbo.cb
WHERE 1=1 )
SELECT
id,
title,
companyname,
url,
posteddate,
description,
city,
state,
city + ', ' + state as Location,
(select count(*) from results) totalcount
FROM results
WHERE RowNum BETWEEN 0 AND 9 ORDER BY RowNum
OPTION(Maxdop 8)
End
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
Thanks
April 12, 2014 at 7:34 am
Actually, you still haven't provided anything we can work with here. Please post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (in the form of INSERT INTO statements) for the table(s) involved, and the expected results based on the sample data you provide (even if you have to create it by hand).
We are volunteers here, and the more you do to help us to help you the better answers (and tested code) you will get.
April 12, 2014 at 8:32 am
USE [NewTable]
GO
/****** Object: Table [dbo].[NewTable2] Script Date: 4/12/2014 10:13:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NewTable2](
[ID] [int] NOT NULL,
[CompanyDID] [varchar](20) NULL,
[PostedDate] [datetime] NULL,
CONSTRAINT [PK_NewTable2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO NewTable2 (id,companydid,posteddate)
VALUES
(1,'FHN4297773M82WK6J14', '2014-04-10 00:00:00.000'),
(2,'FHN4297773M82WK6J14', '2014-03-10 00:00:00.000'),
(3,'F8D33G78FQVXYHWV67X', '2014-02-10 00:00:00.000'),
(4,'F8A0FY6ZSBV7RPS9SVJ', '2014-01-10 00:00:00.000'),
(5,'FHT2JB6FTZFW61ZCDNW', '2013-12-10 00:00:00.000'),
(6,'F8C1MZ6RS45GV1YYNBB', '2013-11-10 00:00:00.000'),
(7,'FHR7NT6GGKHCVBT63SR', '2013-10-10 00:00:00.000'),
(8,'F8H77J70FFB41YYGRSS', '2013-09-10 00:00:00.000'),
(9,'F7G7JJ77NVMSHQ4FCTZ', '2013-08-10 00:00:00.000'),
(10,'FHN2T072MQFGWW5ZBR6', '2013-07-10 00:00:00.000')
USE [NewTable]
GO
/****** Object: StoredProcedure [dbo].[zzNewTable2] Script Date: 4/12/2014 10:12:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[zzNewTable2]
@fromRec int,
@toRec int,
@OrderType VARCHAR(10),
@OrderBy VARCHAR(100)
AS
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'Begin
WITH results AS
(
select
id,
companydid,
posteddate,
'
--if (@OrderBy = 'PostedDate')
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + id ' + ' ' + @OrderType + ') '
--End
--else
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + cast(id as varchar(100)) ' + ' ' + @OrderType + ') '
--END
--if (@OrderBy = 'Posteddate')
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY Posteddate, companydiddid ' + ' ' + @OrderType + ') '
--End
--city + '', '' + state as Location,
--if (@OrderBy = 'Posteddate')
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + companydid ' + ' ' + @OrderType + ') '
--End
--else
--Begin
--SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + CAST(@OrderBy AS varchar(20)) + ' + companydid ' + ' ' + @OrderType + ') '
--END
if (@OrderBy = 'posteddate')
Begin
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + companydid ' + ' ' + @OrderType + ') '
End
else
Begin
SELECT @sql = @sql + 'RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' + companydid ' + ' ' + @OrderType + ') '
END
SELECT @sql = @sql + ' FROM dbo.newtable2
WHERE 1=1 '
SELECT
id,
posteddate,
companydid,
(select count(*) from results) totalcount
FROM results
WHERE RowNum BETWEEN ' + cast(@fromRec as varchar(10)) + ' AND ' + cast(@toRec as varchar(10)) +
' ORDER BY RowNum
OPTION(Maxdop 8)
End
'
print @sql
exec (@sql)
Begin
WITH results AS
(
select
id,
companydid,
posteddate,
RowNum = ROW_NUMBER() OVER (ORDER BY posteddate + companydid asc) FROM dbo.newtable2
WHERE 1=1 )
SELECT
id,
posteddate,
companydid,
(select count(*) from results) totalcount
FROM results
WHERE RowNum BETWEEN 0 AND 9 ORDER BY RowNum
OPTION(Maxdop 8)
End
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I am trying to sort on posteddate+companydid.
Getting past the error would be great at this point.
Thx
April 12, 2014 at 8:34 am
USE [NewTable]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[zzNewTable2]
@fromRec = 0,
@toRec = 9,
@OrderType = N'asc',
@OrderBy = N'posteddate'
SELECT'Return Value' = @return_value
GO
To run the SP.
April 12, 2014 at 8:37 am
Based on the sample data, what is the output supposed to look like?
April 12, 2014 at 8:46 am
You can't sort on posteddate+companydid. You get the error, right?
So, you need to either convert posteddateto varchar and then concatenate:
CONVERT(char(8), posteddate, 112) + companydid
Or change your dynamic code to produce this SQL:
ORDER BY posteddate ASC, companydid ASC
--Vadim R.
April 12, 2014 at 8:57 am
Okay, based on the following call to the procedure:
EXEC @return_value = [dbo].[zzNewTable2]
@fromRec = 0,
@toRec = 9,
@OrderType = N'asc',
@OrderBy = N'posteddate'
It appears that you want to sort the data being returned based on the column passed in @OrderBy in either ascending or descending order based on the value passed in @OrderType, returning the rows based on the values passed in @fromRec and @toRec.
Have I summarized this correctly?
April 12, 2014 at 10:31 am
Guys,
I would like the data sorted based on a derived field which is created by 'posteddate + companydid' in either asc or desc order.
Doing so with posteddate , companydid does NOT work!
Thanks
April 12, 2014 at 10:43 am
Rvadim,
CONVERT(char(8), posteddate, 112) + companydid
That seemed to work 🙂
Many Thanks !
I will keep doing some testing.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply