Fairly complex SQL

Hi all,
i have a fairly complex SQL statement i should build. to make things
easier i wrote some pseudocode that should clarify what the SQL does:
setup:
Table: TBMLCASHFLOW
Important attributes:
- CFID (the ID of the cashflow),
- BDCONTEXTID (the ID of linked cash flows: cash flows that belong
together in a transaction (e.g. security deal and cash deal for a
security transaction) have the same BDCONTEXTID, so they belong to the
same business deal)
- TRANSACTIONAMT (amount of the transaction, important for sorting
purposes).
- a few other items that need to be fetched by the SQL
What the SQL should do:
//search all cash flows in TBMLCASHFLOW matching search criteria
defined in an input GUI
SELECT *
FROM TBMLCASHFLOW
WHERE EXTERNALID = :EXTID, TRADEDATE BETWEEN :TRADEDATEFROM AND
:TRADEDATETO, RETURNTYPECODE = :RETURNTYPECODE
//For every cash flow that is found in the select query, do:
IF TBMLCASHFLOW.BDCONTEXTID <> NULL
MOVE BDContextID to BDContextID_ORG
search TBMLCASHFLOW for BDContextID = BDContextID_ORG and join all
found cash flows together in a logical cash flow group.
These connected cash flows have to be shown one below the other in the
GUI.
The first found cash flow is shown at the top of the group, all linked
cash flows found in the second sql are displayed below it in descending
TRANSACTIONAMT value.
The whole thing has to be coded in cobol, but any other language would
do it if anyone knows an efficitent way to code this. What should be
done, if possible, is that the SQL groups and sorts the result as
specified above, so that we can return a list of values directly to the
GUI without having to sort it again.
If you have any further question or something is not very clear, let me
know.
Thanx in advance for your support, appreciate it!
Regards, ThomasHi Thomas
I am not sure about the Cobol but to return a value from a select statement
you could either user CASE or possibly in this case ISNULL or COALESCE
SELECT t.CFID,
COALESCE ( k.TRANSACTIONAMT, t.TRANSACTIONAMT ) AS TRANSACTIONAMT
FROM TBMLCASHFLOW t
LEFT JOIN TBMLCASHFLOW k ON t.BDCONTEXTID = k.CFID
WHERE t.EXTERNALID = @.EXTID
AND t.TRADEDATE BETWEEN @.TRADEDATEFROM AND @.TRADEDATETO
This would return the linked cashflow amount if it exists otherwise the
original cashflow amount.
HTH
John
"thomas.naegeli@.gmail.com" wrote:

> Hi all,
> i have a fairly complex SQL statement i should build. to make things
> easier i wrote some pseudocode that should clarify what the SQL does:
> setup:
> Table: TBMLCASHFLOW
> Important attributes:
> - CFID (the ID of the cashflow),
> - BDCONTEXTID (the ID of linked cash flows: cash flows that belong
> together in a transaction (e.g. security deal and cash deal for a
> security transaction) have the same BDCONTEXTID, so they belong to the
> same business deal)
> - TRANSACTIONAMT (amount of the transaction, important for sorting
> purposes).
> - a few other items that need to be fetched by the SQL
> What the SQL should do:
> //search all cash flows in TBMLCASHFLOW matching search criteria
> defined in an input GUI
> SELECT *
> FROM TBMLCASHFLOW
> WHERE EXTERNALID = :EXTID, TRADEDATE BETWEEN :TRADEDATEFROM AND
> :TRADEDATETO, RETURNTYPECODE = :RETURNTYPECODE
> //For every cash flow that is found in the select query, do:
> IF TBMLCASHFLOW.BDCONTEXTID <> NULL
> MOVE BDContextID to BDContextID_ORG
> search TBMLCASHFLOW for BDContextID = BDContextID_ORG and join all
> found cash flows together in a logical cash flow group.
> These connected cash flows have to be shown one below the other in the
> GUI.
> The first found cash flow is shown at the top of the group, all linked
> cash flows found in the second sql are displayed below it in descending
> TRANSACTIONAMT value.
> The whole thing has to be coded in cobol, but any other language would
> do it if anyone knows an efficitent way to code this. What should be
> done, if possible, is that the SQL groups and sorts the result as
> specified above, so that we can return a list of values directly to the
> GUI without having to sort it again.
> If you have any further question or something is not very clear, let me
> know.
> Thanx in advance for your support, appreciate it!
> Regards, Thomas
>