HOW TO WRITE THIS QUERY-2

please help me  do this query in Sybase

select  DISTINCT POLICY=mt0.COVERAGE_REF,  
sum(mt0.PB_AMOUNT) AS PB,
sum(mt0.BIC_AMOUNT) AS BIC,
(BIC+PB)  AS TOTAL_COMM ,
from MONEY_TRANSACTION mt0
group by mt0.COVERAGE_REF

but I get following error
Server Message:  Number  207, Severity  16
Line 1:
Invalid column name 'PB'.
Server Message:  Number  207, Severity  16
Line 1:
Invalid column name 'BIC'.

Thanks in advance


PSVAsked:
Who is Participating?
 
Gustavo Perez BuenrostroConnect With a Mentor Commented:
PSV,
Have you tried using a table subquery?

select mt0.POLICY
      ,mt0.PB
      ,mt0.BIC
      ,mt0.PB+mt0.BIC AS TOTAL_COMM
    from
  (select COVERAGE_REF AS POLICY
        ,sum(PB_AMOUNT) AS PB
        ,sum(BIC_AMOUNT) AS BIC
    from MONEY_TRANSACTION
group by COVERAGE_REF) as mt0
0
 
kretzschmarCommented:
hi psv,

didn't have sybase, but try this

select  DISTINCT POLICY=mt0.COVERAGE_REF,  
sum(mt0.PB_AMOUNT) AS PB,
sum(mt0.BIC_AMOUNT) AS BIC,
(sum(mt0.PB_AMOUNT)+sum(mt0.BIC_AMOUNT)) AS TOTAL_COMM ,
from MONEY_TRANSACTION mt0
group by mt0.COVERAGE_REF

meikl
0
 
Gustavo Perez BuenrostroCommented:
PSV,

Check next query and let me know if it works for you.


  select mt0.COVERAGE_REF AS POLICY
        ,sum(mt0.PB_AMOUNT) AS PB
        ,sum(mt0.BIC_AMOUNT) AS BIC
        ,sum(mt0.PB_AMOUNT)
        +sum(mt0.BIC_AMOUNT) AS TOTAL_COMM
    from MONEY_TRANSACTION mt0
group by mt0.COVERAGE_REF
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Gustavo Perez BuenrostroCommented:
Here is an alternative:

  select mt0.COVERAGE_REF AS POLICY
        ,sum(mt0.PB_AMOUNT) AS PB
        ,sum(mt0.BIC_AMOUNT) AS BIC
        ,sum(mt0.PB_AMOUNT+mt0.BIC_AMOUNT) AS TOTAL_COMM
    from MONEY_TRANSACTION mt0
group by mt0.COVERAGE_REF
0
 
PSVAuthor Commented:
Thanks for answer
but  I need  (PB+BIC) AS TOTAL because this query is
just  4% of complete query I have to write and there are lot of complex calculations I have to carry. Otherwise this will make query very difficult to understand.

Other solution for me is to  do programming instead of SQL query.
0
 
wolfgang_93Commented:
Writing a program is certainly a solution. A common mistake is to try to
do everything in a single query when it is much easier to do a query,
saving results into a temporary table, then doing a second query on
the temporary table.

I think people think you are cheating when you do this, but what do you
think Sybase does internally when you ask it to do a complex query?

By doing 2 queries as follows, you will be able to keep things simple
enough to follow and yet efficient as you can hope for in Sybase.

select mt0.COVERAGE_REF AS POLICY
          ,sum(mt0.PB_AMOUNT) AS PB
          ,sum(mt0.BIC_AMOUNT) AS BIC
into #temptable
from MONEY_TRANSACTION mt0
group by mt0.COVERAGE_REF

select PB+BIC as TOTAL from #temptable
0
 
PSVAuthor Commented:
Thanks wolfgang_93 for new idea but this will still not solve my problem.

BTW I can use  TOTAL : (PB+BIC)  using Access Query.
But I have to run this functionality on Sybase server.

Resons of runing query on Sybase Server is SPEED,Memory problem , lack of DateDiff  functionality   in Reporting Tool we are using.

Hope this will make clear whether I am cheating or not.






0
 
kretzschmarCommented:
am i invisible?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.