Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Subtract one queried value from another

    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.
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    will the second query ALWAYS be the subtraction side?

    ie: 1st_query.as_rep - 2nd_query.as_rep

    Try this first and let me know if you get the two rows you wanted:
    PHP Code:
    select cdatetnameas_rep
    from card_table
    where 
       
    (cdate <= '01-Dec-2001' 
        
    and cdate >= '01-Nov-2001'
        
    and tname 'TRSTXN' )
    or (
    cdate <= '01-Dec-2001' 
        
    and cdate >= '01-Nov-2001'
        
    and tname 'TRS554N' ); 
    if this gets the two rows you want, then we have something to work with.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    No, unfortunately this produces 4 rows - 2 for each date.
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select .... (Q1.as_rep - Q2.as_rep)
    FROM CARD_TABLE Q1, CARD_TABLE Q2
    where ...
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2004
    Posts
    19
    Please try self join

    SELECT CDATE ,A.TNAME,(A.AS_REP-B.AS_REP) AS_REP_DIFF FROM CARD_TABLE A,CARD_TABLE B
    WHERE A.CDATE <= '01-Dec-2001'
    AND B.CDATE >= '01-Nov-2001'
    AND A.TNAME = 'TRSTXN'
    AND B.TNAME = 'TRS554N'

    Thanks



    Quote Originally Posted by 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 <a href="http://www.serverlogic3.com/lm/rtl3.asp?si=1&k=card%20table" onmouseover="window.status='CARD_TABLE'; return true;" onmouseout="window.status=''; return true;">CARD_TABLE</a>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •