469,631 Members | 990 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

Summary View Using CASE is only returning first row?

2
Experts,

Please help, I am trying to write a view that enables a list to be produced where other views in the database have a count > 0 ... but when I run this I only get a result returned for the first WHEN statement, the others are not returned, even though they have a count > 0 ?? Help please ... suggestions on approach if I am coming at this the wrong way.
Thanks in Advance
SELECT 'VIEWS TO BE CHECKED' =
CASE
WHEN((SELECT COUNT(1) AS A FROM NN_TblToFunctionalLocationMaster) >= '0') THEN 'NN_TblToFunctionalLocationMaster'
WHEN((SELECT COUNT(1) AS A FROM OI_TblToFunctionalLocationMaster) >= '0') THEN 'OI_TblToFunctionalLocationMaster'
WHEN((SELECT COUNT(1) AS A FROM SD_TblToFunctionalLocationMaster) >= '0') THEN 'SD_TblToFunctionalLocationMaster'
WHEN((SELECT COUNT(1) AS A FROM SS_TblToFunctionalLocationMaster) >= '0') THEN 'SS_TblToFunctionalLocationMaster'
ELSE NULL
END
Jul 11 '07 #1
2 1646
The CASE statement will only execute the first WHEN expression that evaluates to True & generally assumes that the WHEN expressions are mutually exclusive.

Eg

Expand|Select|Wrap|Line Numbers
  1.  
  2. CASE
  3. WHEN (Value < 0) THEN 'Neg'
  4. WHEN (Value = 0) THEN 'Zero'
  5. WHEN (Value > 0) THEN 'Pos'
  6. ELSE 'Undefined'
  7. END
  8.  
  9.  
You are using SELECT COUNT(1) From different tables (NN_TblToFunctionalLocationMaster, OI_TblToFunctionalLocationMaster etc)

You'll need to have a separate CASE statement for each.
Jul 11 '07 #2
KIDDLE
2
Thanks for the reply, ended up going a complete new route to this ... Stored Procedure to return the results and then another to pull out the duplicate, changed and identical data across the two databases.

Cheers
Kiddle
Jul 12 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.