Solved

HOW TO WRITE THIS QUERY-2

Posted on 2000-04-04
8
284 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:PSV
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 8

Expert Comment

by:wolfgang_93
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
am i invisible?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now