CASE statement advice needed

It appears that this query goes into an endless loop. Can someone please identify the error? The 4 tables referenced (CPADF_DT , CPACCDEF , CPARTY, CPDELACC) have less that 2000 records each.

--CP BANK ACCOUNT DEFAULTS
SELECT
CPARTY.NAME AS "COUNTERPARTY" ,
CPACCDEF.TRANS_TYPE AS "TRANSACTION TYPE",
CPACCDEF.SECTYPE AS "INSTRUMENT TYPE",
CPACCDEF.ENTITY AS "ENTITY",
CPACCDEF.FACILITY AS "RELATIONSHIP",
CPACCDEF.CFLOW_TYPE AS "CASHFLOW TYPE",
CPACCDEF.CCY AS "CURRENCY",
CASE CPACCDEF.PAY_REC
WHEN 'B' THEN 'BOTH'
WHEN 'P' THEN 'PAYMENTS'
WHEN 'R' THEN 'RECEIPTS'
ELSE
CPACCDEF.PAY_REC END AS "PAYMENTS/RECEIPTS",
CPADF_DT.EFFECT_DT AS "EFFECTIVE DATE",
CPADF_DT.ACC_NO AS "ACCOUNT NUMBER / IDENTIFIER",
CPDELACC.ACC_NAME AS "ACCOUNT NAME/PAYMENT METHOD", CPDELACC.CCY AS "CURRENCY",
CPDELACC.BANK_NAME AS "BANK NAME"
FROM CPADF_DT , CPACCDEF , CPARTY, CPDELACCHey first of all the join happening here is cross join and hence it will result in the cartesian product of all the 4 tables and I don't think it is the intended behavior.

there should be some joining condition between the tables.

finally it is recommended to use aliases for the tables in the join so that query looks good :)|||Hi,

I think its not going into an endless loop. The thing is that, since no join condition is provided it will be a cartesian product and if there are lot of rows in each of the tables it will take a lot of time to execute the above query.

The solution to ur problem is that have join conditions so that you filter out unwanted rows.sql

Cascade Delete Not Working Correctly?

I have a master table that is updated. The master has
child records that are referenced by a foreign key with
the CASCADE DELETE option.
In the transaction log, an update of the master appears
as a DELETE/INSERT. The primary key is not being updated.
For the child record, the same update appears to
DELETE/INSERT the child.
Occassionaly, when a master record is updated, the child
record is deleted but not inserted.
Can anyone explain why?
TIA,
HarryIn order to do this you will need to enable Cascade Update as well as
Cascade Delete...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
> I have a master table that is updated. The master has
> child records that are referenced by a foreign key with
> the CASCADE DELETE option.
> In the transaction log, an update of the master appears
> as a DELETE/INSERT. The primary key is not being updated.
> For the child record, the same update appears to
> DELETE/INSERT the child.
> Occassionaly, when a master record is updated, the child
> record is deleted but not inserted.
> Can anyone explain why?
> TIA,
> Harry|||My apologies, I've not made myself clear.
I'm not trying to do this.
Firstly, I'm trying to understand why an update of a
master record results in an delete/insert of the child.
Secondly, why the delete part sometimes fails.
TIA.
>--Original Message--
>In order to do this you will need to enable Cascade
Update as well as
>Cascade Delete...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
>news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
updated.
>
>.
>|||What exactly are you auditing to see this?
I cannot replicate this on a sample db I have...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
> Secondly, why the delete part sometimes fails.
> TIA.
> Update as well as
> message
> updated.|||I'm using the transaction log explorer
tool from Lumigent.
It shows that some updates of the master keep
the child and others do not.
>--Original Message--
>What exactly are you auditing to see this?
>I cannot replicate this on a sample db I have...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
has
with
appears
child
>
>.
>|||"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
Has the table got triggers associated with it? An Update trigger results in
updates being converted to a delete followed by an insert (so the trigger
can reference the before and after values in the INSERTED and DELETED
tables)
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||The only triggers on the table are SQL Server merge
replication triggers.
Interesting point though. I was not aware of that
behaviour.
>--Original Message--
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
>Has the table got triggers associated with it? An Update
trigger results in
>updates being converted to a delete followed by an insert
(so the trigger
>can reference the before and after values in the INSERTED
and DELETED
>tables)
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.614 / Virus Database: 393 - Release Date:
05/03/2004
>
>.
>

capturing cell click event

hi

is there a way i can capture cell click event like may be in report referenced assemblies etc.. so that i can read the cell text in order to open some external applications/windows forms from that..any idea?

regards
farazi'v got an idea to host custom control in the report and then capture the custom control mouse clicnk event.. i'll explore it as well.. but still if someone can give some clue on the original post..|||

If you this will only when the report is exported to HTML and assuming that you use the Report Viewer ASP.NET control, try hooking mouse onclick event in the page body of the hosting page. You should be able to capture the sender using some java script.

The only RS supported feature that you can use to simulate this requirement is assigning a hyperlink to a field using the Navigation tab.

|||hmm.. it seems that we cant do any client side interaction from reports like calling code from client assemblies(from GAC) etc..as its html rendering .. and yeah the only thing left is builtin navigation and i dont think that i can do much from it..actually in simple ways i wanted to show some window from client side assembley on click of specific cells on reports..and i dont see some straight of doing this..

regards
faraz|||

Please consider the option of visualize the reports by means of web service. Once you obtain the report stream in html format (using the render method) you can parse it to attach javascript events you need.

Regards

Maciej Kiewra