SQL query works in PL/SQL but not in Visual Studio

Question

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 

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.

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...

Answer 1

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 ;

Or, perhaps the select needs to be:

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

(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:

• The application is not connected to the right database.
• The application does not have permissions on the database or table.
• The application query is different from the query run in the database, typically due to some substitution problem.
• The results from running the query in the application are not being interpreted correctly.