Solved

HOW TO WRITE THIS QUERY-2

Posted on 2000-04-04
8
316 Views
Last Modified: 2012-08-14
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


0
Comment
Question by:PSV
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2686361
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2687276
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2687292
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PSV
ID: 2687949
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
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 2688154
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
 

Author Comment

by:PSV
ID: 2688217
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
 
LVL 4

Accepted Solution

by:
Gustavo Perez Buenrostro earned 200 total points
ID: 2688327
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2688778
am i invisible?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamic Table mySQL stored procedure 5 63
MS SQL Server Management Studio R2 4 60
comparing two rows 10 40
What are the recommended security measures to put in place? 19 92
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question