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

Cascading Blocking ?

We have a situation that occurs every so often with blocking of
various databases on one server (Win200 SQL7). It appears to happen at
random, so I'm assuming it originates from something a user does and
not a regularily run process.

We've examined the data available to us and used the very helpful
blocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
(Thanks Erland).

Were getting closer to finding the problem, but need some advice on
what to look for.

This is a bit of guesswork, but we suspect that we get into a
situation where blocking takes places, and this then cascades to other
processes which then block others in turn. The original culprit then
finishes, but the blocks continue as the newer processes are holding
something else up. A bit like dominoes. It seems to take a while to
free this up.

The problem we have is determining the start of this process. Once we
are made aware of blocking issues, we can find out who is doing what,
but almost always get a different answer/user and think we're getting
to it a little late.

Ideally, I want to log the blocking somewhere so I can examine the
files when this occurs and can therefore establish a pattern etc...

Any ideas or suggestions would be welcome.Take a look at
http://support.microsoft.com/defaul...kb;EN-US;251004.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0310170116.c4b64ed@.posting.google.co m...
> We have a situation that occurs every so often with blocking of
> various databases on one server (Win200 SQL7). It appears to happen at
> random, so I'm assuming it originates from something a user does and
> not a regularily run process.
> We've examined the data available to us and used the very helpful
> blocking code on
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
> (Thanks Erland).
> Were getting closer to finding the problem, but need some advice on
> what to look for.
> This is a bit of guesswork, but we suspect that we get into a
> situation where blocking takes places, and this then cascades to other
> processes which then block others in turn. The original culprit then
> finishes, but the blocks continue as the newer processes are holding
> something else up. A bit like dominoes. It seems to take a while to
> free this up.
> The problem we have is determining the start of this process. Once we
> are made aware of blocking issues, we can find out who is doing what,
> but almost always get a different answer/user and think we're getting
> to it a little late.
> Ideally, I want to log the blocking somewhere so I can examine the
> files when this occurs and can therefore establish a pattern etc...
> Any ideas or suggestions would be welcome.