Cross apply from function not working

I have the following function:

***************************************
USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[fnTops]
  (@Top int, @CategoryID int)
RETURNS TABLE
AS
  RETURN
    SELECT TOP(@Top) ProductName, UnitPrice
    FROM   dbo.Products
    WHERE  CategoryID=@CategoryID
    ORDER BY UnitPrice DESC;
*****************************************

If I do:

select * from [dbo].[fnTops](3,5)

It returns 3 rows fine.

If I do:

SELECT CategoryID, CategoryName, ProductName, UnitPrice
FROM   dbo.Categories C CROSS APPLY
       dbo.fnTops(2, C.CategoryID) P
ORDER BY C.CategoryID, ProductName

I get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'C'.

But if I change C.CategoryID to 3, it works fine.

What is wrong here?

Thanks,

Tom 


0
tshad
9/6/2010 4:17:14 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1289 Views

Similar Articles

[PageSpeed] 44

Most likely the compatibility level of the Northwind database is 80 (SQL Server
2000). Change the compatibility level to 90 (SQL Server 2005) or 100 (SQL Server
2008) and try it again. The CROSS APPLY operator was introduced in SQL Server
2005 and while it works if the database is in compatibility level 80 you cannot
pass references from other source tables.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
9/6/2010 4:43:41 AM
That was it.

Thanks,

Tom
"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:rbs886tr0geva4oah68aig60smq9gsqdvu@4ax.com...
> Most likely the compatibility level of the Northwind database is 80 (SQL 
> Server
> 2000). Change the compatibility level to 90 (SQL Server 2005) or 100 (SQL 
> Server
> 2008) and try it again. The CROSS APPLY operator was introduced in SQL 
> Server
> 2005 and while it works if the database is in compatibility level 80 you 
> cannot
> pass references from other source tables.
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


0
tshad
9/6/2010 7:34:09 PM
Reply:

Similar Artilces:

How do I turn off unwanted (cross functional flowchart) dialogue?
I've created a new document. I wanted to check what shapes were available, so tested a few shapes, including Cross Functional flowchart. I've gone ahead with my diagram within the same document, and (to my knowledge) not used any of the Cross Functional Flowchart elements. Now when I open the document I'm presented with an unwanted cross functional flowchart dialogue, asking what type of flowchart I require [I don't require any...]. How do I remove this dialogue from my document? Thanks in advance Hi Toshi! If you've started a document from the Cross Functional Fl...

VLOOKUP Function using multiple worksheets #2
Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible??...

web query doesn't work
I have previously had success with importing refreshable data into excel. The web site has changed its format and now it says there is no data to import. But it is there. -- BrianAllen ...

Can a macro be made to work based on a formula? #2
Is it possible to have a macro run based on the answer to a question? If the answer to a certain question is yes then the macro would run. A formula in a worksheet cell can call a function written in VBA. But that function can only return a result to the cell containing the formula, just like the built-in functions like SUM and AVERAGE do. The code can't change other cells, do formatting, etc. On Tue, 25 Jan 2005 18:59:04 -0800, "lonnied" <lonnied@discussions.microsoft.com> wrote: >Is it possible to have a macro run based on the answer to a question? If the >ans...

SOS! List of fields for Contact does not show-Advanced Find, Work
Hi, I noticed today, when I try to do an advanced find or edit filter in a view for the Contact entity. All other entities show the list of fields. How do I get them back? Voni "Voni" wrote: > Hi, > > I noticed today, when I try to do an advanced find or edit filter in a view > for the Contact entity, the list of fields do not display. All other entities show the list of fields. > > How do I get them back? > > Voni If I try an Advanced find starting with a view that has selection criteria, for example: My Active Contacts,,,,,,, there is an...

WaitForMultipleObjects , threads and blocking function
Hi, I would like some help about a problem about Threading and blocking function. We have developped a c++ wrapper around wininet to post some specific data. Here is the architecture : A thread always running and waiting on two events, when the event m_hStartRequest is signaled the HTTP request is written to server and once it has finished it send an event to inform the caller that the request is done. DWORD WINAPI CHttpAdapter::ThrRequestAction( IN LPVOID lpvThreadParam ) { HANDLE WaitHnd[2] = {0}; CHttpAdapter* pThis = (CHttpAdapter*) lpvThreadParam; if ( pThis =...

Nasty red crosses
I am really struggling with my website. Images appear on the page but when I upload them they do not appear on the web! Also I cannot get the banner image to appear on all the pages despite being able to see them in Front Page. Having a very bad day! No crystal balls here. Post a URL to the page(s) in question. Most likely you didn't import the images into your web before inserting on a page and saving before publishing, and the links are pointing to your hard drive. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "J...

Outlook 2003 works with which versions of Exchange Server ?
If you have Outlook 2003 connecting to an Exchange Server, does it have to be Exchange 2003 or can it also work with Exchange 2000 and Exchange 5.5? It works with Exchange 5.5, 2000 and 2003. However, there are certain features (such as cached mode headers only and RPC over HTTP) that only work with Exchange 2003 and other features (such as compression of messages) that work better with Exchange 2003. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the newsgroups*** "Julian Milenbach"...

Using MID function within an IF function
I have a simple spreadsheet of about 240 rows with columns as follows: Column A - name Column B - street address Column C - city, state, Zip Column D - phone number Column E - email address Column F - "Permissions" to include data in columns A-E in a directory Column F consists of 1 to 7 characters. The first position is always Y. The other odd positions can be Y, N or blank. The even positions are hyphens or blanks. Examples: Y-Y-Y-Y means OK to include all data in columns A-E in directory Y-N-Y-Y means OK to include cols A, D and E in directory Y-N-Y means OK to include cols A an...

Message Tracking enabled, but not working
Hi there, I have 1 Exchange 2k3 server which has been running for about 3 months now. I enabled message tracking on the server, and had been able to track messages no problem. However, now when doing a search for messages in the message tracking center, the results aren't accurate. It won't return all messages sent to/received from particular senders. I've had a look at the actual message log itself & confirmed that the correct entries are all being logged. It seems to be a problem querying the log. This has only happened recently, as during the initial server setup...

Exchange 2003
I have two Exchange servers, Exchange2003 and Exchange2003Remote. I am trying to create a recipient policy to stamp an additional SMTP address on mailboxes located on Exchange2003Remote. After I create the policy (called RemoteDomain) specifying the remote server where the mailboxes reside I apply the policy and wait for the additional addresses to be added to the mailboxes - no addresses are stamped. I current have a LegacyDN policy with the Highest Priority from our old Exchange 5.5 migration, the RemoteDomain policy with the next highest priority and finally the Default policy. Any as...

AJAX AutocompleteExtender does not work when type between delimeters
I have a multiline textbox attached with ajax autocomplete extender with "\n" as delimeter. When i type between these delimeters, the suggestion list does not appear. Eg. : hi //When i type between these lines of "hi" and "bye", the suggestionlist does not appear. bye If "," is used as delimeter then hi,"PROBLEM",bye suggestionlist does not appear between "hi" and "bye". I want the list to appear anywhere in between delimeters. Submitted via EggHeadCafe - Software Developer Portal of Choice ...

Test to see if new group posting works
Seems to work :o) Welcome back. Tom "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:W_90h.445$Mh6.95@newsfe24.lga... > > Actually it did not work on my end. All of my newsgroup messages quit downloading three days ago. I have to unsubscribe and reboot, then resubscribe to get it to work again. I have the same problem on two identical computers. "Tom Serface" <tserface@msn.com> wrote in message news:u6m6a0U%23GHA.1224@TK2MSFTNGP04.phx.gbl... > Seems to work :o) Welcome back. > > Tom > > "Peter Olcott" <...

Windows live messenger has stopped working
when I boot my PC and start Windows 7 WLM during the startup phase stops working before it has fully started. If however i start it after the boot up process is complete it works fine. I am using Window 7 64 Home Prem... and am running a new ATI 5800 series card, have 4g of ram etc..... Any ideas? I have read through the posts on this board and i do not see my problem though it is likely related to the other ones i have seen. Greetings, It might be a bit hard to isolate what's causing this without disabling other things starting at startup, as it's probably just a...

SPAM filter does not work when rule moves message to other mailbox
It seems that the Outlook 2003 SMAP filter does not work for messages that are moved to another mailbox upon arrival. I have a number of email accounts and would like each of them to receive mail in a different mailbox. To achieve this I have set up rules, moving messages to a different mailbox, depending on which account they were received by. Unfortunately it seems that the SPAM filter does not get applied to messages that get moved. I'd like the messages to first be SMAP filtered and the remaining ones moved to the appropriate mailbox. Does anybody have a solution? Thank...

Query linked to Excel doesn't work
Hi I have a database where one query is linked to Excel file. Query works fine, but I can't refresh it in Excel and I get the info:"[Microsoft][ODBC Microsoft Access Driver]Data type mismatch in criteria expression" what does it mean? When I double click in Access there is no error message. I also tried to relink it again in a new sheet but I can import it (I don't see the query when I get to the "selet table" part). Can you advise? -- Greatly appreciated Eva What that usually means is that a datatype from excel is not the same as the datatyp...

Double click xlsname in explorer doesn't work.
My recent foray into automating excel via its com interface went fine as far as the program is concerned. Unfortunately, somewhere along the way I've messed up its ability to function interactively from my desktop. In particular, when I double click on a xls file in Windows explorer, excel starts up with the message "Windows cannot find "+ the name of the xls in question or a similar dialog with a "Microsoft Excel" caption which reads "<xyz> could not be found. Check... ", where xyz is a garbled form of the name of the directory in which the xls i...

Does SFO work on a Tablet PC
Does the SFO client (ver1.2) work on a Tablet PC Edition 2005 workstation. When Outlook loads and the SFO toolbar starts to initialize, Outlook locks up and goes into a not responding state It should work. Make sure the tablet pc is set to landscape mode. -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Mark J" <Mark J@discussions.microsoft.com> wrote in message news:0BFCCB19-CE62-465E-AB2E-2B863F2B8D7F@microsoft.com... > Does the SFO client (ver1.2) work on a Tablet PC Edition 2005 workstation. > When...

How do I insert a row & have the existing formulas apply to new ce
I have a table which has a column that uses a formula. When I insert a new row in the middle of the table, Excel inserts a blank cell into the column with formulas, rather than keeping the formula that applies to the adjacent rows. How do I insert a row and keep the column with a formula so it applies to that new row? Click on a row number to highlight the entire row, then right click and select "copy". While still highlighted, right click on the same row number and click "insert copied cells". Excel will automatically adjust the formulas for the new row, unles...

trouble with send function
Using Oulook 2000 and Windows 2000. I have the "send" feature on delay -- to send and receive every 10 minutes. After composing a message and clicking on send, the message is supposed to go to the Outbox where its TO and SUBJECT line appear in italics. About 75% of the time, that is exacly what happens. From the outbox I can click on send of off the messages go. However, about 25% of the time the messages in the outbox are not italicized. From the oubox I open them, then click send again, and the still appear in the outbox in normal font. The only way I can successfully send ...

if function equals zero...
I have a long function in a cell thats gets value of another cell. what i want to do is, if it equals zero then set value to "" = IF( my_long_function=0 ; "" ; my_long_function) the function is too long and could be cause errors when uncareful changes made in it (some other people will use it). -how to simplify the above statement? The only suggestions I can give are - work out which part might drive the 0 result and test just that, or - put the formula in another cell and test that cell -- HTH RP (remove nothere from the email address if mailing direct) &qu...

Microsoft works v. Excel
I need to bring some spreadsheet files forward from an old computer to a new one. The files on the old computer were in MS Works. My new computer does not have the old MS Works program, Is there a way I can convert my old MS Works spreadsheet programs to Excell? G'day ED, Is this any help? Works: How to Import and Export Works Spreadsheets to Excel 97 [Q163109] http://support.microsoft.com/d�efault.aspx?scid=KB;en-us;Q163�109 "Excel Dumb" <Excel Dumb@discussions.microsoft.com> wrote in message news:529CCB18-F102-448E-8504-63BF60974240@microsoft.com... | I need to...

Multiple (text) criteria in a "countif" function??
Hello folks - I'm trying to set up a formula to count the number of times it says "A" in Column L, but ONLY IF it also says "Red" in the same record, in column K. Help?? Was hoping some sort of If Then formula existed and I just didn't know it... Appreciate all responses.. Muse of Fire =SUMPRODUCT((K1:K100="A")*(L1:L100="Red")) "Muse 0f Fire" <MuseOfFire@gmail.com> wrote in message news:af78c498.0409271554.fa76cf7@posting.google.com... > Hello folks - > > I'm trying to set up a formula to count the number of ...

Mapping to address field in Quote, order or invoice is not working
In my account form i have added 1 field (postofficebox) and I use it to store the housenumber so I can use the dutch postal code file. I also added this filed in quote, order and invoice. When creating a quote I use lookup address to fill the address fields in the quote. It doesnot fill the field postofficebox although I have made mappings from account to quote, order and invoice. What do I do wrong? Does the mapping has to be made someore else?? Please help Fred, Mappings won't help with the "Lookup Address" functionality. Where on the Account did you add this field? Is ...

How to get outlook 97 to work with an SMTP/POP email service, not MsMail, PostOffice/Exchange Server?
How do you get outlook 97 to simply work with an ordinary SMTP/POP server? I have Outlook Express working fine, with the settings for account name, pw, etc, already put in and it works great (my O/S is XP Home). When I installed Outlook 97 it wouldn't deliver or receive mail, saying, "No Transport Provider". When I try to add a "Service", it seems to only accept the Microsoft Mail Postoffice as the source/destination for email. There seems to be no way to tell it to use an SMTP/POP protocol service. Tom Download and install the Internet Mail Enhancement Pat...