473,396 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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_pointer
Jul 24 '07 #1
2 5538
debasisdas
8,127 Expert 4TB
As you have posted a question in the SQL server Article section it is being moved to SQL Server Forum.

MODERATOR.
Jul 25 '07 #2
ck9663
2,878 Expert 2GB
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
Jul 25 '07 #3

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

Similar topics

7
by: Guy Hocking | last post by:
Hi there, I have a problem in my ASP/SQL Server application i am developing, i hope you guys can help. I have a ASP form with list boxes populated by SQL tables. When a user selects a value...
1
by: Fran?ois Bourdages | last post by:
Hi is there a way to know if object (view, function, etc) are invalid ? let say a have a table t1 (field col1, col2) and a view v1 (field t1.col1, t1.col2) if I drop t1.col2, the view v1 is not...
2
by: Martin | last post by:
Dear Group I'm having trouble with the statement below. I tried CASE and IF without success. What I'm trying to do: There is a field in the database called Business_TelNo. If the field has some...
3
by: DraguVaso | last post by:
Hi, When you double click on a form in the Solution Explorer, it will show that form in the Designe Mode. Is there a way to change this, so it will switcvh to the 'Code Mode'? Is this...
6
by: simon.harris | last post by:
I have a query in an Access2000 db (created using the QBE as it happens!) that the resulting SQL looks like this; SELECT CLIENTS., Logfile., Logfile., SASTESTS.Cost, SASTESTS.*CLIENTS. AS...
0
by: VirDesi | last post by:
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...
3
by: mckbill | last post by:
Is there a way I can direct the cursor to a specific field (variable) in a form by typing the field name while in form view? I have a form with many fields, and it would be nice if there were...
4
by: smanojgroup | last post by:
I have a view defined as select * on base table. When base table is altered to add new column, new column does not appear when selected from view. Here is what I did to test. db2 "create table...
9
by: mbedford | last post by:
Structure: Table: tblAssets Fields: ID, UID, ModelID, NetName I'm trying to populate a query which I'll then use for the source of a Combo Box in a form. The query will return two fields from...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.