Or how about doing it as an inner join - generally more efficient
Select
TP.acctNo,
TP.trxAmt AS posAmt,
TP.trxID AS posTrxID,
TN.trxID AS negTrxID,
TN.trxAmt AS negAmt,
FROM transactions TP INNER JOIN transactions TN
ON TP.trxType = TN.trxType
AND TP.acctNo = TN.acctNo
WHERE TP.trxAmt = 0 - TN.trxAmt
It's also generally considered better coding practice to use JOIN
statements where you can, as it lets you separate the join conditions
from the selection criteria. This helps make the query more readable,
and unltimately more maintainable.
Just my two-penn'orth ;)
Alistair Davidson
Application Developer
Freeserve.com PLC
-----Original Message-----
From: Matthew Walker [mailto:[EMAIL PROTECTED]
Sent: 28 August 2003 06:19
To: CF-Talk
Subject: RE: sql question
How about this?? Even if it does work, there could be a case where two
reversals have the same dollar amount. This could cause confusion.
Select *
FROM transactions AS transactionsP, transactions AS transactionsN
WHERE transactionsP.acctNo = transactionsN.acctNo
AND transactionsP.trxType in ('d','r')
AND transactionsP.trxType = transactionsN.trxType
AND transactionsP.trxAmt > 0
AND transactionsP.trxAmt = -transactionsN.trxAmt
> -----Original Message-----
> From: Tony Weeg [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 28 August 2003 3:54 p.m.
> To: CF-Talk
> Subject: sql question
>
> testy this one is i tell ya, testy...
>
> ok. i have a table, with 4 columns:
>
> 1. acctNo
> 2. trxAmt
> 3. trxType
> 4. trxId
>
> where trxType might be [d] or [r]
> and for every [r] there is a matching
> [d]. i want to get acctNo's that match
> but only when the trxAmt are opposing
> value matches...
>
> could be...
>
> acctNo: 100
> trxAmt: -545.00
> trxType: r
> trxId: 500
>
> and the matching one would look like
>
> acctNo: 100
> trxAmt: 545.00
> trxType: r
> trxId: 510
>
> basically a reversal of a mistaken transaction....
> now, the problem lies in the fact that there may
> be another transaction that matches the acctNo
> but has nothing to do with this payment reversal.
>
> can this be done?
>
> here is what i have, and its giving me ones that
> match the acctNo, but are extra, since they are not
> matching in the dollar value...
>
> select acctNo, trxAmt from transactions
> where (trxtype = 'd' or trxType = 'r')
> and acctNo in (select acctNo from colthist where trxAmt like '%-%')
> group by trxAmt, acctNo
>
> here is a sample set, the first one that pulls up, and clearly
> illustrates
> the problemo ;)
>
> 127 -154.50 #1
> 127 154.50 #2
> 127 618.00 #3
> 133 -245.00 #4
> 133 -196.00 #5
> 133 -98.00 #6
> 133 25.00 #7
> 133 50.00 #8
> 133 85.11 #9
> 133 98.00 #10
> 133 196.00 #11
> 133 245.00 #12
> 133 500.00 #13
>
> where 1 and 2 are correct but three isnt, and 4,5,6 match 10,11 and
12.
> but, i dont want 3,7,8,9 or 13 to be there.
>
> confused, i hope not, but i hope this is possible in a query...
>
> ....tony
>
> tony weeg
> [EMAIL PROTECTED]
> www.revolutionwebdesign.com
> rEvOlUtIoN wEb DeSiGn
> 410.334.6331
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
