Conditional select in view

Greetings all,
I have three databases dmart, dmart_a and dmart_b. The first being a pointer database, has lookup table that one has to query to know which one of the two databases (dmart_a and dmart_b) is online. I want to create a view which can dynamically select the data from the database that is online . Following is the SQL which I was able to write however I am not able to precede any further b'cause of error Sub-query returns more than one row. All your suggestions are welcomed.

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointerAs you have posted a question in the SQL server Article section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by VirDesi

Greetings all,
I have three databases dmart, dmart_a and dmart_b. The first being a pointer database, has lookup table that one has to query to know which one of the two databases (dmart_a and dmart_b) is online. I want to create a view which can dynamically select the data from the database that is online . Following is the SQL which I was able to write however I am not able to precede any further b'cause of error Sub-query returns more than one row. All your suggestions are welcomed.

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointer

try:

select dmart_db_pointer.dbname, cnt_a, cnt_b from
dmart_db_pointer left join
(select 'dmart_a' as dbname, count(*) as cnt _afrom dmart_a.upload.person_data) dmart_a on dmart_a.dbname = dmart_db_pointer.dbname
left join (select 'dmart_b' as dbname, count(*) as cnt_b from dmart_b.upload.person_data) dmart_b on dmart_b.dbname = dmart_db_pointer.dbname