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

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
Susan
It can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith
|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
--
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the firs
t
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the fir
st
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capture new and modified data

Hi,
I have two tables, 1 is yesterday data and 1 is today data.
My job is to identify new and modified data only.
I am thinking of compare data, field by field in the where clause to
identify change.
Is this the best way to do it?
Thanks,
CulamIt would seriously help if you had a column that stores the date/time the
row were inserted or updated.
"culam" <culam@.discussions.microsoft.com> wrote in message
news:5DC1BEE5-145A-48F8-B3A2-893E05651B9D@.microsoft.com...
> Hi,
> I have two tables, 1 is yesterday data and 1 is today data.
> My job is to identify new and modified data only.
> I am thinking of compare data, field by field in the where clause to
> identify change.
> Is this the best way to do it?
> Thanks,
> Culam|||Use BINARY_CHECKSUM to compare data and see what changed.