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 contraints - accessible through TSQL?

Hi all,

I was wondering if there is an easy way to loop through all contraints in a database and programmatically set the cascade delete to ON. I have a database with hundreds of contraints, so individually setting cascade delete on them is not optimal.

Thanks for any info in advance!

I think that the constraints are simply held in one of the system datatables, is there anyway to simply update that table?

You will have to drop and recreate the FK constraints to add the cascase option. Using DDLs is the only supported mechanism to achieve it.|||

Ok. is there a easy way to add and drop all contraints?