.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Asad Ali
Post New Web Links

Why does the engine ignore my Cross Apply?

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
 
I'm a beginner attempting to understand cross apply. Suppose I have a Numbers table with only one column: DECLARE @Nums Table(Num int) INSERT INTO @Nums (Num) Values (5) INSERT INTO @Nums (Num) Values (6) INSERT INTO @Nums (Num) Values (7) The following produces two columns of output (in SSMS Express 2005). SELECT  * FROM @Nums         CROSS APPLY         (             SELECT  Num * 2 as Doubled         ) as NewTable Now let's change the SELECT * to  SELECT Num (but I would expect the same result since that's the only column in the table). SELECT  Num FROM @Nums         CROSS APPLY         (             SELECT  Num * 2 as Doubled         ) as NewTable But instead I only get one column of output - the engine simply ignores my Cross Apply clause. Why?    After all, I'm told that using SELECT * is bad practice because I should name my columns explicitly. But in this case I'm getting punished for naming my columns explicitly?  I must be doing something wrong, but I don't know what it is.


View Complete Post


More Related Resource Links

XQUERY using Cross Apply and Outer Apply

  
Trying to shred XML into relational table rows and can do it for one set of node values .. having difficulty creating a pair of columns. The relevant part of the XML looks like this ... <ns:Coverage>      <ns:CoverageCd>cvalue1</ns:CoverageCd>      <ns:Form>           <ns:FormNumber>fvalue1</ns:FormNumber>      </ns:Form> </ns:Coverage> I can successfully navigate through the XML and pull back a set of rows for all values of CoverageCd. Now I'm trying to add a column to each output row for the FormNumber value associated with the CoverageCd value. However; FormNumber is not always present. I found an example where using 'Outer Apply' can account for that, but it's not working for me. Here's my XQuery: WITH XMLNAMESPACES('http://www.mycompanyname.com/ACORD1.11.0/Policy_1.0/xml' AS "ns") select cov.i.value('.', 'varchar(30)')   [Coverage],          form.i.value('.', 'varchar(30)')  [FormNumber] from  dbo.mytablename        cross apply XMLcolumn.nodes('.//ns:Coverage/ns:CoverageCd') as cov(i)        outer apply cov.i.nodes('.//ns:Coverage/ns:Form/ns:FormNumber) as form(

CROSS APPLY a FREETEXTTABLE

  
table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! CREATE FUNCTION fnt_FullTextSearch ( @s NVARCHAR(4000) ) RETURNS TABLE AS RETURN ( SELECT [key], [rank] FROM FREETEXTTABLE(table1, *, @s) ) DECLARE @terms TABLE ( term VARCHAR(MAX) ) INSERT INTO @terms VALUES ( 'flu' ) INSERT INTO @terms VALUES ( 'acid' ) --The inline function "..." cannot take correlated parameters or subqueries -- because it uses a full-text operator. SELECT ft.[key], ft.[rank] FROM @terms CROSS APPLY fnt_FullTextSearch(term) ft --syntax error on term SELECT ft.[key], ft.[rank] FROM @terms CROSS APPLY FREETEXTTABLE(table1, *, term)

problem with cross apply query

  
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], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;

CROSS APPLY doesn't work in some databases

  
I've run across a weird error trying to use CROSS APPLY. Running SQL2005SP2 developer edition on XP2 (but same error occurs on Enterprise running on W2K3).A simple CROSS APPLY always works when run from master, but doesn't work when run from most (but not all) of my other user databases. I can't figure out what could be causing the error, or is there some reason it would only work when the current database is master?Here is the simplest testcase I can come up with:select a.spid,b.text from master..sysprocesses a cross apply ::fn_get_sql(a.sql_handle) bIf this is run while the current database is master, it returns spid and SQL without a problem. If it's run in the other SQL-delivered databases (model, msdb, tempb), it also works fine. However, if it's run in all but one of the user databases, I get back an error:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'a'.I'm stumped on how to troubleshoot this. There is no difference in the query; the only difference is the current database. The databases are all owned by sa. I get the same results with sys.sysprocesses instead of master..sysprocesses.As another datapoint, the employee/department example in the BOL for CROSS APPLY exhibits the same behavior; it works fine if run with master as the current database (regardless of where the tables are created), it fails withMsg 102, Level 15, State 1, Line 3Incorrect synt

Inline table valued function, full outer join and cross apply

  
Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Using CROSS APPLY in a data source query causes ARITHABORT error

  

I have an issue where the following query as an OLEDB data source throws the following error:

SELECT XmlNode.value('SomeValue[1]', 'varchar(10)') as SomeValue

FROM (SELECT CAST('<TestXML><SomeValue>123</SomeValue></TestXML>' AS XML

group by clause in cross apply

  

Hi,

I am using cross apply against 2 huge tables and I am trying to show the number of customers customers in 3 of the countries US, Canada, UK. When I use my cross apply how do I show the count of customers in the resultset. I have to do a cross apply becoz I do not have fields that have unique data that I can join on.

Example:

Product UK Customer US Customer CA Customer

A          1000              2000             3000

B           0                  500               2000

C         1200              2250              0

D         3400              5650              2390  

 

Pleas

Cross Apply

  

Hello,

  I have created the following funcation :

 ALTER FUNCTION [dbo].[fnTopNByCategory]

(@TopN int, @CategoryID int)

RETURNS

Cross Apply

  
Hello
ASk

How do I preserve __viewstate between cross domain posts or how do I get a __viewstate of a Remote S

  

Here is what I want to do:
I have a local site in which I want to display data from a remote site
Lets say: I want to display data of http://www.abc.com/Default.aspx on my localhost

Now Default.aspx requires some post-data which it sends to itself. Thus the __viewstate and __eventvalidation are posted back to it by itself. I want to directly post the data to Default.aspx from localhost without opening Default.aspx and display the Default.aspx's response on my localhost.

The WebApp on http://www.abc.com is configured for __eventvalidation i.e. I cannot post-data to it from my localhost without getting a __viewstate from it. I also have to post the current __viewstate of http://www.abc.com/Default.aspx to itself

How can I accomplish it?


Stop Sorting Cross Tab Report Header

  

Dear, I want to stop sorting in cross tab report header. And also want to sort in specific order. How can i do that? Please help me. Its urgent

Jewel


Cloud Storage: Fueling Your Application's Engine with Windows Azure Storage

  

Yes, you can run background processes in the cloud. Kevin Hoffman and Nate Dudek use a shopping cart example to demonstrate how to build an application engine as well as implement asynchronous messaging and processing using Azure Storage.

Kevin Hoffman, Nathan Dudek

MSDN Magazine January 2010


Extreme ASP.NET: Search Engine Optimization with ASP.NET 4.0, Visual Studio 2010 and IIS7

  

In this article, the author takes a look at SEO practices that the reader can apply when using the latest Microsoft Web technologies.

Scott Allen

MSDN Magazine September 2009


SOA Simplified: Service Virtualization With The Managed Services Engine

  

By using Managed Services Engine service virtualization technology, you can begin participating in the cloud by simply configuring virtual service endpoints and operations that integrate with the .NET Service Bus.

Aaron Skonnard

MSDN Magazine May 2009


Foundations: Easily Apply Transactions To Services

  

Managing state and error recovery using transactions is the topic of this month's installment of Foundations.

Juval Lowy

MSDN Magazine January 2009


Toolbox: Cross Browser Testing, Mock Objects, and Raymond Chen

  

This month test your web site on many platforms and browsers without setting up a test environment, use mock objects for unit testing, and visit Raymond Chen.

Scott Mitchell

MSDN Magazine June 2008


Unit Testing: Apply Test-Driven Development to your Database Projects

  

Jamie Laflen extols the benefits of TDD when applied to database development-and supplies some useful techniques along the way.

Jamie Laflen

MSDN Magazine Launch 2008


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend