SQL查询可在PL/SQL中使用，但不能在Visual Studio中使用 [英] SQL query works in PL/SQL but not in Visual Studio

问题描述

我在网上搜索，发现很多问题都相同，但是没有一个解决方案对我有用. 我真的希望您能为我提供帮助: 我有一个可以在PL/SQL中正常运行的ORACLE SQL查询:

I searched online and found out many had the same problem, but non of the solutions worked for me. I'm really hoping you could help me: I have this ORACLE SQL query that is working fine in PL/SQL:

select a.bzq_terminate_provider, a.callsnum, a.at_call_dur_sec, sum_charge 
From (select * from usage_cycle_sum 
where ban='80072922' and ben='1'
and subscriber_no='036585305'
and start_cycle_code ='20150207'
and feature_code_rank='1') a, (select bzq_terminate_provider,sum(charge_amount) as sum_charge from usage_cycle_sum 
where ban='80072922' and ben='1'
and subscriber_no='036585305'
and start_cycle_code ='20150207' group by bzq_terminate_provider)  b
where  a.bzq_terminate_provider=b.bzq_terminate_provider

我也尝试了另一个运行良好的版本:

I also tried this other version that works fine as well:

    select PROVIDER,sum(CALLS),sum(CHARGE),sum(DUR)
from (
select bzq_terminate_provider PROVIDER,callsnum CALLS,charge_amount CHARGE,at_call_dur_sec DUR
from   usage_cycle_sum 
where  ban='80072922' and ben='1'
  and  subscriber_no='036585305'
  and  start_cycle_code ='20150207'
  and  feature_code_rank='1'
union  
select bzq_terminate_provider PROVIDER,0 CALLS,charge_amount CHARGE,0 DUR
from   usage_cycle_sum 
where  ban='80072922' and ben='1'
  and  subscriber_no='036585305'
  and  start_cycle_code ='20150207'
  and  feature_code_rank='2'
  )
group by PROVIDER 

我的问题是，当我在Visual Studio Web应用程序中创建数据网格时，出现错误:语法错误:期望标识符或带引号的标识符

My problem is that when i create a datagrid in Visual Studio web application, i get an error: syntax error: expecting identifier or quoted identifier

连接正常，我在附加的第二个查询中检查了简单选择查询以及整个并集部分，它们起作用了！ 但是当我使用这两个版本时，会出现此错误.

The connection is ok, i checked the simple select queries as well as the whole union part in the second query i attached, they work! But when i use those two versions, i get this error.

可能是什么问题?还有另一种解决方法吗? 谢谢.

What can be the problem? Is there another way to solve this? Thanks.

编辑21/06/2015 看来Visual Studio不能很好地处理复杂的查询，并且我仍在寻找解决方案，因为我的下一个查询更加复杂...

EDIT 21/06/2015 It seems that visual studio doesn't work well with complex queries and i'm still looking for a solution for this, since my next queries are more complex...

推荐答案

您的第二个查询写得更好:

Your second query is so much nicer to write as:

select bzq_terminate_provider as PROVIDER, sum(callsnum) as CALLS,
       sum(charge_amount) as CHARGE, sum(at_call_dur_sec) as DUR
from usage_cycle_sum 
where ban = '80072922' and ben = '1' and
      subscriber_no = '036585305' and
      start_cycle_code ='20150207' and
      feature_code_rank in ('1', '2')
group by bzq_terminate_provider ;

或者，也许select必须是:

select bzq_terminate_provider as PROVIDER,
       sum(case when feature = '1' then callsnum else 0 end) as CALLS,
       sum(charge_amount) as CHARGE,
       sum(case when feature = '1' then at_call_dur_sec else 0 end) as DUR

(第一个版本假定字段在第二个子查询中被清零，因为它们在数据中为NULL，但这可能不正确.)

(The first version assumed that the fields were zeroed out in the second subquery because they are NULL in the data, but that might not be true.)

但是，应用程序软件还不够智能，无法识别此类笨拙的查询，因此这并不是您要面对的实际问题.如果查询在数据库中有效，但在应用程序中无效，则典型问题是:

However, application software is not yet smart enough to identify such awkwardly written queries, so that is not the actual problem you are facing. If the query works in the database, but not in the application, then typical problems are:

• 该应用程序未连接到正确的数据库.
• 该应用程序没有数据库或表的权限.
• 应用程序查询与数据库中运行的查询不同，这通常是由于某些替换问题造成的.
• 在应用程序中运行查询的结果未正确解释.

这篇关于SQL查询可在PL/SQL中使用，但不能在Visual Studio中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！