ERROR: The Nested Query May Be Missing An ORDER BY Clause.
Aug 19, 2006
Hi,
on executing the below query i am getting the following error
ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.
even though the order by clause is presenet in the nested query
SELECT t.[ProductId], Predict ([Association].[Product Basket],3)
From
[Association]
PREDICTION JOIN
SHAPE {
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
APPEND
({OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
RELATE [ProductId] To [ProductId]
)
AS
[Product] AS t
ON
[Association].[Product Id] = t.[ProductId] AND
[Association].[Product Basket].[Product Id] = t.[Product].[ProductId]
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.
Code: SELECT * FROM DMTM SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223' WHERE temp_pk in
Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau
In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.
I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope works. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id], [Project1].[DirectoryId] AS [DirectoryId], [Project1].[SPI] AS [SPI], [Project1].[FirstName] AS [FirstName], [Project1].[LastName] AS [LastName], [Project1].[NPI] AS [NPI], [Project1].[AddressLine1] AS [AddressLine1], [Project1].[AddressLine2] AS [AddressLine2],
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons');
then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75));
select * from test_sort order by cast( description as nvarchar(75));
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from ( select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data where dm_type = 'prime' and dm_closedate <= '31-dec-2007' and dm_closedate >= '1-dec-2007' and program = 'aads' group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from (select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days from dm_data dm, ProgramXref px where px.Program_Name = 'aads' and dm.Program_Id = px.Program_Id and dm.dm_type = 'prime' and dm.dm_closedate <= '31-dec-2007' and dm.dm_closedate >= '1-dec-2007' group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM " sSQL = sSQL & sView sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"
The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
If I use the order by clause to sort on a date, where the date andtime stamp are the exact same for multiple records, how does SQLoutput the data?At random... or does it look at the primary key?
So I have an MDX query in an SSRS data set. Here is my MDX query:
Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses)
Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run.
How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
Hello,Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8....98....0,0,0My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?Thanks, Curt.
The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical? SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1 ORDER BY Country_Long
Hi! I think the order by clause is driving me crazy.The following T-SQL query works: SELECT count(*) AS c From F_POST Where id=@id Order by c Ok, so far so good, but in the following case it is NOT possible to order the result set according to "count(*)": Select T_Date AS TDATE, count(*) AS c From F_Post Where id=@id Order By case when @OrderBy = 1 then c elseT_DATE DESC This is just strange since it is essentially the same query!? Furthermore, it seems to be inpossible to have a order-by-clause that looks like: Order By case when @OrderBy = 1 then T_Column1 ASC elseT_Column2 DESC
Does anyone know how I can implement querys that do (almost) exactly this what the last 2 querys "should" to do?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results. Any way, we can fix it. i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work.
IF @StartDate = 'ALL DATES' BEGIN SELECT @MinDate = Min(AccessTime) FROM tblAudit END ELSE BEGIN SELECT @MinDate = @StartDate END
IF @EndDate = 'ALL DATES' BEGIN SELECT @MaxDate = Max(AccessTime) FROM tblAudit END ELSE BEGIN SELECT @MaxDate = @StartDate END
BEGIN SELECT tblReports.ReportName, tblReports.ReportCode, tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime, tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime, tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime) FROM tblReports INNER JOIN tblReportsAccess ON tblReports.Report_ID = tblReportsAccess.Report_ID INNER JOIN tblAudit ON tblReportsAccess.Audit_ID = tblAudit.Audit_ID WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy) END
I have a query that returns several ordered rows where one of the fields in the ORDER BY clause is a date field (DueDate) that we use to see the most pressing deadline first. The problem is that the default value in that field (which other code translates to mean no due date) is 1/1/1900. That means that items with no due date show up before today's import deadline. I can see one potential solution where I join my results on the original table where DueDate>1/1/1900 and then back to my results so I can use an ISNULL() on the field to set a value in the future (like 1/1/9999), but that seems like a really nasty wrong round-about way to do it. I think there has to be something better.
I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.
Need to pass column to ORDER BY as parameter in sp (possible 8 columns out of total 30). Is there a way to do it avoiding dynamic SQL use(will be used frequently)?
