Cross Apply is not workign on SQL Server compatibility level 2000(80)

Question

Cross Apply is not workign on SQL Server compatibility level 2000(80)

vaseem-773819

Ten Centuries

Points: 1015
More actions
November 26, 2008 at 3:10 am

#126077

I need to fire a SQl having cross apply, My db is on compatibility level 2000(80) which I cannot change due to some restriction.
Is there any way so that i can run the same cross apply without changing the compatibility level into 2005(90).
Please help.
vaseem

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You cannot use CROSS APPLY, but the functionality you get from a CROSS APPLY can always be achieved without using it.

If you can post the query with the CROSS APPLY, the DDL for the tables, and some sample data, I can help you rewrite it.

Here are some tips on posting:

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

The compatibility level affects which keywords are working, so you can't do this without changing the level. However you can use T-SQL, as mentioned above, to achieve the same type of results. Might not be as efficient, but it can be done.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why can't you change the compatibility level?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vaseem-773819 Ten Centuries Points: 1015 More actions · Answer 4

Thanks

I have a table like

tblProducts

Id Voltage

1 230

2 200/250

3 230/180

4 250

I want all voltage value in separate row like bellow

Voltage

230

200

250

230

180

250

for this I have used sql like

Select voltage from tblProducts tp cross apply dbo.fn_split(tp.voltage,'/')

fn_split is a table valued function which give "/" separated value in form of table

it solved my problem but unfortunately where I want to use this sql there compatibility level is 2000(80) and there is restriction to change the compatibility level.

How can i get the same result without using cross apply ie compatibility leve 2000(80)

Thanks

Vaseem

vwilliams SSC-Addicted Points: 435 More actions · Answer 5

I am having the same problem. I cannot change the compatibility level on the database because of older applications. I need to simulate the cross apply and probably the PIVOT in my code. Is it possible?

Here is my code:

SELECT , version, paynet_id, payment_comprehensive, payment_summary, business_backround,

trade_summary, trade_detail, public_filings, financial_relationships, ucc_filings, transpo_score,

master_score, legal_name, agri_score, construction_score

FROM

(

SELECT phra_subid,SELECT substring(f.Val, 0, (PATINDEX('%=%', f.Val))) AS Col,substring(f.Val, (PATINDEX('%=%', f.Val)+1), len(f.Val)-(PATINDEX('%=%', f.Val)+0)) AS Val

FROM from dbo.phr_audit a

CROSS APPLY dbo.ParseValues(a.phra_request_querystring,'&') f

where (@subid = -1 or phra_subid = @subid)

and (phra_auditdt between @from and @thru)

and (@reqUrl = 'ALL' or phra_request_url = @reqURL)

and phra_request_url <> '/phr_generate.asp'

)m

PIVOT

(

MAX(Val) FOR Col IN (user, version, paynet_id, payment_comprehensive, payment_summary, business_backround,

trade_summary, trade_detail, public_filings, financial_relationships', ucc_filings, transpo_score,

master_score, legal_name, agri_score, construction_score)

)p

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

vwilliams (3/23/2009)
I am having the same problem. I cannot change the compatibility level on the database because of older applications.

Also posted here - http://www.sqlservercentral.com/Forums/Topic681532-145-1.aspx

No replies to this thread please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass