Solved

HOW TO WRITE THIS QUERY-2

Posted on 2000-04-04
8
312 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Latency in .net app using DB in .net 21 81
SQL Query assistance 16 46
Select specific duplicate row based on specific criteria 4 70
Software for Local History Museum Library 5 71
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

756 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