I'm fairly new to the DBA thing.
I've been doing quite a bit of research on monitoring MSSQL performance.
What tools are you guys using?
PerfMon, Dbartisan, Spotlight, or what?
I'd like to find some free or affordable way to watch our Servers to check health/performance/etc.If you're using SQL 2005 (SP2), you can use a combination of SQL Health and History (aka SQL H2) and the new SQL Dashboard. Both require SQL Reporting services.
I'm using a set of vb scripts that use WMI queries to gather basic information: storage, network config, os config, installed apps, memory and cpu utilization. These are stored in a database. You can do a google on "wmi computer inventory vb script" and I think it will come up with a variety of examples.
Regards,
hmscott|||"whatsup" has a lot of robust automation for wmi if you're looking for a prepackaged solution. We've had reasonable luck with it so far.
Showing posts with label fairly. Show all posts
Showing posts with label fairly. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Fast SQL, slow storedproc?
I have a storedproc that takes "forever" to run. However, the SQL inside is
fairly simple, consisting of a series of SELECT...INTOs that build up a table
only 2500 rows long. There's only one time consuming query, and when I run
that by hand it only takes eight seconds, the other seven inside finish in
less than a second. That jives with what I think should happen, it should
take maybe 15 seconds to run, but instead times out after 2 minutes.
Can anyone offer some suggestions here?
MauryDo you use sp_executesql somewhere? if yes, then you've run into the same
problem I posted a few minutes ago...
"Maury Markowitz" wrote:
> I have a storedproc that takes "forever" to run. However, the SQL inside is
> fairly simple, consisting of a series of SELECT...INTOs that build up a table
> only 2500 rows long. There's only one time consuming query, and when I run
> that by hand it only takes eight seconds, the other seven inside finish in
> less than a second. That jives with what I think should happen, it should
> take maybe 15 seconds to run, but instead times out after 2 minutes.
> Can anyone offer some suggestions here?
> Maury|||"Jochen Wezel" wrote:
> Do you use sp_executesql somewhere? if yes, then you've run into the same
> problem I posted a few minutes ago...
I'm not sure what that is, but the SP has nothing but selects in it (with
two parameters) and I call it thus:
exec pGenerateHPLPriceList '8/24/07', 13
Maury|||Ahh...
Taking a hint from your other thread, I googled up "recompile". Try this...
exec sp_recompile yourprocnamehere
Mine went from 3:09 to 0.06. Might want to try it :-)
Maury|||Okay, then it might be another problem. Sorry.
"Maury Markowitz" wrote:
> "Jochen Wezel" wrote:
> > Do you use sp_executesql somewhere? if yes, then you've run into the same
> > problem I posted a few minutes ago...
> I'm not sure what that is, but the SP has nothing but selects in it (with
> two parameters) and I call it thus:
> exec pGenerateHPLPriceList '8/24/07', 13
> Maury
fairly simple, consisting of a series of SELECT...INTOs that build up a table
only 2500 rows long. There's only one time consuming query, and when I run
that by hand it only takes eight seconds, the other seven inside finish in
less than a second. That jives with what I think should happen, it should
take maybe 15 seconds to run, but instead times out after 2 minutes.
Can anyone offer some suggestions here?
MauryDo you use sp_executesql somewhere? if yes, then you've run into the same
problem I posted a few minutes ago...
"Maury Markowitz" wrote:
> I have a storedproc that takes "forever" to run. However, the SQL inside is
> fairly simple, consisting of a series of SELECT...INTOs that build up a table
> only 2500 rows long. There's only one time consuming query, and when I run
> that by hand it only takes eight seconds, the other seven inside finish in
> less than a second. That jives with what I think should happen, it should
> take maybe 15 seconds to run, but instead times out after 2 minutes.
> Can anyone offer some suggestions here?
> Maury|||"Jochen Wezel" wrote:
> Do you use sp_executesql somewhere? if yes, then you've run into the same
> problem I posted a few minutes ago...
I'm not sure what that is, but the SP has nothing but selects in it (with
two parameters) and I call it thus:
exec pGenerateHPLPriceList '8/24/07', 13
Maury|||Ahh...
Taking a hint from your other thread, I googled up "recompile". Try this...
exec sp_recompile yourprocnamehere
Mine went from 3:09 to 0.06. Might want to try it :-)
Maury|||Okay, then it might be another problem. Sorry.
"Maury Markowitz" wrote:
> "Jochen Wezel" wrote:
> > Do you use sp_executesql somewhere? if yes, then you've run into the same
> > problem I posted a few minutes ago...
> I'm not sure what that is, but the SP has nothing but selects in it (with
> two parameters) and I call it thus:
> exec pGenerateHPLPriceList '8/24/07', 13
> Maury
Sunday, February 26, 2012
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, Thomas
Hi 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
>
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
Hi 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
>
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
>
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
>
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
>
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
>
Subscribe to:
Posts (Atom)