查询有什么问题

问题描述

嗨

我有以下查询。当我解析查询时，它没有解析。你能告诉我问题在哪里吗？

Hi
I have a below query. When I parse the query, It did not parsed. Could you please tell me where is the issue?

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

我的尝试：



您好

我有以下查询。当我解析查询时，它没有解析。你能告诉我问题在哪里吗？

What I have tried:

Hi
I have a below query. When I parse the query, It did not parsed. Could you please tell me where is the issue?

推荐答案

declare @channel_name nvarchar

因为如果你提供的值大于1个字符，它将被截断为一个字符。

because if you are supplying a value greater than 1 character it will be truncated to a single char.

Code written is not clear, I suggest you use temporary tables or With such as common table to write SQL statements
I hope your structure is clea



<pre lang="SQL">declare @row_number int
declare @acct_no bigint
declare @channel_name nvarchar

SET @row_number=0


WITH a AS
(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
),
b AS
(
select
a.Account_Id,
a.Channel_Name,
a.Last_Refresh_Time
FROM a order by Last_Refresh_Time desc
),
c AS
(
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 b
)
select
 c.Account_Id,
 c.Channel_Name,
 c.Last_Refresh_Time
 FROM c where
c.rnk=1</pre>

这篇关于查询有什么问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！