Order by with Insert into?

I'm selecting records from a table and inserting them into another existing table

Use DSRBQ000
INSERT INTO dbo.db_table_information
Select Table_Name, Column_Name + ' ' + Upper(data_type) +
CASE WHEN data_type IN('binary','char','nchar','nvarchar','varbinary', 'varchar') THEN '('
+ Cast(character_maximum_length AS varchar(10))+')'
WHEN data_type IN('decimal','numeric') THEN '(' + Cast(numeric_precision as varchar(3)) + ','
+ Cast(numeric_scale as varchar(3))+ ')'
Else ''
End +
CASE WHEN columnproperty(object_id(table_name),column_name,' IsIdentity')= 1 THEN ' IDENTITY' +
'(' + Cast(ident_seed(table_name) AS varchar(10)) + ',' + Cast(ident_incr(table_name) AS varchar(10)) + ')'
Else ''
End +
CASE WHEN is_nullable = 'YES' THEN ' NULL'
ELSE ''
END 'Column_Definition', ordinal_position
from information_schema.columns
where table_name IN(select distinct table_name from information_schema.tables where table_type = 'BASE TABLE')
and table_name NOT IN('dtproperties','dbo.db_table_information')
order by table_name, ordinal_position

I'm trying to first order by table_name and then ordinal_position. However, when viewing the table that it data is getting inserted into, I notice that even though it is ordered by table_name, sometimes a row is out of order according to ordinal position.

Is there a valid reason for this? Are you not allow to select the order from which a recordset gets inserted into a table? If thats the case, how can I update the db_table_information and save it so it is in table_name, ordinal_position order?Inserting a sorted record set is usally a wast of time as there is most likely an index in place. When you issue a select your result set will be based on the clustered index or first non-clustered index created for the table.

If you always want db_table_information to be in table_name, ordinal_position order then create an index on those attributes.|||figured out why this occured.. i defined ordinal position as char instead of a number.|||DOH! If only these computers would do as we want rather than do as we ask the world would be a better place!

Order By not working as thought

I have a table that has a two column clustered index. Col 1 is int and
col 2 is char, defined as desc.
When I run a query selecting only those two columns and use an Order By
Col1 ASC, the result set returned has the Col 1 returned in DESC. And
when I use an Order By Col 1 DESC, the result set returned has the Col1
returned in ASC.
The plan shows only a clustered index seek. So even though my
clustered index is defined as desc, why would that affect the way the
Order By clause chooses to return the results? ASC in the Order By
should still return the rows from 1 to 100 not from 100 to 1 even
though the clustered index is desc.
Am I wrong in my thinking...'
Thanks!!Are you saying that SQL Server reverses the meaning of ASN and DESC on your ORDER BY when have an
index defined as DESC instead of ASC. If so, it is a bug and should be reported to MS (and see first
if such a bug has been reported, test recent service pack etc). Post a repro script if you want us
to rest or recent build or 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mghale" <martinghale@.yahoo.com> wrote in message
news:1131465273.531584.225150@.o13g2000cwo.googlegroups.com...
>I have a table that has a two column clustered index. Col 1 is int and
> col 2 is char, defined as desc.
> When I run a query selecting only those two columns and use an Order By
> Col1 ASC, the result set returned has the Col 1 returned in DESC. And
> when I use an Order By Col 1 DESC, the result set returned has the Col1
> returned in ASC.
> The plan shows only a clustered index seek. So even though my
> clustered index is defined as desc, why would that affect the way the
> Order By clause chooses to return the results? ASC in the Order By
> should still return the rows from 1 to 100 not from 100 to 1 even
> though the clustered index is desc.
> Am I wrong in my thinking...'
> Thanks!!
>|||That's exactly what I found. Actually it's a client of mine. They
came to me asking if this is the way it should be returning the
results. The funny thing is that if you add a column to the select
that is not included in the clustered index definition, the execution
plan shows a SORT and the result set is returned in the correct oder as
specified by the Order By clause.
When the select list only contained the two columns that make up the
clustered index, the execuation plan showed NO sort being performed and
returned the results backwards as specified by the Order By clause.
Thanks for the reply. I will advise my client.
Martin|||I you sure this query isn't a view? Views are supposed to be unordered
and an ORDER BY in a view will not exhibit the same (unsupported)
behaviour in 2005 as in 2000.
--
David Portas
SQL Server MVP
--|||Good point, David!
Martin, if you can produce a repro script, we are happy to check it out for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131476712.154075.133400@.g43g2000cwa.googlegroups.com...
>I you sure this query isn't a view? Views are supposed to be unordered
> and an ORDER BY in a view will not exhibit the same (unsupported)
> behaviour in 2005 as in 2000.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
SQL 2000 support ORDER BY in query view using the TOP statement:
SELECT TOP 100 percent FROM table
ORDER BY column ASC | DESC
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"David Portas" wrote:
> I you sure this query isn't a view? Views are supposed to be unordered
> and an ORDER BY in a view will not exhibit the same (unsupported)
> behaviour in 2005 as in 2000.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks guys.
This isn't a view but a base table.
I'll see if I can get the client to allow me to post the DDL for the
table.
Essentially it is a table with x columns. Column one and two are Age
(INT) and Suffix(Char). The rest of the columns vary in type. The
table has a DESC clusterd index on Age, Suffix.
The select statement that produces the 'strange' results selects Age,
Suffice from tablename where Age in value, value, value, and Suffix not
in value, value Order By Age ASC
Then they execute this statement the ordering of Age (expected to be
from lowest to highest) is actually reversed and is listed from highest
to lowest. When you change the ASC to DESC in the Order By clause you
get just the opposite results. The order returned is the opposite to
what you expect to be returned according to the Oder By clause sort
order.
Also when we add a column to the select list that is not part of the
clustered index key and also add the column to the Order By clause
(i.e. Order By Age, ColNotInIndex) the execution plan shows a SORT
phase and the results are returned correctly as specified by the Order
By clause, either DESC or ASC.
Very strange and not what I expected although I have been researching
the forums and it looks like others have run into similar issues when
using DESC Clusterd Indexes...|||Yes, but that doesn't guarantee that you get the data back in that order when you query the view
(unless that query has a similar ORDER BY, of course). Using TOP 100 PERCENT in a view definition
has always been considered a hack, and in 2005 we will typically not see this have any impact of the
ordering of the rows returned from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in message
news:B8B5EFE3-848F-4CE2-BA9E-5C89F2868AFB@.microsoft.com...
> David,
> SQL 2000 support ORDER BY in query view using the TOP statement:
> SELECT TOP 100 percent FROM table
> ORDER BY column ASC | DESC
> --
> ** * Esta msg foi útil pra você ? Então marque-a como tal. ***
> Regards,
> Rodrigo Fernandes
>
> "David Portas" wrote:
>> I you sure this query isn't a view? Views are supposed to be unordered
>> and an ORDER BY in a view will not exhibit the same (unsupported)
>> behaviour in 2005 as in 2000.
>> --
>> David Portas
>> SQL Server MVP
>> --
>>|||I agree that using Top 100 percent is a hack but it also commonly used
so far as I have seen at my clients.
But still in my case were are hitting a base table and not a view.
Just a single base table. The fact is that using the Order By clause
should cause a SORT to be performed prior to the result set being
returned to ensure the data is in fact in the order specified by the
Order By clause. For whatever reason, on this table with a DESC
Clustered Index, the SORT is not being performed if the only columns in
the Order By Clause are key columns in the DESC Clustered Index. I
know SQL Server is not just ignoring the Order By clause because
depending on the sort order in the clause, either ASC or DESC it is
returning the rows in the exact opposite order. It's like it is using
the sorted (clustered) data and thinking that it is already ordered
correctly then applying the ASC or DESC directly to the order of the
clustered index which in this case in DESC. Perhaps that is why it
produces backwards results when using the Order By clause with ASC. It
doesn't sort but simply leaves the oder as it is in the ordered
(clustered) index which returns the results in a DESC order. Then when
you specify DESC in the order by clause SQL Server just reverses the
order of teh clusterd index which returns the rows in an ASC order.
Definitely not the right behavior for the Order By clause but it is my
best guess as to why this is happening. This is not a complicated
table structure and a very simple SQL Statement. Not alot of room for
human error on this one...|||SQL Server doesn't need a SORT as it can use the index to traverse the rows in the correct (!)
order. Look at the execution plan for the index usage and you will see ORDERED FORWARD or BACKWARD.
This is letting the execution engine that it must follow the index linked list to retrieve the rows,
not tie IAM page. But in this case, SQL Server obviously does it wrong. Again, with a repro we can
try it and verify etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mghale" <martinghale@.yahoo.com> wrote in message
news:1131481374.194917.233520@.z14g2000cwz.googlegroups.com...
>I agree that using Top 100 percent is a hack but it also commonly used
> so far as I have seen at my clients.
> But still in my case were are hitting a base table and not a view.
> Just a single base table. The fact is that using the Order By clause
> should cause a SORT to be performed prior to the result set being
> returned to ensure the data is in fact in the order specified by the
> Order By clause. For whatever reason, on this table with a DESC
> Clustered Index, the SORT is not being performed if the only columns in
> the Order By Clause are key columns in the DESC Clustered Index. I
> know SQL Server is not just ignoring the Order By clause because
> depending on the sort order in the clause, either ASC or DESC it is
> returning the rows in the exact opposite order. It's like it is using
> the sorted (clustered) data and thinking that it is already ordered
> correctly then applying the ASC or DESC directly to the order of the
> clustered index which in this case in DESC. Perhaps that is why it
> produces backwards results when using the Order By clause with ASC. It
> doesn't sort but simply leaves the oder as it is in the ordered
> (clustered) index which returns the results in a DESC order. Then when
> you specify DESC in the order by clause SQL Server just reverses the
> order of teh clusterd index which returns the rows in an ASC order.
> Definitely not the right behavior for the Order By clause but it is my
> best guess as to why this is happening. This is not a complicated
> table structure and a very simple SQL Statement. Not alot of room for
> human error on this one...
>|||Thanks for the clarification on the SORT. I will check the execution
plan. I guess I assumed that any time you include an Order By clause
in your query, the DBMS would perform a SORT as the final step to order
the rows according to the Order By clause.
I'll work with the client today to get the DDL for the table and
indexes, the exact SQL statement, the the levels of the OS and SQL
Server.
I won't be able to provide a sampling of data as my client is an
insurance company and restricted by HIPPA.
Thanks for all the feedback.
Martin|||OK,
I didn't get the client to provide their actual table DDL but we have
created a test script that reproduces the same outcome on three
different servers and found something very interesting. When using
just the first column of the clustered index which is an INT in the
WHERE clause, the Oder By clause is applied correctly. When we also
include the second column of the clustered index which is a char in the
WHERE clause, the Order By clause is applied incorrectly, the reverse
of what it should ordered according to the Order By clause. Here's the
info to reproduce the issue...
1. First create table and populate with data and do not add the
clusterd index yet...
---
-- Create Table
---
create table table_one
(
column_one int not null ,
column_two char(1) collate sql_latin1_general_cp1_ci_as not null
)
go
---
-- Insert Test Data
---
insert table_one values (1,'a')
insert table_one values (2,'b')
insert table_one values (3,'c')
insert table_one values (3,'k')
insert table_one values (3,'l')
insert table_one values (3,'x')
insert table_one values (4,'d')
insert table_one values (5,'e')
insert table_one values (6,'f')
insert table_one values (6,'m')
insert table_one values (6,'n')
insert table_one values (6,'x')
insert table_one values (7,'g')
insert table_one values (7,'s')
insert table_one values (8,'h')
insert table_one values (8,'q')
insert table_one values (9,'i')
insert table_one values (9,'o')
insert table_one values (9,'p')
insert table_one values (9,'x')
insert table_one values (10,'j')
insert table_one values (10,'p')
go
2. Run the following SELECT statements and make not of the correct
oder returned by each query. The comments to the side specify what
order the result set is returned and whether a SORT operation was part
of the execution plan. All these statements return the data and
perform a SORT as expected as no clustered index exists yet.
---
-- No ORDER BY Clause
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- No SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
-- order by column_one
go
---
-- ORDER BY Clause on Column_One
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one
go
---
-- ORDER BY Clause on Column_One DESC
---
SELECT column_one, column_two -- Descending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one desc
go
---
-- ORDER BY Clause on Column_One ASC
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one asc
go
---
-- No AND Clause
-- No ORDER BY Clause
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- No SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
-- order by column_one
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One DESC
---
SELECT column_one, column_two -- Descending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one desc
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One ASC
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one asc
go
3. Create the Clustered Index with the following...
---
-- Create Clustered Index
---
create clustered index cluster_001
on table_one
(column_one desc, column_two)
go
4. Re-execute the above SELECT statements. Before sure to compare the
order of the result set returned with the order specified in the
comment. You will see on a few of the statements,l namely the SELECTS
that include both columns in the WHERE clause, the oder is returned in
the reverse as what it should be returned...
We have verified this behavior on three different servers and a
workstation. We are on Windows 2003 Server and SQL Server 2000 SP3.
We have not seen any mention of a fix for this in SP4 of SQL 2005
although we are going to execute this test on both of those
environments today as well as open an issue with Microsoft Support.
Have a look, see for your self. Very interesting results...
Thanks for all the replies!
Martin|||Definitely looks like a bug to me.
After creating the clustered index many of the resultsets are in an
incorrect order. I have run the script on SQL2K SP4 (8.00.2039).
The weird part is, that it even goes wrong if the first column is not
indexed as descending, as long as the second column is indexed as
descending. IOW, it also goes wrong with the index definition:
create clustered index cluster_001
on table_one
(column_one, column_two desc)
BTW: a work around is to change the column name in the ORDER BY clause
to a non trivial expression, for example ORDER BY RTRIM(column_one)
Gert-Jan
mghale wrote:
> OK,
> I didn't get the client to provide their actual table DDL but we have
> created a test script that reproduces the same outcome on three
> different servers and found something very interesting. When using
> just the first column of the clustered index which is an INT in the
> WHERE clause, the Oder By clause is applied correctly. When we also
> include the second column of the clustered index which is a char in the
> WHERE clause, the Order By clause is applied incorrectly, the reverse
> of what it should ordered according to the Order By clause. Here's the
> info to reproduce the issue...
> 1. First create table and populate with data and do not add the
> clusterd index yet...
> ---
> -- Create Table
> ---
> create table table_one
> (
> column_one int not null ,
> column_two char(1) collate sql_latin1_general_cp1_ci_as not null
> )
> go
> ---
> -- Insert Test Data
> ---
> insert table_one values (1,'a')
> insert table_one values (2,'b')
> insert table_one values (3,'c')
> insert table_one values (3,'k')
> insert table_one values (3,'l')
> insert table_one values (3,'x')
> insert table_one values (4,'d')
> insert table_one values (5,'e')
> insert table_one values (6,'f')
> insert table_one values (6,'m')
> insert table_one values (6,'n')
> insert table_one values (6,'x')
> insert table_one values (7,'g')
> insert table_one values (7,'s')
> insert table_one values (8,'h')
> insert table_one values (8,'q')
> insert table_one values (9,'i')
> insert table_one values (9,'o')
> insert table_one values (9,'p')
> insert table_one values (9,'x')
> insert table_one values (10,'j')
> insert table_one values (10,'p')
> go
> 2. Run the following SELECT statements and make not of the correct
> oder returned by each query. The comments to the side specify what
> order the result set is returned and whether a SORT operation was part
> of the execution plan. All these statements return the data and
> perform a SORT as expected as no clustered index exists yet.
> ---
> -- No ORDER BY Clause
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- No SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> -- order by column_one
> go
> ---
> -- ORDER BY Clause on Column_One
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one
> go
> ---
> -- ORDER BY Clause on Column_One DESC
> ---
> SELECT column_one, column_two -- Descending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one desc
> go
> ---
> -- ORDER BY Clause on Column_One ASC
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one asc
> go
> ---
> -- No AND Clause
> -- No ORDER BY Clause
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- No SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> -- order by column_one
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One DESC
> ---
> SELECT column_one, column_two -- Descending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one desc
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One ASC
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one asc
> go
> 3. Create the Clustered Index with the following...
> ---
> -- Create Clustered Index
> ---
> create clustered index cluster_001
> on table_one
> (column_one desc, column_two)
> go
> 4. Re-execute the above SELECT statements. Before sure to compare the
> order of the result set returned with the order specified in the
> comment. You will see on a few of the statements,l namely the SELECTS
> that include both columns in the WHERE clause, the oder is returned in
> the reverse as what it should be returned...
> We have verified this behavior on three different servers and a
> workstation. We are on Windows 2003 Server and SQL Server 2000 SP3.
> We have not seen any mention of a fix for this in SP4 of SQL 2005
> although we are going to execute this test on both of those
> environments today as well as open an issue with Microsoft Support.
> Have a look, see for your self. Very interesting results...
> Thanks for all the replies!
> Martin|||Thanks for the replies everyone. Gert-Jan - thanks for the work-around
suggestion.
My client is opening an issue with MS and we are also going to test for
the same behavior on SQL 2005...
Martinsql

ORDER BY Clause with multiple tables

Hi All
I am having a problem with an ORDER BY clause when selecting information from multiple tables. Eg
SELECT i.InvoiceId, pd.PayDescription, u.UserName
FROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId
LEFT OUTER JOIN tblUsers ON i.UserId = u.UserId
ORDER BY pd.PayDescription
this is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?
I am writing this for MSSQL Server 2000
Thanks
BraidenYou can definitely do an ORDER BY like that. What's the problem? Are you receiving an error?
(The abbreviated example you've supplied would of course return anerror because there is no definitition for the pd alias in the query.)
|||

Hi
This is my current stored procedure, it is implementing a server side paging algorithm.
--CODE BEGINS
CREATE PROCEDURE [dbo].[ap_APPInvoiceSearchSEL]

--Declare input parameters
@.ActivityInvoiceTypeID as int,
@.CommunityVisitorID as int,
@.PayRunID as bigint,
@.InvoiceDateFrom as datetime,
@.InvoiceDateTo as datetime,
@.PayStatusID as int,
@.PageNum as bigint,
@.PageSize as bigint,
@.SortExpression as varchar(50)

AS
BEGIN

-- Create a Variable Table to hold search results in
DECLARE @.SearchResults TABLE
(
SearchResultID bigint IDENTITY,
InvoiceID bigint,
SiteName varchar(50),
ActivityInvoiceTypeDescription varchar(50),
CommunityVisitorName varchar(100),
InvoiceDate datetime,
ActivityDate datetime,
PayStatusDescription varchar(50),
PayRunID bigint,
PayRunDate datetime,
ActivityAmountClaimed decimal(9),
TravelAmountClaimed decimal(9),
VehicleAllowanceClaimed decimal(9),
TotalAmountClaimed decimal(9),
RecordCount bigint

)

--Declare variables
DECLARE @.RecordCount as bigint,
@.StartRecord as bigint,
@.EndRecord as bigint

--Find the number of results
SELECT @.RecordCount = Count(i.InvoiceID)
FROM vwInvoice i LEFT OUTER JOIN tblCommunityVisitor c ON i.CommunityVisitorID = c.CommunityVisitorID
LEFT OUTER JOIN tblJAGUser u ON u.UserID = c.UserID
LEFT OUTER JOIN tluActivityInvoiceType it ON i.ActivityInvoiceTypeID = it.ActivityInvoiceTypeID
LEFT OUTER JOIN tluPayStatus ps ON i.PayStatusID = ps.PayStatusID
LEFT OUTER JOIN tblPayRun pr ON i.PayRunID = pr.PayRunID
LEFT OUTER JOIN tblSite s ON i.SiteID = s.SiteID

WHERE (i.ActivityInvoiceTypeID = @.ActivityInvoiceTypeID OR @.ActivityInvoiceTypeID is null)
AND (i.CommunityVisitorID = @.CommunityVisitorID OR @.CommunityVisitorID is null)
AND (i.PayRunID = @.PayRunID OR @.PayRunID is null)
AND (i.InvoiceDate >= @.InvoiceDateFrom OR @.InvoiceDateFrom is null)
AND (i.InvoiceDate <= @.InvoiceDateTo OR @.InvoiceDateTo is null)
AND (i.PayStatusID = @.PayStatusID OR @.PayStatusID is null)

INSERT INTO
@.SearchResults (InvoiceID,
SiteName,
ActivityInvoiceTypeDescription,
CommunityVisitorName,
InvoiceDate,
ActivityDate,
PayStatusDescription,
PayRunID,
PayRunDate,
ActivityAmountClaimed ,
TravelAmountClaimed,
VehicleAllowanceClaimed,
TotalAmountClaimed,
RecordCount)

SELECT i.InvoiceID,
s.SiteName,
it.ActivityInvoiceTypeDescription,
u.FirstName + ' ' + u.LastName as CommunityVisitorName,
i.InvoiceDate as InvoiceDate,
i.ActivityDate,
ps.PayStatusDescription,
i.PayRunID,
pr.PayRunDate,
i.ActivityAmountClaimed,
i.TravelAmountClaimed,
i.VehicleAllowanceClaimed,
i.TotalAmountClaimed,
@.RecordCount As RecordCount

FROM vwInvoice i LEFT OUTER JOIN tblCommunityVisitor c ON i.CommunityVisitorID = c.CommunityVisitorID
LEFT OUTER JOIN tblJAGUser u ON u.UserID = c.UserID
LEFT OUTER JOIN tluActivityInvoiceType it ON i.ActivityInvoiceTypeID = it.ActivityInvoiceTypeID
LEFT OUTER JOIN tluPayStatus ps ON i.PayStatusID = ps.PayStatusID
LEFT OUTER JOIN tblPayRun pr ON i.PayRunID = pr.PayRunID
LEFT OUTER JOIN tblSite s ON i.SiteID = s.SiteID

WHERE (i.ActivityInvoiceTypeID = @.ActivityInvoiceTypeID OR @.ActivityInvoiceTypeID is null)
AND (i.CommunityVisitorID = @.CommunityVisitorID OR @.CommunityVisitorID is null)

AND (i.PayRunID = @.PayRunID OR @.PayRunID is null)
AND (i.InvoiceDate >= @.InvoiceDateFrom OR @.InvoiceDateFrom is null)
AND (i.InvoiceDate <= @.InvoiceDateTo OR @.InvoiceDateTo is null)
AND (i.PayStatusID = @.PayStatusID OR @.PayStatusID is null)

ORDER BY

CASE @.SortExpression
WHEN 'InvoiceID' THEN i.InvoiceID
WHEN 'SiteName' THEN s.SiteName
WHEN 'ActivityInvoiceTypeDescription' THEN it.ActivityInvoiceTypeDescription
WHEN 'CommunityVisitorName' THEN u.FirstName
WHEN 'ActivityDate' THEN i.ActivityDate
WHEN 'PayStatusDescription' THEN ps.PayStatusDescription
WHEN 'ActivityAmountClaimed' THEN i.ActivityAmountClaimed
WHEN 'TravelAmountClaimed' THEN i.TravelAmountClaimed
WHEN 'VehicleAllowanceClaimed' THEN i.VehicleAllowanceClaimed
WHEN 'TotalAmountClaimed' THEN i.totalAmountClaimed
ELSE i.InvoiceID
END

--Determine page positions
SET @.StartRecord = ((@.PageNum-1) * @.PageSize) + 1
SET @.EndRecord = @.PageNum * @.PageSize

--Now get the page of search results from the temp table
SELECT *
FROM @.SearchResults
WHERE SearchResultID BETWEEN @.StartRecord AND @.EndRecord

END
GO
--CODE ENDS
After further investigation i have narrowed it down to the final select case statement, which works find when the @.SortExpression variables resolves to a field that is in the invoice table. However when it does not i get a rather perculiar error saying"Syntax error converting datetime from character string."
Thanks
Braiden

|||Hi,
I observed that the ORDER BY works successfully when the order by column is one of the following types: int, boolean, datetime
But when the order by column is varchar or nvarchar, the sql statement fails with the following error message
Syntax error converting datetime from characterstring.

declare @.column nvarchar(100)
set @.column = 'firstname'
select *
from Customers
order by
case @.column
when 'cityid' then cityid
when 'firstname' then firstname
when 'birthdate' then birthdate
when 'active' then active
end
Eralper
http://www.kodyaz.com

|||Hi,
I realised that the problem occurs if your case statement has at leaston different column type side by side with varchar or nvarchar datatype.
If you do not have a column with data type string, the order by with case runs successfully.
Or in the case statement if you have only varchar data types (no othertype like datetime, boolean, int, etc) then it runs again successfully.

CASE @.SortExpression
WHEN 'InvoiceID' THEN i.InvoiceID
WHEN 'SiteName' THEN s.SiteName
WHEN 'ActivityInvoiceTypeDescription' THEN it.ActivityInvoiceTypeDescription
WHEN 'CommunityVisitorName' THEN u.FirstName
WHEN 'ActivityDate' THEN i.ActivityDate
WHEN 'PayStatusDescription' THEN ps.PayStatusDescription
WHEN 'ActivityAmountClaimed' THEN i.ActivityAmountClaimed
WHEN 'TravelAmountClaimed' THEN i.TravelAmountClaimed
WHEN 'VehicleAllowanceClaimed' THEN i.VehicleAllowanceClaimed
WHEN 'TotalAmountClaimed' THEN i.totalAmountClaimed
ELSE i.InvoiceID
END
If you really need a functionality like this, you should use run a copy of the script for string data types

Or you can use dynamic sql statements
Eralper
|||Hi again,
You can use multiple CASE statement in ORDER BY instead of one
declare @.column nvarchar(100)
set @.column = 'firstname'
select *
from Customers
order by
case when @.column = 'cityid' then cityid end,
case when @.column = 'firstname' then firstname end

You can check the article at http://www.extremeexperts.com/SQL/Articles/CASEinORDER.aspx

|||Thanks aloteralper
That was a big help, i have got it working now
Braiden

Oracle parameters

I am creating a report using a query where the data source is an Oracle 8i database.

I'm selecting data that is between 2 dates:

"Select * from table where mydate is between :startdate and :stopdate".

To get all of the data I need to use :stopdate + 1 to add a day for oracle SSRS uses dateadd to get the date that oracle requires. SSRS does not like the (+1) and Oracle does not recognize the dateadd function.

How do I solve this problem?

P.S. This is my first SSRS report.

The bottom of this posting contains a simple report that shows how to do this based on a SQL Server data source.

Overall there are several options:

* you can keep your current query commandtext and perform the calculation in the report parameter as shown in the RS 2005 sample below, where the second report parameter is marked as "hidden" and its value is determined based on the first report parameter using an expression: =CDate(Parameters!StartDate.Value).AddDays(1)

* perform the datetime calculation in the commandtext. However in that case you *must* use the Oracle date functions to perform the calculation (lookup your Oracle SQL documentation)

* write a Oracle stored procedure (e.g. in PL/SQL) and call the stored procedure from Reporting Services. The stored procedure has to return the data through one OUT REF cursor (see also this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1)

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>95e33366-62c0-4eea-87f0-94d2534377bd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
<Prompt>StartDate</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(Parameters!StartDate.Value).AddDays(1)</Value>
</Values>
</DefaultValue>
<Prompt>EndDate</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox2">
<Left>0.25in</Left>
<Top>0.5in</Top>
<ZIndex>1</ZIndex>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!EndDate.Value</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.25in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!StartDate.Value</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>e15b81e3-ab03-4e68-a6b8-0de6d6880ab5</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from orders where OrderDate between @.StartDate and @.EndDate</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
<Field Name="CustomerID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CustomerID</DataField>
</Field>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="RequiredDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>RequiredDate</DataField>
</Field>
<Field Name="ShippedDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>ShippedDate</DataField>
</Field>
<Field Name="ShipVia">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ShipVia</DataField>
</Field>
<Field Name="Freight">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Freight</DataField>
</Field>
<Field Name="ShipName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipName</DataField>
</Field>
<Field Name="ShipAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipAddress</DataField>
</Field>
<Field Name="ShipCity">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCity</DataField>
</Field>
<Field Name="ShipRegion">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipRegion</DataField>
</Field>
<Field Name="ShipPostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipPostalCode</DataField>
</Field>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Robert,

Thanks for your response. Options 1 & 2 are feasible since we can only 'read' from the Oracle database.

Oracle linked Server Performance

Hello,
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.
Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>

Oracle linked Server Performance

Hello,
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>