How to solve this complex query

[mrkamran]

Hello,
I m working on accounts software and i have a problem this query

SELECT V_MAST.VOUCHER_DATE, V_DTL.DTL_CODE, V_DTL.VOUCHERNO, V_DTL.VOUCHERTP, V_DTL.DEBIT, V_DTL.CREDIT
FROM V_MAST, V_DTL
WHERE V_MAST.VOUCHERNO = V_DTL.VOUCHERNO
AND V_MAST.VOUCHERTP = V_DTL.VOUCHERTP
AND V_MAST.VOUCHER_DATE BETWEEN '01-JUL-2006' AND '10-JUL-2006'
AND V_DTL.DTL_CODE = 917001

This query will select all those vouchers in which DTL_CODE = 917001 but i want to select its reciprocal entries i.e I require those entries which code is not 917001 but its voucher # same as 917001 Voucher No
What should i do

Thanks!

 

[IanWaterman]

You could try (if I have guessed the correct fields

SELECT V_MAST.VOUCHER_DATE, V_DTL.DTL_CODE, V_DTL.VOUCHERNO, V_DTL.VOUCHERTP, V_DTL.DEBIT, V_DTL.CREDIT
FROM V_MAST, V_DTL
WHERE V_MAST.VOUCHERNO = V_DTL.VOUCHERNO
AND V_MAST.VOUCHERTP = V_DTL.VOUCHERTP
AND V_MAST.VOUCHER_DATE BETWEEN '01-JUL-2006' AND '10-JUL-2006'
AND ((V_DTL.DTL_CODE = 917001) or (V_DTL.DTL_CODE <> 917001 and V_DTL.VOUCHERNO = 917001))


Ian

 

[Thargy]

mrkamran,

assuming your posted query does precisely what it's supposed to, how about

SELECT V_MAST.VOUCHER_DATE, V_DTL.DTL_CODE, V_DTL.VOUCHERNO, V_DTL.VOUCHERTP, V_DTL.DEBIT, V_DTL.CREDIT
FROM V_MAST, V_DTL
WHERE V_MAST.VOUCHERNO = V_DTL.VOUCHERNO
AND V_MAST.VOUCHERTP = V_DTL.VOUCHERTP
AND V_MAST.VOUCHER_DATE BETWEEN '01-JUL-2006' AND '10-JUL-2006'
MINUS
SELECT V_MAST.VOUCHER_DATE, V_DTL.DTL_CODE, V_DTL.VOUCHERNO, V_DTL.VOUCHERTP, V_DTL.DEBIT, V_DTL.CREDIT
FROM V_MAST, V_DTL
WHERE V_MAST.VOUCHERNO = V_DTL.VOUCHERNO
AND V_MAST.VOUCHERTP = V_DTL.VOUCHERTP
AND V_MAST.VOUCHER_DATE BETWEEN '01-JUL-2006' AND '10-JUL-2006'
AND V_DTL.DTL_CODE = 917001

The much underrated minus operator is a good un'

Regards

T

Grinding away at things Oracular