Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you.
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example. I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block CREATE FUNCTION fnGetAvgCost(@ProdID int) RETURNS @RetTable TABLE (AvgCost money) AS BEGIN WITH Product(stdcost) AS ( SELECT avg(standardcost) as AvgCost FROM Production.ProductCostHistory WHERE ProductID = @ProdID ) INSERT INTO @RetTable SELECT * FROM Product RETURN END
and then run a sample T-SQL statement
Code Block SELECT p.Name, p.ProductNumber, Convert(varchar, cost.AvgCost,1) AS 'Average Cost' FROM Production.Product p CROSS APPLY fnGetAvgCost(p.ProductID) AS cost WHERE cost.AvgCost IS NOT NULL ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
Why am I getting a different numbers of distinct ids in those queries?
USE AdventureWorks go Declare @myXml as xml set @myXml = ' <lol>omg</lol> <lol>rofl</lol> ';
select locations.*, T.c.value('.','nvarchar(max)') from ( select newid() as Id from Production.ProductModel where ProductModelID in (7, 8) ) as locations cross apply @myXml.nodes('(/lol)') T(c);
select mytable.* , T.c.value('.','nvarchar(max)') from ( select newid() as Id union select newid() ) as mytable cross apply @myXml.nodes('(/lol)') T(c);
I've been trying to figure out why these two return a different amount of distinct ids... Is that a bug in optimization?
Code Snippet
USE AdventureWorks go Declare @myXml as xml set @myXml = ' <lol>omg</lol> <lol>rofl</lol> ';
WITH locations as ( select newid() as Id from Production.ProductModel where ProductModelID in (7, 8) ) select locations.*, T.c.value('.','nvarchar(max)') from locations cross apply @myXml.nodes('(/lol)') T(c);
with mytable as ( select newid() as Id union select newid() ) select mytable.* , T.c.value('.','nvarchar(max)') from mytable cross apply @myXml.nodes('(/lol)') T(c);
I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.
This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.
The problem appears to be with the second "Cross Apply". What I'm attempting to accomplish is to count all models produced, regardless of time frame.
When executed the query appears to go into an loop and I end up canceling out the request.
Any ideas where I went wrong?? Any help is greatly appreciated!
select b1.model as Model ,b1.MinDate as Mfg_Str_Date ,b1.MaxDate as Mfg_End_Date ,Count(b2.Model+B2.Serial) as Mfg_Date_Valid ,Count(b3.Model+B3.Serial) as All_Units
from (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate from etbl_models_Serial as b group by b.model) as b1
--These are Units produced within Valid Window cross apply (select b2.model,b2.Serial from etbl_Production as b2 where b2.Model = b1.Model and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2
--These are all units produced cross apply (select b3.model,b3.Serial from etbl_Production as b3 where b3.Model = b2.Model) as b3
Group by b1.Model, b1.MinDate, b1.MaxDate Order by b1.Model
Select top 1000 a.EmployeeID,b.* from #TmpActiveEmployeesWSeverance a cross apply dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b order by a.EmployeeID,a.BenefitTypeID
It runs 4 seconds
If I try to insert the results into anything It runs > 5 minutes (I have yet to let it finish)
I have tried the two following pieces of code, both with the same results
Select top 1000 a.EmployeeID,b.* into #Tmp from #TmpActiveEmployeesWSeverance a cross apply dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b order by a.EmployeeID,a.BenefitTypeID
--and Insert Into TRP_ActiveEmployeesWSeverance (EmployeeID ,PK ,BeginningBalance ,BenefitInterestRowID ,BenefitInterestID ,BenefitTypeID ,DateReceived ,InvoiceDate ,Amount ,Hours ,Fraction1 ,Fraction2 ,Interest ,InterestAmount ,StartDate ,EndDate ,PeriodApplied ,Offset ,Reserve ,Account ,BenefitClosedID ,PaidOut ,ClosedAccount ,ai ,ClosedDate ,StartAgain ,PartialDividend ,PartialFraction ,SameDateCount) Select top 1000 a.EmployeeID,b.* from #TmpActiveEmployeesWSeverance a cross apply dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b order by a.EmployeeID,a.BenefitTypeID
SELECT res.res_id, sub.value('(./@id)[1]', 'char(2)') id FROM vwResult res CROSS APPLY report.nodes('/clue_personal_auto/report/search_dataset/subjects/*') report(sub)
It works just fine in SQL Query.After placing this into a view in SSDT November 2012 update, I get a compilation error.
I am doing a report that uses paging and in order to optimize it, i used row_number() so i could make it return x rows per page, so, in order to compute the number of pages needed, i have to count the total number of rows, which gets very slow because i'm using a cross apply with a table-valued function. Is there any way so i can get the number of rows processed by row_number() so i dont have the need to do count?
The result I want is the unique rows from columns:
PupilPersonId, EducationTypeId,VehicleTypeId AND there MAX EducationDate SELECT er1.* FROM EducationResult er1 INNER JOIN ( SELECT er.PupilPersonId, er.EducationTypeId, er.VehicleTypeId, MAX(er.EducationDate) as EducationDate
[Code] ....
I like to know is there another approach with CTE and or Cross Apply I can use instead?
ChangeID ChangeDate EquipmentID ModuleID EquipStatus 1 12/9/08 230 1789 Normal 2 13/9/08 450 1245 Normal 3 17/9/08 230 1789 Open 4 21/9/08 230 1899 Open 5 21/9/08 450 1674 Normal 6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.* FROM your_table AS outerT CROSS APPLY ( SELECT TOP 1 equipment_id , change_date FROM your_table AS innerT WHERE innerT.change_date <= @point_in_time AND innerT.equipment_id = outerT.equipment_id ORDER BY change_date DESC ) AS applicable_records WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle( Cycle INT NOT NULL, PatientID INT NOT NULL, ALOPECIA TINYINT, Causality1 TINYINT, Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2 CREATE TABLE #Suppliers (ID INT, Product VARCHAR(500) ,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500) ,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.
The error is: Incorrect syntax near '.'
the query in question resembles this:
Select column1, column2 from Table1 T cross apply function(t.column4,t.column5) F where column3 = 'XXXX'
I made sure that the compatibility level is greater than 90 this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)
Can it be the .net framework? Although the machines had .net framework 3.52.
Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2
I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
create table a (id int, name varchar(10)); create table b(id int, sal int); insert into a values(1,'John'),(1,'ken'),(2,'paul'); insert into b values(1,400),(1,500);
select * from a cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal 1John500 1ken500 2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.
Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.
Here are some things I’ve tried so far…
1.Changed the States table into a subquery that only returns a single state 2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other). 3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.
SELECT s.state_name , COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits , SUM(DistinctPermits) AS DistinctPermits FROM States S CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits
The following query is failing when trying to apply the MAX(field_x_order) to the variable @max Note the x is represented by the string variable @stri
declare @i int declare @stri varchar(10) declare @max int set @i = 18 set @max = 0 while @i < 49 begin set @i = @i + 1 set @stri = cast(@i as varchar(10)) select @max = MAX(field_ + @stri + _ORDER) FROM table_name WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near the keyword 'IS'. exec ('UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL') end
I have also tried: select MAX(field_ + @stri + _ORDER) INTO @max = FROM display_1a WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near '@max'.
and: select @max = ('SELECT MAX(field_' + @stri + '_ORDER) FROM display_1a WHERE field_' + @stri + ' IS NOT NULL') -- error: Conversion failed when converting the varchar value 'SELECT MAX(field_19_ORDER) FROM display_1a WHERE field_19 IS NOT NULL' to data type int.
1982 Jammu and kashmir $30,000.00 1 Animal Husbandary
1882 Jharkhand $35,000.00 1 Animal Husbandary
1982 Karnataka $40,000.00 1 Animal Husbandary
1982 Kerala $20,000.00 1 Animal Husbandary
1982 Madhya pradesh $5,000.00 1 Animal Husbandary
I want to produce report as by using crosstab query as showing bellow
Year Asam Hyadrabad goa arunachal pardesh etc............ 1981 1000.00 2000.00 8000.2 00000 000000 ...... 1981 ' ' Is it possible by crosstab query ?or please suggest me another way as early as possible.
I wanna develope a report using cross tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008 test 45 50 25 10 11
Please can any one help me how to do this this is very urgent ?
So I have 2 options : I have to do in in Cross tab or I have to create a query for this. Is it possible to built a query like this,I mean assigning data as column?
we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?
Hi - I have what I think is a "simple problem".We currently have a database table that stores ItemProperties byItemID, PropertyID and Value. (The PropertyID references another tablefor property names and types.)This ItemProperties table is indexed and provides a flexible way ofstoring our item metadata. However, I would now like to returnrecordsets to the application layer that list these properties incolumn fashion, grouped by ItemIDI have seen that most cross-tab queries examples assume numerical dataand are based around using SUM and the GROUP BY phrase. But our datais a mixture of string and numbers (of various formats) and so GROUPBY is not an obvious solution. I have tried using CASE in the selectlist but this returns one row for each property with one column havingthe correct value, and all the other colums are NULL. I cannot thinkof how to combine these into one full record!!I could achieve the desired resultset by using a SELECT sub-statementfor every column, but I was hoping there was a more efficient method.Can anyone offer advice on this? It would be most appreciated.Best,Bill
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?
I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2, SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?
