Nested SELECT w/ CASE View does not work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested SELECT w/ CASE View does not work

Author

Topic

dsteinmetz
Starting Member

6 Posts

Posted - 2007-06-06 : 10:28:07

SQL Server 2005 SP2
Windows Server 2003

I have a view that does not work when that view is either opened or called from a query. HOWEVER, the view works fine when I test it after opening it to be modified in Management Studio (Query designer). The view contains a nested CASE statement that has a SELECT statement contained within it. If I take out the CASE portion of the main parent select the view works fine. As soon as I re-introduce the CASE statement and try to open the VIEW I get the dreaded "severe error occurred" statement and no rows are returned.

Any help would be appreciated....I've search pretty hard already!

Here is the query
SELECT CD.CLAIM_DATA_PK,
CD.BATCH_FK,
CD.CLIENT_FK,
CD.EFFECTIVE_DATE,
CD.FACILITY_FK,
CD.ACCOUNT_NUMBER,
CD.ACCOUNT_TYPE_FK,
CD.CLAIM_NUMBER,
CD.CLIENT_PAYOR_FK,
CD.CLIENT_FINANCIAL_CLASS_FK,
CD.ELIGIBLE,
CD.NVF,
CD.ADV_PCT,
CD.ENV,
CD.STANDARD_PAYOR_FK,
CD.HFG_FINANCIAL_CLASS_FK,
CD.PRIOR_ELIGIBLE,
CD.INELIGIBLE_AGE,
CD.ADJUSTMENT,
CD.ROLL_FWD,
( CASE WHEN CD.ADMIT_DATE = NULL
THEN ( SELECT MIN(TD.SERVICE_DATE)
FROM dbo.TRANSACTION_DATA TD
WHERE TD.CLIENT_FK = CD.CLIENT_FK
AND TD.EFFECTIVE_DATE = CD.EFFECTIVE_DATE
AND TD.FACILITY_FK = CD.FACILITY_FK
AND TD.CLAIM_NUMBER = CD.CLAIM_NUMBER
)
ELSE CD.ADMIT_DATE
END ) AS ADMIT_DATE
FROM dbo.CLAIM_DATA CD

sshelper
Posting Yak Master

216 Posts

Posted - 2007-06-06 : 10:33:52

One option is to re-write your query and make use of a JOIN:

SELECT CD.CLAIM_DATA_PK,
CD.BATCH_FK,
CD.CLIENT_FK,
CD.EFFECTIVE_DATE,
CD.FACILITY_FK,
CD.ACCOUNT_NUMBER,
CD.ACCOUNT_TYPE_FK,
CD.CLAIM_NUMBER,
CD.CLIENT_PAYOR_FK,
CD.CLIENT_FINANCIAL_CLASS_FK,
CD.ELIGIBLE,
CD.NVF,
CD.ADV_PCT,
CD.ENV,
CD.STANDARD_PAYOR_FK,
CD.HFG_FINANCIAL_CLASS_FK,
CD.PRIOR_ELIGIBLE,
CD.INELIGIBLE_AGE,
CD.ADJUSTMENT,
CD.ROLL_FWD,
ISNULL(CD.ADMIT_DATE, TD.SERVICE_DATE)
FROM dbo.CLAIM_DATA CD LEFT OUTER JOIN (SELECT CLIENT_FK, EFFECTIVE_DATE, FACILITY_FK, CLAIM_NUMBER, MIN(SERVICE_DATE) AS SERVICE_DATE FROM dbo.TRANSACTION_DATA) TD
ON TD.CLIENT_FK = CD.CLIENT_FK
AND TD.EFFECTIVE_DATE = CD.EFFECTIVE_DATE
AND TD.FACILITY_FK = CD.FACILITY_FK
AND TD.CLAIM_NUMBER = CD.CLAIM_NUMBER

SQL Server Helper
http://www.sql-server-helper.com

dsteinmetz
Starting Member

6 Posts

Posted - 2007-06-06 : 10:59:20

I just discovered that the query itself runs fine but as soon as I make it into a VIEW, thats when all of the issues start popping up. It's as if the VIEW cannot handle the execution of the query?

dsteinmetz
Starting Member

6 Posts

Posted - 2007-06-06 : 15:55:33

Issue resolved. There was bad code in the SQL statement (DATE = NULL) should have been DATE IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources