Using temporary variables in a query
Posted on 2006-10-29
I'm running a commercial app named ProLaw. This application is running on top of a Microsoft SQL Server 2000 database. This application allows a customized report function. The way the report function works is a query is performed, then a report is called which runs against the results of the query.
The report design part of the program allows 'simple' variables to be dragged onto the body of the report from a list of variables in the database. For more complicated items it allows a query to be performed. The function used to invoke a query is select('Real SQL query'). The real SQL query is in standard notation with a few exceptions. I could give much more detail on the report function, but I don't think it is relevant to the question.
The report on which I am working has several 'simple' variable fields and also some fields that require running a query. Several of the queries use the SUM function. Here's what I'm trying to find out if I can do. I'd like to store the results of some of the sum functions to use later on in the report. The reason that I want to do this is that even one of the sum queries is long. If I can't store the result in a temporary variable, then to do arithmetic later on using that sum, I will have to repeat the query, plus the other ones involved in the arithmetic. For instance, to find a total for the report I will have set a field equal to:
select('Query1') + select('Query2') - select('Query3') - select('Query4'), etc. I want to make it clear that I can't set a variable called Query1, but rather where I have this in the example I would have to put the full queries.
The problem is that each of these Queries is (what I consider) very lengthy. I'll give an example below. I want to avoid doing this if possible because editing and debugging these will be very difficult. The report functionality within ProLaw does not directly allow this, but I'd like to know if there is a some functionality within SQL to store the results of these queries to temporary variable that I can use later in the report.
Constraints: Because this is commercial software under a support contract, there are several things that I can't do:
1) I can't make any changes to any of the existing tables or create new ones in the database.
2) I can't use the update function
3) I can't do anything like a stored procedure, etc.
4) There are probably other constraints, but I can't say in advance what they might be.
So in a nutshell what I'm looking for is a way to assign a temporary variable in a query and be able to use it later in the report. In order to use it later I would have to use it inside some type of query so that I can invoke the SQL functionality to be able to retrieve the value of the variable.
Here is an example of one of the queries. This was provided to us by ProLaw support. The actual report will have at least a dozen queries like this. To get the 'grand total' at the end of the report I would have to have a set of queries about 6 times longer than this.
select('select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^ ') + select('select Sum(QTOTALBILLEMS1) as frog2 from matters, mattersqpimedprov, mattersqpimedprov2 where mattersqpimedprov2.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^ ') + select('select Sum(QTOTALBILLHOSP1) as frog3 from matters, mattersqpimedprov, mattersqpimedprov3 where mattersqpimedprov3.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^ ')