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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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
Gustavo Perez BuenrostroCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kretzschmarCommented:
am i invisible?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.