Field Names

I want to build a report that from an OLAP data source that uses a named set
that is a rolling 12 month view. The problem is I don't like the field names
that are displayed. For Example,
'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
All I want is August 2003. I know I can edit the field name, but what
happens when the fields change due to the nature of the rolling view? Any
ideas?
--
Michael Hardy
ETL Developer
Visit our web pages:
www.atlantis.com
www.oceanclub.com
www.oneandonlyresorts.com
www.kerzner.comOpuspocus wrote:
> I want to build a report that from an OLAP data source that uses a named set
> that is a rolling 12 month view. The problem is I don't like the field names
> that are displayed. For Example,
> 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> All I want is August 2003. I know I can edit the field name, but what
> happens when the fields change due to the nature of the rolling view? Any
> ideas?
We uses this technique, coudl be more out there...
WITH
Member [Measures].[TimeMemberUniqueName] as
'[Time].currentmember.UniqueName'
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
Time].[2003].[Q4].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
{ [SelectedPeriod]} on 1
FROM XX_YY
In the dynamic version we get the "lag" and actual date from parameters
and code functions. We then get a static field_name
"Measures_TimeDisplayName" to be used in the report.
Regards
// Jonas Montonen|||I'm trying to work out the syntax. I get a 'Query failed: Syntax error in
axis definition' for the following MDX. Can't figure out why.
WITH
Member [Measures].[TimeMemberUniqueName] as
'[ArrivalDate].currentmember.UniqueName',
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[ArrivalDate].[All ArrivalDate].[2003].[3] :
[ArrivalDate].[All ArrivalDate].[2003].[3].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[RsrvtnCount] } ON Axis(0)
{ [SelectedPeriod]} on ROWS
FROM [RsrvtnSummary]
"Jonas Montonen" wrote:
> Opuspocus wrote:
> > I want to build a report that from an OLAP data source that uses a named set
> > that is a rolling 12 month view. The problem is I don't like the field names
> > that are displayed. For Example,
> >
> > 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> > All I want is August 2003. I know I can edit the field name, but what
> > happens when the fields change due to the nature of the rolling view? Any
> > ideas?
> We uses this technique, coudl be more out there...
> WITH
> Member [Measures].[TimeMemberUniqueName] as
> '[Time].currentmember.UniqueName'
> Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
> SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
> Time].[2003].[Q4].Lag(3)'
> SELECT
> { [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
> { [SelectedPeriod]} on 1
> FROM XX_YY
> In the dynamic version we get the "lag" and actual date from parameters
> and code functions. We then get a static field_name
> "Measures_TimeDisplayName" to be used in the report.
> Regards
> // Jonas Montonen
>sql

Field grouping in Report Builder

Hi,

I build all my report with the report builder.

When I build a report that the first column is ID and I've several records with the same ID one after abnother the report is grouping the ID data (see 2 in the example below)

How can I cancel the grouping for report or for the entire environment?

example:

ID Date

1 1/1/1990

2 1/1/1990

1/1/2000

3 1/1/2005

Dear ,

Go to the data tab and remove the grouping.

from

sufian

|||

Hi,

I'm building the report in Report Builder not report designer.

There is no data tab in the report builder.

Anyone?

|||

You need to make sure you have an entity group, not a value group (i.e. grouping on a field). Check the label on the gray "tab" above the fields in the Report Builder design area to see what you are grouping by. If dragging in MyEntity.ID didn't give you an entity group on MyEntity, then you probably need to set MyEntity.DiscourageGrouping=True in your report model. Regardless, you can be sure to get an entity group if you actually drag the entity onto your report instead of a field.

Once you have an entity group, make sure you add any other fields to the same group. Usually they will go in by default, but if you specifically drop them on the far left edge, you will get a new group on that field.

Hope this helps!

Fastest way to build a database out of SQL scripts

What is the fastest way to generate an SQL 2000 database
out of SQL scripts.

The SQL scripts contain the create tables, views, stored
procedures, triggers, constraints, and the tables DATA
records.

What are my options? isql? osql? are there other ways?

Thank youHi

Using the command line utility unless you specify the :r option for the list
of files you are most likely to be creating a connection for each script.
Check out http://tinyurl.com/5299q

You can use a program that uses DMO (or ADO) to open each file and then
execute the commands within them, this can re-use a single connection, and
so be faster.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:pJ%qe.22686$Es6.203309@.wagner.videotron.net.. .
> What is the fastest way to generate an SQL 2000 database
> out of SQL scripts.
> The SQL scripts contain the create tables, views, stored
> procedures, triggers, constraints, and the tables DATA
> records.
> What are my options? isql? osql? are there other ways?
> Thank you|||Hi

Using the command line utility unless you specify the :r option for the list
of files you are most likely to be creating a connection for each script.
Check out http://tinyurl.com/5299q

You can use a program that uses DMO (or ADO) to open each file and then
execute the commands within them, this can re-use a single connection, and
so be faster.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:pJ%qe.22686$Es6.203309@.wagner.videotron.net.. .
> What is the fastest way to generate an SQL 2000 database
> out of SQL scripts.
> The SQL scripts contain the create tables, views, stored
> procedures, triggers, constraints, and the tables DATA
> records.
> What are my options? isql? osql? are there other ways?
> Thank you|||There is a tool called DB Ghost that builds databases from individual
drop/create scripts (schema) and static data insert scripts (data) and
it is, by far, the fastest method to build a database. It beats hand
coded scripts executed via QA or osql by a long way.

DB Ghost can also compare databases, produce a delta upgrade script and
it is possible to execute it via the command line which means that one
tool can give you a fully automated database change management
solution. When you keep the drop/create scripts in a source control
system you also get a full audit trail of changes made.

It's a very powerful way of making changes to SQL Server databases, I
highly recommend you check it out.

Malc|||There is a tool called DB Ghost that builds databases from individual
drop/create scripts (schema) and static data insert scripts (data) and
it is, by far, the fastest method to build a database. It beats hand
coded scripts executed via QA or osql by a long way.

DB Ghost can also compare databases, produce a delta upgrade script and
it is possible to execute it via the command line which means that one
tool can give you a fully automated database change management
solution. When you keep the drop/create scripts in a source control
system you also get a full audit trail of changes made.

It's a very powerful way of making changes to SQL Server databases, I
highly recommend you check it out.

Malc|||> Using the command line utility unless you specify the :r option for the
list
> of files you are most likely to be creating a connection for each script.
> Check out http://tinyurl.com/5299q
> You can use a program that uses DMO (or ADO) to open each file and then
> execute the commands within them, this can re-use a single connection, and
> so be faster.

Thank you John.|||> Using the command line utility unless you specify the :r option for the
list
> of files you are most likely to be creating a connection for each script.
> Check out http://tinyurl.com/5299q
> You can use a program that uses DMO (or ADO) to open each file and then
> execute the commands within them, this can re-use a single connection, and
> so be faster.

Thank you John.

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

Fast question

Hello!
I want to build an application (in visual basic) using the MSDE, and sell it
to thirds. What should i do for make it legally? I have licenses for visual
basic, but as far i know, I can redistribute the MSDE for free. Or I should
sign some agreement, like the Microsoft Agent?
Thank you for your answers and sorry my poor english
Lirn
Liran Marino
__________
Este mensaje se proporciona "TAL COMO ESTA", sin garantias y no otorga
ningun
derecho
__________
(Gua de netiquette del foro)
http://www.uyssoft.com/Netiquette/
__________
Las listas de News son como el Tetris, para la gente que aun recuerda como
leer...
Hi
You need a licence for Visual Basic Development Environment, and MSDE can be
distributed freely with a VB application.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Liran Marino" wrote:

> Hello!
> I want to build an application (in visual basic) using the MSDE, and sell it
> to thirds. What should i do for make it legally? I have licenses for visual
> basic, but as far i know, I can redistribute the MSDE for free. Or I should
> sign some agreement, like the Microsoft Agent?
> Thank you for your answers and sorry my poor english
> Lirán
> --
> Liran Marino
> __________
> Este mensaje se proporciona "TAL COMO ESTA", sin garantias y no otorga
> ningun
> derecho
> __________
> (GuXa de netiquette del foro)
> http://www.uyssoft.com/Netiquette/
> __________
> Las listas de News son como el Tetris, para la gente que aun recuerda como
> leer...
>
>

FAQ: Are there any whitepapers about building a Disaster Recovery site at a remote location for

Hi,

Sorry for the wide distribution.

I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?

I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?

Thanks in advance

here are some articles and white papers that may be helpful to you:

SQL Server 2005 Failover Clustering White paper
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

SQL Server 2000 High Availability Series
https://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/harag01.mspx

SQL Server 2000 Backup and Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

I'm not trying to endorse products and I'm not claiming to know the most about this topic. There are many people that have a lot more insight into the specifics but these are intended to give you some quick and dirty information.

I haven't been able to find anything that specifically addresses the DR for SQL Server 2000 that is all encompassing. In general there are somethings that you could consider that vary in costs as your recovery techniques become more sophisticated.

Some options that are available within SQL 2000 are:

Log shipping: I believe that you send log backups and restore them at the far end without recovery. When a disaster occurs you restore the last log backup with the WITH RECOVERY option and your off and running on your backup server.

Replication: The freshness of the data at the far end will depend on the replication model chosen. Real time can be very demanding if you're planning on replicating every table in every database on your SQL Server. Generally speaking there is less overhead the more specific you could be. There are some functionality constraints that you will have to investigate to see how well they will work for you. For example if you add a column to a table that is being replicated you will have to manually add that column to the subscription for the subscribers to receive it. This is a very simple example.

Some options that are available within SQL 2005

Log shipping: Works similarly to SQL 2000.

Replication: Much improved over SQL 2000. Subscriptions automatically add columns added to tables now. Very slick.

Database Mirroring: Just had this functionality enabled with SP1. There is a lot to consider with this model. There is a primary database server, a secondary or backup database server, and a witness database server. The witnesses function is to control when the failover happens amongst other things. One of the downfalls are incorrect failovers.There are instances of the witness server incorrectly identifying a failed primary server and forcing traffic to the backup. Also I'm not sure how smooth the failback works.

Warm database server just needing to have SQL databases restored. This is a very low cost and not very real time option. This is SQL version independent.

There are several different third party High Availability/ Disaster Recovery products that are out there. Depending on how sophisticated a failover you'll need to have.

Sonasoft - Sonasafe for SQL Server - www.sonasafe.com

Neverfail - Neverfail for SQL Server - www.neverfailgroup.com

This is a very broad topic to try to address I'm sorry if this information hasn't been helpful. If you'd like to compare notes, I'd be glad to discuss some of my findings. I'm going through a very similar experience right now. Except we are a SQL 2005 only shop.

The other thing that you didn't mention is whether or not you're considering clustering. This adds a whole additional level of complexity to the considerations.

Drew Flint

FAQ: Are there any whitepapers about building a Disaster Recovery site at a remote location

Hi,

Sorry for the wide distribution.

I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?

I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?

Thanks in advance

here are some articles and white papers that may be helpful to you:

SQL Server 2005 Failover Clustering White paper
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

SQL Server 2000 High Availability Series
https://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/harag01.mspx

SQL Server 2000 Backup and Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

I'm not trying to endorse products and I'm not claiming to know the most about this topic. There are many people that have a lot more insight into the specifics but these are intended to give you some quick and dirty information.

I haven't been able to find anything that specifically addresses the DR for SQL Server 2000 that is all encompassing. In general there are somethings that you could consider that vary in costs as your recovery techniques become more sophisticated.

Some options that are available within SQL 2000 are:

Log shipping: I believe that you send log backups and restore them at the far end without recovery. When a disaster occurs you restore the last log backup with the WITH RECOVERY option and your off and running on your backup server.

Replication: The freshness of the data at the far end will depend on the replication model chosen. Real time can be very demanding if you're planning on replicating every table in every database on your SQL Server. Generally speaking there is less overhead the more specific you could be. There are some functionality constraints that you will have to investigate to see how well they will work for you. For example if you add a column to a table that is being replicated you will have to manually add that column to the subscription for the subscribers to receive it. This is a very simple example.

Some options that are available within SQL 2005

Log shipping: Works similarly to SQL 2000.

Replication: Much improved over SQL 2000. Subscriptions automatically add columns added to tables now. Very slick.

Database Mirroring: Just had this functionality enabled with SP1. There is a lot to consider with this model. There is a primary database server, a secondary or backup database server, and a witness database server. The witnesses function is to control when the failover happens amongst other things. One of the downfalls are incorrect failovers.There are instances of the witness server incorrectly identifying a failed primary server and forcing traffic to the backup. Also I'm not sure how smooth the failback works.

Warm database server just needing to have SQL databases restored. This is a very low cost and not very real time option. This is SQL version independent.

There are several different third party High Availability/ Disaster Recovery products that are out there. Depending on how sophisticated a failover you'll need to have.

Sonasoft - Sonasafe for SQL Server - www.sonasafe.com

Neverfail - Neverfail for SQL Server - www.neverfailgroup.com

This is a very broad topic to try to address I'm sorry if this information hasn't been helpful. If you'd like to compare notes, I'd be glad to discuss some of my findings. I'm going through a very similar experience right now. Except we are a SQL 2005 only shop.

The other thing that you didn't mention is whether or not you're considering clustering. This adds a whole additional level of complexity to the considerations.

Drew Flint

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
>

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
>

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
>