question

manuu avatar image
manuu asked

The query below does not parse

When I attempt to parse the query below, it does not parse. Could you please tell me where the issue is? declare @row_number int declare @acct_no bigint declare @channel_name nvarchar SET @row_number=0 select c.Account_Id, c.Channel_Name, c.Last_Refresh_Time from ( select @row_number=CASE WHEN @acct_no = b.Account_Id and @channel_name = b.Channel_Name THEN @row_number + 1 ELSE 1 END AS rnk, @acct_no= b.Account_Id as Account_Id, @channel_name= b.Channel_Name as Channel_Name, b.Last_Refresh_Time from ( select a.Account_Id, a.Channel_Name, a.Last_Refresh_Time from ( select a.acct_id as Account_Id, dp.name as Channel_Name, dc.last_call_made_for_access As Last_Refresh_Time from stg.delivered_cap dc inner join stg.ordered_product tp on tp.ord_prod_id=dc.ord_prod_id inner join stg.acct_order_item ai on tp.ord_prod_id = ai.ord_prod_id inner join stg.acct_order ao on ai.order_id = ao.order_id inner join stg.account a on ao.acct_id = a.acct_id left join stg.delivery_point dp on dp.del_point_id = dc.del_point_id left join stg.account_status dcact on dcact.acct_stat_id=dc.acct_status_id where a.bu_id = 49 and dc.last_call_made_for_access is not null ) a order by Last_Refresh_Time desc ) b ) c where c.rnk=1
sql-server-2008parsing
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What was the error upon parsing? It will usually tell you which line is causing issues.
1 Like 1 ·

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
You've got an ORDER BY clause in an inline / subquery without a TOP / OFFSET / FOR XML. I'm a bit suspicious of the mixture of setting local variables while generating resultsets, eg: .... SELECT ... @acct_no = b.Account_Id as Account_Id .... but that could just be me being overly cautious.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry, which bit is causing you problems? There's only one `ORDER BY`, and there are multiple `@`s to search for...
1 Like 1 ·
Hi Thomas, Thanks for your reply. Could you please paste the full query as I am not able to find the issue. Thanks in advance
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.