Subtracting one queried value from another

[robojam]

I'm 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 can't seem to get it right.

[Puiu]

Maybe you could use a temporary table with 2 columns, one for each value of AS_REP and then substract them.

 

I don't know how to create temp tables in oracle, but it should't be difficult.

You'll 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