Subtracting one queried value from another

R

robojam

Member
Joined
Jan 16, 2003
Messages
11
Im looking for the fastest way to run a query that gets two different values from an Oracle table, and subtracts one from the other. The first is determined by querying a field that identifies one type of data, and the second by querying the same field for another type of data. The two individual queries are:

SELECT CDATE, TNAME, AS_REP
FROM CARD_TABLE
WHERE CDATE <= 01-Dec-2001
AND CDATE >= 01-Nov-2001
AND TNAME = TRSTXN

and

SELECT CDATE, TNAME, AS_REP
FROM CARD_TABLE
WHERE CDATE <= 01-Dec-2001
AND CDATE >= 01-Nov-2001
AND TNAME = TRS554N

These two queries will each return two rows, as there is one value on the first of each month. What I need is a query that would return two rows, but would give me the value of AS_REP from the first query minus AS_REP from the second query.

I have been trying to write subqueries, but I cant seem to get it right.
 
Maybe you could use a temporary table with 2 columns, one for each value of AS_REP and then substract them.

I dont know how to create temp tables in oracle, but it shouldt be difficult.
Youll have 2 queries like:

insert into ##TempTbl (AS_REP1)
select AS_REP
FROM CARD_TABLE
WHERE CDATE <= 01-Dec-2001
AND CDATE >= 01-Nov-2001
AND TNAME = TRSTXN

and the second:

insert into ##TempTbl (AS_REP2)
select AS_REP
FROM CARD_TABLE
WHERE CDATE <= 01-Dec-2001
AND CDATE >= 01-Nov-2001
AND TNAME = TRS554N

select @Result = AS_REP1 - AS_REP2 from ##TempTbl
 
Back
Top