CODEWITHSUNDEEP
sqloracle-database
Pallavi
Pallavi

Reputation: 63

Oracle SQL to subtract 2 values from different table joins

I am trying to subtract sequences MN_SEQ from Table C generated based on join with other tables. enter image description here Here is the problem. Query 1 - Select M_Seq from Table C, Table A, Table B where C.date_sk=A.MTH_END_DT and B.Loan_seq=A.Loan_seq

Query 2 -

Select M_Seq from Table C, Table B where C.date_sk=B.ORIG_DT

I have to get difference between 2 M_SEQ generated from the result set of query 1 and Query 2.

Below is what i tried, but I am getting error. 

                                                                        select mn_seq -mn_seq from 
    ((select mn_seq from Table C, Table A, Table B where  B.MTH_END_DT=C.DATE_SK and B.LOAN_SEQ=A.LOAN_SEQ)a,
   (select mn_seq from Table C , Table B where B.ORIG_DT=C.DATE_SK
    )b)

T

Kindly provide inputs . I am not sure if this is the right way to do it. I tried just using "-" between queries but didnt work. Thanks!

Upvotes: 0

Views: 1972

Answers (1)

Shankar
Shankar

Reputation: 879

Try this..

SELECT (SELECT mn_seq
          FROM TABLE c, TABLE a, TABLE b
         WHERE b.mth_end_dt = c.date_sk
           AND b.loan_seq = a.loan_seq) -
       (SELECT mn_seq FROM TABLE c, TABLE b WHERE b.orig_dt = c.date_sk)
  FROM dual

I assume both the mn_seq are NUMBER and also your WHERE clause returns only one record in each of the inner queries.

Upvotes: 1

Related Questions