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

Career Path

Hi. Sorry if this is a little off topic. I was wondering if someone could provide a little advice please.

I

currently work in a software development team leader role working in mainly

VB.NET and SQL Server 2000 technologies. Recently I’ve become more interested

in data analysis and decision support systems etc and am thinking in the future

I’d like to specialise in this area.

I was wondering if anyone has any advice

on courses and accreditations that would help me to get into this field. I

don’t have any previous experience of analysis services or similar tools but

have several years of SQL experience including writing applications, reports

and as an administrator. Many thanks in advance.

This DM Review article on BI career considerations may be relevant:

http://www.dmreview.com/article_sub.cfm?articleId=1060142

>>

Career Considerations in the Field of Business Intelligence

Article published in DM Review Magazine
August 2006 Issue

By Nan Weitzman and Jonathan Wu

Never has there been a better time to have a career in business intelligence (BI) than the present. The discipline of making decisions based on relevant, timely and accurate information is now a standard business practice. The technology that we enjoy today supports this discipline while competitive, economic and regulatory factors are forcing individuals to embrace it. As the amount of data continues to accumulate at a rapid pace, organizing it in a meaningful manner is critical to realizing and sustaining any value.

...

>>

|||That's great, I will take a look. Thanks for your help.