Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
ORDER BY CASE WHEN @SortOrder = 'Sales' THEN Sum(SALES.Sale) WHEN @SortOrder = 'Profit' THEN Sum(GP) WHEN @SortOrder = 'Product' THEN SALES.Product END
Error:
Warning: Null value is eliminated by an aggregate or other SET operation. Server: Msg 8114, Level 16, State 5, Line 8 Error converting data type varchar to float.
I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:
CREATE PROCEDURE GetFromTable ( @SortOn varchar(20) ) AS SELECT * FROM Table ORDER BY @SortOn GO
This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?
I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.
currently I am ordering it by:
c.CurrentItemNumber , p.PriorItemNumber
among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).
Is this possible?
This is my stored procedure for reference:
ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison] ( @CurrentSalesQuoteNumberNVARCHAR(20) ,@PriorSalesQuoteNumberNVARCHAR(20) ,@CurrentItemNumber1NVARCHAR(20) ,@PriorItemNumber1NVARCHAR(20) ,@CurrentItemNumber2NVARCHAR(20)
hi all, hope someone can help....i'm having trouble calling an SP where the ORDER BY operator is specified asa parameter when the SP is calledmy SP is.....CREATE PROCEDURE dbo.sp_CustSearch (@SearchFor VARCHAR(80) , @SortOrderVARCHAR(50))ASBEGINSELECT first_name, last_name, postcode , address, townFROM customer WITH (nolock)WHERE (UPPER (last_name) LIKE '%' + @SearchFor + '%'OR UPPER(address.postcode) = @SearchFor )ORDER BY @SortOrderENDGOthe line causing the problem is ORDER BY @SortOrderthe error i get on checking the syntax is:"Error 1008: The SELECT item identified by the ORDER BY number 1 contains avariable as part of the expression identifying a column position. Variablesare only allowed when ordering by an expression referencing a column name"anyone know how to solve this? i'm guessing it's something simple.enclosing @SortOrder in single quotes gives, unsuprisinlgy, unsortedresults.trying ORDER BY '[' + @SortOrder + ']' gives the same error asabovethe only way i've managed to get it working so far is to dynamically buildthe SQL statement and then execute that at the end. it's a little messywhich was why i was trying to get the above working.thanks in advance.
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
Hi all, I've been trying to rack my brains over this one; When I use this select statement in SqlDataAdapter, it runs fine; it takes about 4 to 5 seconds to run. In SqlDataSource, its very erratic; taking between 8 to 71 seconds to run. I've finally narrowed down the issue to something that resolves the symptoms to something in my query; SELECT f.UnitID, f.FacilityName, SUM(CASE WHEN t .sData_Type = 'CLINICAL' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Clinical, SUM(CASE WHEN t .sData_Type = 'OTHERS' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Others, SUM(t.nWrite_Off_Amt) AS Sum_Total, SUM(t.nCashAmount) AS Sum_Cash, t.iYearFROM t LEFT OUTER JOIN d ON t.sUnitID = d.sUnitID AND t.sPat_Num = d.sPat_Num AND t.iMonth = d.iMonth AND t.iYear = d.iYear LEFT OUTER JOIN tblFac AS f ON t.sUnitID = f.UnitID_1WHERE (t.iYear = @year) AND (t.iMonth = @month) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))GROUP BY f.UnitID, t.iYear, f.FacilityName, f.Division If I change my WHERE clause to the following WHERE (@year = t.iYear) AND (@month = t.iMonth) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5))) performance is MUCH better. How could such a little change make such a big difference in speed? Thanks. -e
I use a DataGrid to show the data, and I want it has a sorting and Paging function, so I use dataset to collect the data from middle tier function and stored procedure. I have code in aspx page like BindData(ViewState("SortExpr")).
In the stored procedure I pass SortExpr as parameter as following:
DECLARE @RowCount int SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0 BEGIN SELECT z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance /* The above functions requires the Distance Assistant. */ FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a WHERE z.Latitude <= r.MaxLat AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND z.CityType = 'D' AND z.ZIPCodeType <> 'M' AND z.ZIPCode = a.Zipcode AND a.AdActive = '1' AND a.AdExpiredate >= getdate() AND a.Make = @Make AND a.Model = IsNull(@Model,a.Model) AND a.Condition = IsNull(@Condition, a.Condition) AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles ORDER BY @SortExpr END ELSE SELECT -1 As ZIPCode --ZIP Code not found... GO
but I got the error as "variables are only allowed when ordering by an expression referenceing a column name". How I fix this error? Please help.
I've run into something that really isn't a problem - I corrected the issue with a CTE solution - but, it did raise a question.
I was developing a report and, when I previewed it, I got an error that a parameter was dependent upon the value of another parameter - no forward dependencies allowed.
But, another parameter that used the exact same SQL code - just different fields - works fine.
So, here's the list of parameters;
CurDate is derived by; DECLARE @CurDate AS date SET @CurDate = ( SELECT MAX(Load_Date) FROM Census_Rpt_Final )
The parameter that gives me the problem is PCPName. It is a list of names derived by the following;
SELECT DISTINCT UPPER(PCP_Name) AS [PCP Name] FROM Census_Rpt_Final WHERE Load_Date = @CurDate ORDER BY [PCP Name];
So, OK, fine, I can understand the error. But, MemberName is derived by;
SELECT DISTINCT RTRIM(UPPER(Member_LName)) + ', ' + RTRIM(UPPER(Member_Fname)) AS [Member Name] FROM Census_Rpt_Final WHERE Load_Date = @CurDate ORDER BY [Member Name];
And, it works fine. No Foward Dependency problem at all.
So, my question becomes one of precedence. I see, in the list of parameters, that PCPName precedes CurDate which precedes MemberName. Is the problem because, when it hits PCPName, CurDate has yet to be defined, but when it hits MemberName, CurDate has been defined?
Is SSRS one-pass compilerĀ or multi-pass? My guess is that it's a one-pass, because of the error I'm getting. As I said, I've corrected the problem using;
WITH Date_of_Most_Recent_Census AS ( SELECT MAX(Load_Date) AS [CurDate]
[Code] ....
Removing the parameter reference from the code, but it made me curious as to why one worked and the other didn't.
can i pass the name of the table and the "order by" column name to stored procedure? i tried the simple way (@tablename varchar and then "select * from @tablename) but i get error massesges. the same for order by... what is the right syntex for this task?
the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?
In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:
'Document Date (most recent date first)' 'Document Number (highest number first)' 'Document Date (most recent first) and Number' 'Document Number (lowest number first)'
The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.
So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.
The code is:
DECLARE @ls_OrderBy varchar(80) --'Document Number (highest number first)' --'Customer and Document Date (most recent date first)' --'Customer and Document Number (highest number first)' --'Document Date (most recent first) and Number'
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original - sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @datesmalldatetime ) AS
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into datatable values
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO
If I return top 25 with an order by, will it get the whole result then take top 25 and order it or will it just return the first 25 it finds and order it. select top 25 c.firstname, c.lastname, o.units, o.partnum from customer c inner join orders o on c.key = o.key where o.units > 500 order by partnum
I have a Microsoft SQL Server 7.0.I wrote a sql command that creates a temporary table with a ORDER BYclause.When a execute a SELECT on this temporary table sometimes the result isok, but sometimes is not ordered. I didn“t see anything like that. Anyclue?Is there any kind of limits with temporary tables ? Because the commandthat creates the temporary table is working and the rsults is alwaysordered. But when I create a table with it, sometimes the table is notordered.Paulo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
Hi I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc DECLARE @Counter TABLE( PlanId int, FundId int, ClientFundName varchar(110), DisplayOrder int IDENTITY(1,1), IsDefault bit, IsPortfolioFundOnly bit ) INSERT INTO @Counter ( PlanId, FundId, ClientFundName, IsDefault, IsPortfolioFundOnly ) SELECT 5923, f.FundId, d.FundName, CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END, CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END FROM PlanDetail d INNER JOIN Statements..Fund f ON d.CUSIP = f.CUSIP OR d.Ticker = f.Ticker OR d.Ticker = f.ClientFundId OR d.CUSIP = f.ClientFundId -- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether --fund can only be chosen as part of a portfolio. LEFT JOIN PlanDetail p ON d.FundName = p.FundName AND d.PortfolioName = p.PortfolioName WHERE d.PlanNumber IS NOT NULL AND p.PortFundPercent IS NULL GROUP BY f.FundId, d.FundName, d.FundDefault, --d.PlanNumber, --d.Cusip, -- d.Ticker, --d.RowNumber, p.FundType
I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
SELECT DISTINCT pdm.Account, pdm.Customer FROM dbo.Demands pdm LEFT OUTER JOIN dbo.Tickets rct ON pdm.Account = rct.Account WHERE pdm.Code IN (66, 51) ORDER BY pdm.TransactionDate DESC
Is there any way to make the ORDER BY work in this case?
I have table called 'UserDetails'. If I execute below select query it should display in order of uno= 7,13,5 but i get in order of
uno=5,7,13.
How to get in order of uno= 7,13,5
Select EmailAddress,EmployeeName,UNo, MobileNumber from UserDetails where (UNo=7 or UNo=13 or UNo=5 ) group by uno,emailaddress,employeename,uno,mobilenumber
Result I am getting as
EmailAddress EmployeeName UNo MobileNumber ----------------------------------------------------------- aaa@xxx.com ravi 5 8989898989 bbb@xxx.comramesh 79898989898 ariv@gmail.com arivu 13 8989898989
If i have two identical queries, with exception of top criteriaSelect top 1 * from photosSelect top 8 * from photoswhy does altering the top critieria alter the order of the returnedresults?
I have the following basic statements being executed:Create a temp table, #TempPagingInsert Into #TempPaging (Col1, Col2)Select Col1, Col2 From SomeOtherTable Order By Col2, Col1Select * from #TempPagingI can't provide a reproduceable scenario right now without making thisinto a 200K post, so I'm hoping someone will know what the issue is.. Ican't find anything regarding this in BOL.Basically the order is off a little bit every now and then when thereare large amounts of data, like more than a couple hundred rows. Isthere something I need to do to guarantee the originally selectedorder?This is very important that it be in order from the original selectstatement as this is for paging. Adding an order by in the secondselect from the temp table will not fix the problem. In this particularinstance that I have reproduced we are using SQL 2005 but have alsoseen this on SQL 2000 servers. I had previously asked this question asI was using a SELECT INTO statement, but now we are manually creatingthe temp table (Pain in the ass) and still having the same issue. Bestcase scenario is for it to work for a SELECT INTO.Any ideas?
Dear GroupI wonder whether you can push me in a direction on how to design thefollowing statement. I'm looking for a SELECT with some tricky ORDERBY.The database table looks like this:MenuID TabText SubTabID TabOrderID------- ----------- ----------- -----------1 Main 0 12 Cars 0 23 Boats 0 34 Planes 0 45 Pick-Ups 2 16 Campers 2 2The result should look like this:MainCarsPick-UpsCampersBoatsPlanesNotice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' andmust appear in the result directly following 'Cars'.In more detail:'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabIDvalue of an item identifies to what top-level category a subcategorybelongs.The TabOrderID specifies in what order the items should be sorted,e.g. 'Pick-Ups' comes first and 'Campers' second.Thanks very much for your help & efforts!Martin
I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table: 1- the exact match for the search string come first from the two table. 2- and the partial match comes last after the exact match.
this is my DDL for the two tables :
USE [Northwind] GO /****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Person]( [PersonID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
second table: USE [Northwind] GO /****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Members]( [MemberID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF
and this my search query I have it in a stored Proc.
select *
from
(
SELECT PersonID, Type, Name, email
FROM Person WHERE (Name LIKE '%'@Name'%') union all
This may sound like a dumb question, but I need to be certain of the answer.
If I have a query like this:
INSERT INTO table1 (col1, col2, col3, ... ) SELECT col4, col5, col6, ... FROM table2 ORDER BY col7, col8, col9, ...
and table1 has an identity column that increments by 1 each time, am I gauranteed that the records inserted into table1 will always be inserted in the order as specified in the ORDER BY clause and hence the increasing identity column in table1 will reflect the same order as that of the ORDER BY clause?
Seems like it should be the case but I need to be sure.
I have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.
But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.