January 26, 2009 at 12:36 pm
Hi All,
How are you doing all? I have created 3 Views and each of one is BO, CT, and NH and add where clause in each view. For example,
SELECT COUNT(DEClearedDate) AS DECleared, COUNT(SomaticMCClearedDate) AS SomaticMCCleared, COUNT(PsycMCClearedDate) AS PsycMCCleared, COUNT(DESecondClearedDate) AS DESecondCleared, COUNT(SomaticMCSecondClearedDate) AS SomaticMCSecondCleared,
COUNT(PsycMCSecondClearedDate) AS PsycMCSecondCleared, COUNT(DEThirdClearedDate) AS DEThirdCleared, COUNT(SomaticMCThirdClearedDate) AS SomaticMCThirdCleared, COUNT(PsycMCThirdClearedDate) AS PsycMCThirdCleared,COUNT(DEFourthClearedDate) AS DEFourthCleared, COUNT (SomaticMCClearedDate) AS SomaticMCFourthCleared, COUNT(PsycMCFourthClearedDate) AS PsycMCFourthCleared, CONVERT(varchar(16), DEClearedDate, 101) AS DEClearedDate, CONVERT(varchar(16), SomaticMCClearedDate, 101) AS SomaticMCClearedDate, CONVERT(varchar(16), PsycMCClearedDate, 101) AS PsycMCClearedDate, CONVERT(varchar(16), DESecondClearedDate, 101) AS DESecondClearedDate, CONVERT(varchar(16), SomaticMCSecondClearedDate, 101) AS SomaticMCSecondClearedDate, CONVERT(varchar(16), PsycMCSecondClearedDate, 101) AS PsycMCSecondClearedDate, CONVERT(varchar(16), DEThirdClearedDate, 101) AS DEThirdClearedDate, CONVERT(varchar(16), SomaticMCThirdClearedDate, 101) AS SomaticMCThirdClearedDate, CONVERT(varchar(16), PsycMCThirdClearedDate, 101) AS PsycMCThirdClearedDate, CONVERT(varchar(16), DEFourthClearedDate, 101) AS DEFourthClearedDate, CONVERT(varchar(16), SomaticMCFourthClearedDate, 101) AS SomaticMCFourthClearedDate, CONVERT(varchar(16), PsycMCFourthClearedDate, 101) AS PsycMCFourthClearedDate
FROM dbo.ROCAPData
WHERE (DDS = ('BO')
GROUP BY DEClearedDate, SomaticMCClearedDate, PsycMCClearedDate, DESecondClearedDate, SomaticMCSecondClearedDate, PsycMCSecondClearedDate,
DEThirdClearedDate, SomaticMCThirdClearedDate, PsycMCThirdClearedDate, DEFourthClearedDate, SomaticMCFourthClearedDate, PsycMCFourthClearedDate
The reason I created the where clause to be able pull out the specific data from each region such as BO or NH OR CT. But if I wish to combined all of them into one such as BO + NH + CT like in Access Database then It would not allow me to write
Where (DDS = ('BO'+ 'NH' +'CT' = 'ALL'))
The reason I wish to combined all of them into one because if I wish to look at the summary report of all 3 regions then I have to create drop down box on the front end (ASP.NET 2.0)
my drop down box will contain BO, NH, CT, and ALL.
But as soon as I wrote where clause like BO + NH +CT = all then it gives me a syntax error.
Do anyone out there know how to fix this problem?
I'm new to this SQL
Thanks in advances.
JOE
January 26, 2009 at 12:40 pm
This is how you do a where on more than 1 column, if you want it all, just add "OR" clauses.
Here:
SELECT COUNT(DEClearedDate) AS DECleared,
COUNT(SomaticMCClearedDate) AS SomaticMCCleared,
COUNT(PsycMCClearedDate) AS PsycMCCleared,
COUNT(DESecondClearedDate) AS DESecondCleared,
COUNT(SomaticMCSecondClearedDate) AS SomaticMCSecondCleared,
COUNT(PsycMCSecondClearedDate) AS PsycMCSecondCleared,
COUNT(DEThirdClearedDate) AS DEThirdCleared,
COUNT(SomaticMCThirdClearedDate) AS SomaticMCThirdCleared,
COUNT(PsycMCThirdClearedDate) AS PsycMCThirdCleared,
COUNT(DEFourthClearedDate) AS DEFourthCleared,
COUNT(SomaticMCClearedDate) AS SomaticMCFourthCleared,
COUNT(PsycMCFourthClearedDate) AS PsycMCFourthCleared,
CONVERT(VARCHAR(16),DEClearedDate,101) AS DEClearedDate,
CONVERT(VARCHAR(16),SomaticMCClearedDate,101) AS SomaticMCClearedDate,
CONVERT(VARCHAR(16),PsycMCClearedDate,101) AS PsycMCClearedDate,
CONVERT(VARCHAR(16),DESecondClearedDate,101) AS DESecondClearedDate,
CONVERT(VARCHAR(16),SomaticMCSecondClearedDate,101) AS SomaticMCSecondClearedDate,
CONVERT(VARCHAR(16),PsycMCSecondClearedDate,101) AS PsycMCSecondClearedDate,
CONVERT(VARCHAR(16),DEThirdClearedDate,101) AS DEThirdClearedDate,
CONVERT(VARCHAR(16),SomaticMCThirdClearedDate,101) AS SomaticMCThirdClearedDate,
CONVERT(VARCHAR(16),PsycMCThirdClearedDate,101) AS PsycMCThirdClearedDate,
CONVERT(VARCHAR(16),DEFourthClearedDate,101) AS DEFourthClearedDate,
CONVERT(VARCHAR(16),SomaticMCFourthClearedDate,101) AS SomaticMCFourthClearedDate,
CONVERT(VARCHAR(16),PsycMCFourthClearedDate,101) AS PsycMCFourthClearedDate
FROM dbo.ROCAPData
WHERE (DDS = 'BO'
OR DDS = 'NH'
OR DDS = 'CT')
GROUP BY DEClearedDate,
SomaticMCClearedDate,
PsycMCClearedDate,
DESecondClearedDate,
SomaticMCSecondClearedDate,
PsycMCSecondClearedDate,
DEThirdClearedDate,
SomaticMCThirdClearedDate,
PsycMCThirdClearedDate,
DEFourthClearedDate,
SomaticMCFourthClearedDate,
PsycMCFourthClearedDate
Hope it helps,
Cheers,
J-F
January 26, 2009 at 12:56 pm
Hi There,
Thanks for the helps, but I have a question about the answer that you have posted. If I wish to display all three regions together in other word that I wish to add all three regions into one, and names it as ALL. So how do I do that ?
Thanks
January 26, 2009 at 1:04 pm
create PROCSelectForReport
as
@Parameter nvarchar(3)
BEGIN
if @Parameter = 'ALL'
BEGIN
Select blablabla
--With no where clause (you get all the rows)
END
else
BEGIN
select blablabla
where region = @Parameter
END
END
Hope this helps,
Cheers,
J-F
January 26, 2009 at 1:24 pm
JF is right - you need a stored procedure for this.
However, I would suggest you can simplify it slightly:
create PROCSelectForReport
@Parameter nvarchar(3)
as
BEGIN
select blablabla
where ((region = @Parameter) or ('all' = @Parameter))
END
If the parameter is 'All' the second part of the statement will be true and all records will be returned. If the parameter is something else then the first part of the statement will be true and only those records will be returned.
B
January 26, 2009 at 1:58 pm
Hi Jean,
I think I got an answer from you and B. Thank you very much, but I have a small question which relates to the question that I asked you to help me. That question is:
Before I create the Store Procedure in order to set the parameter, do I have to add the Where clause in my Select Statement such as ( DDS = ('BO' + 'CT' + 'NH'= All)) in order to allow the store procedure's parameter to understand where is ALL Coming from.
Thank you in advances.
Joseph
January 26, 2009 at 2:02 pm
Look at the clause B added:
select * from blabla
where (Region = @Parameter) or (Parameter = 'ALL')
This will get you either the region, or if your parameter is 'ALL' it will return every region.
That should get you going,
Cheers,
J-F
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply