November 26, 2008 at 3:10 am
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
November 26, 2008 at 8:20 am
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]
November 26, 2008 at 8:41 am
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.
November 26, 2008 at 9:12 am
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
November 26, 2008 at 9:19 am
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
March 23, 2009 at 9:13 am
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
March 23, 2009 at 9:25 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy