查询有什么问题 [英] What wrong with the query

问题描述

嗨

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

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?

推荐答案

感觉这不会解决你的问题虽然但是b $



对我来说最突出的是

Have a feeling that this wont fix your problem though BUT

On thing that stands out for me is the declaration of

declare @channel_name nvarchar

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

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

似乎有一些问题有了它，但一个主要的一点就是你不能使用单个select语句来为变量赋值以及检索数据。您填写@row_number和@acct_no等的行，您也只需选择b.Last_Refresh_Time。你不能这样做。

There appears to be a few issues with it, but one main one is that you cannot use a single select statement to both assign values to a variable as well as retrieve data. The line where you fill in @row_number and @acct_no, etc, you also just select b.Last_Refresh_Time. You can't do that.

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>

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