June 11, 2010 at 7:15 am
There are three sets of dropdowns in my front end
1) SortBy1, SortOrder1
2) SortBy2, SortOrder2
3) SortBy3, SortOrder3
Which has the follwing options
1) ID
2) IssuedDate
3) Size
4) Make
User can select either one or more than a set of drodowns say
1) ID ASC
2) ID ASC, IssuedDate DESC
3) ID ASC, IssuedDate DESC, Size DESC
etc.. like above, there are many combinations
Limitations:
- the query should not use 'EXEC' statement. I am using this query for reporting purpose and if i use EXEC then the columns were not showing up for drag-drop into my report.
Also i have tried using CASE statement:
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC
I got an error 'A column has been specified more than once in the order by list. Columns in the order by list must be unique'
Could some one help me out. Thanks in advance.
June 11, 2010 at 8:05 am
You are only showing us part of the problem. Please provide the entire query, and if it is a stored procedure, the actual procedure.
June 11, 2010 at 8:11 am
Hi Lynn,
Here is the query in the stored procedure where @sortorder1,@sortby1, @sortorder2 and @sortby2 are the parameters.
SELECT
FD.Id,
FDS.Size,
FDM.Make,
CONVERT(NVARCHAR(10),FD.IssuedDate,101) as IssuedDate,
FROM
[dbo].[tblFlashDrive] FD
LEFT OUTER JOIN dbo.tblFlashDriveSize FDS ON FDS.SizeId = FD.SizeId
LEFT OUTER JOIN dbo.tblFlashDriveMake FDM ON FDM.MakeId = FD.MakeId
WHERE
IssuedReturnedLost = 'I'
ORDER BY
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC
June 11, 2010 at 9:10 am
I'm confused. Please provide the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (as a series of INSERT INTO statements) for each table (data should not be real, but representative of the data involved), and expected results based on each possible input to the stored proc.
June 11, 2010 at 9:34 am
Hi Lynn,
I have made the query simple.
I have given the user an option to select 2 sort order and 2 sortby dropdowns. example: User may select as following sortby/sort order
1) Id DESC and Size ASC
2) Id ASC and Size ASC
3) Id DESC and Size DESC
4) Id ASC and Size DESC
5) Id ASC
6) Id DESC
7) Size ASC
8) Size DESC
SELECT
Id,
Size
FROM
[dbo].[tblFlashDrive]
WHERE
Size = '2GB'
ORDER BY
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC
Did my explanation suffice?
June 16, 2010 at 12:19 pm
Any Clues?
June 16, 2010 at 12:58 pm
Any clues? No. I am still waiting for the information I requested in my last post.
June 16, 2010 at 1:11 pm
Lynn, I have replied to your post. I gave you the modified query with a table and two columns. Pls let me know if you need more details.
Thanks.
June 16, 2010 at 1:37 pm
rameshk75 (6/16/2010)
Lynn, I have replied to your post. I gave you the modified query with a table and two columns. Pls let me know if you need more details.Thanks.
Actually, no. I have scrolled through this entire thread and do not find the DDL (CREATE TABLE) statements for the tables involved, or sample data for the tables involved, or any expected results based on the sample data and possible user inputs.
Not having access to the information you have at hand makes helping you difficult.
June 17, 2010 at 6:49 am
Ok Lynn, Here is the complete picture.
***********************************************
CREATE TABLE [dbo].[tblFlashDrive](
[Id] [int] NULL,
[SizeId] [int] NULL,
[MakeId] [int] NULL,
[IssuedTo] [int] NULL,
[IssuedDate] [datetime] NULL,
[Issued] [bit] NULL
)
CREATE TABLE [dbo].[tblFlashDriveMake](
[MakeId] [int] NULL,
[Make] [nvarchar](50) NULL
)
CREATE TABLE [dbo].[tblFlashDriveSize](
[SizeId] [int] NULL,
[Size] [nchar](10) NULL
)
******************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Report_FDIssued]
@nvSortOrder1 NVARCHAR (20)
@nvSortOrderBy1 NCHAR (4)
@nvSortOrder2 NVARCHAR (20)
@nvSortOrderBy2 NCHAR (4)
@nvSortOrder3 NVARCHAR (20)
@nvSortOrderBy3 NCHAR (4)
AS
DECLARE @query nvarchar(4000)
BEGIN
SELECT
FD.Id,
FDS.Size,
FDM.Make,
IssuedTo,
IssuedDate
FROM
[dbo].[tblFlashDrive] FD
LEFT OUTER JOIN dbo.tblFlashDriveSize FDS ON FDS.SizeId = FD.SizeId
LEFT OUTER JOIN dbo.tblFlashDriveMake FDM ON FDM.MakeId = FD.MakeId
WHERE
Issued = '1'
ORDER BY
-----* TO DO *-------
END
******************************************************
NOTE: I don't use dynamic SQL with the statement 'EXEC' as i am doing reports and if is use 'EXEC' then the columns are unavailable during the deisgn time. So, i am just focussing on building a SQL Query inside a stored procedure.
There are three sets of dropdowns on the Front End
1) SortBy1, SortOrder1
2) SortBy2, SortOrder2
3) SortBy3, SortOrder3
Which has the follwing options
1) ID
2) IssuedDate
3) Size
4) Make
5) IssuedTo
User can select either one or more than a set of dropdowns on the Front end say
1) ID ASC
2) ID DESC
3) ID ASC, IssuedDate DESC
4) ID ASC, IssuedDate DESC, Size DESC
5) IssuedTo ASC, IssuedDate DESC, Size DESC
and so on..
So, i would like to use the above options in the ORDER BY Clause.
Hope i gave a good picture of what i am aiming for.. thanks in advance Lynn.
June 17, 2010 at 7:39 am
Getting there but not quite there. Still need sample data for the tables as a series of INSERT INTO statements and expected results based on the sample data and different user inputs.
Don't need a lot of data, just enough to be representative of you problem domain.
June 17, 2010 at 8:00 am
Ok Lynn, Here is the sample data
INSERT INTO [dbo].[tblFlashDrive]([Id],[SizeId],[MakeId],[IssuedTo],[IssuedDate],[Issued] VALUES (1,2,1,900,06/17/2010,'1')
INSERT INTO [dbo].[tblFlashDrive]([Id],[SizeId],[MakeId],[IssuedTo],[IssuedDate],[Issued] VALUES (1,2,1,800,06/17/2010,'1')
INSERT INTO [dbo].[tblFlashDrive]([Id],[SizeId],[MakeId],[IssuedTo],[IssuedDate],[Issued] VALUES (1,2,2,700,06/17/2010,'1')
INSERT INTO [dbo].[tblFlashDrive]([Id],[SizeId],[MakeId],[IssuedTo],[IssuedDate],[Issued] VALUES (1,1,2,800,06/17/2010,'1')
INSERT INTO [dbo].[tblFlashDrive]([Id],[SizeId],[MakeId],[IssuedTo],[IssuedDate],[Issued] VALUES (1,1,1,900,06/17/2010,'1')
GO
INSERT INTO [dbo].[tblFlashDriveMake]([MakeId],[Make]) VALUES (1,'SanDisk')
INSERT INTO [dbo].[tblFlashDriveMake]([MakeId],[Make]) VALUES (2,'Kingston')
INSERT INTO [dbo].[tblFlashDriveMake]([MakeId],[Make]) VALUES (3,'Memorex')
Go
INSERT INTO [dbo].[tblFlashDriveSize]([SizeId],[Size]) VALUES (1,'1GB')
INSERT INTO [dbo].[tblFlashDriveSize]([SizeId],[Size]) VALUES (2,'2GB')
Let me know if you need more details..thanks
December 2, 2010 at 9:34 am
Not sure if you got an answer to your question but I am a little confused why you need to have multiple variables that are virtually the same thing:
Also i have tried using CASE statement:
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC
@sortorder1 & @sortorder2 hold the sorting values correct? Why do you need to pass that in two different variables?
If you only had one variable for the sort order, and one variable for the sorting by column it should work correctly.
December 5, 2010 at 12:30 pm
I have not run the complete DDL and data through, but looking at your CASE statement for the ORDER BY it looks a bit odd;
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC
You have 4 separate case statements each producing an order-by condition - so, for example, if your @SortOrder1 variable equals 'DESC' and @SortBy1 equals 'ID' then I think you are getting an ORDER BY that looks something like;
ORDER BY ASC, ID DESC, ASC, DESC
Chris
December 13, 2010 at 11:07 am
Here's a simple way.
Create a local temp table with an identity column called sort_id, plus all the columns you want in your output query.
Use dynamic sql to populate the temp table IN THE CORRECT ORDER.
Select all the columns (except sort_id) from the temp table with an order by sort_id.
Pay attention to sql injection attacks since you're using dynamic sql, by the way.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply