SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Case Select Sum


Case Select Sum

Author
Message
davidc-765016
davidc-765016
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 111
I have the following sql:

SELECT Id, Name,

CASE
(
(SELECT SUM(Revenue)
FROM myTable
WHERE [Start Date] = '2008-08-01' AND Name = 'myName')
)
WHEN > 0 THEN 'Has Revenue'
ELSE 'NO Revenue'
END

FROM myTable


Any pointers getting the case statement working?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213629 Visits: 41977
Haven't tested it because no data... but this should work
 SELECT ID, Name,
CASE SUM(Revenue)
WHEN 0 THEN 'NO Revenue'
ELSE 'Has Revenue'
END
FROM MyTable
WHERE [Start Date] = '2008-08-01' AND Name = 'myName'

...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
davidc-765016
davidc-765016
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 111
Worked like a charm, thanks
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213629 Visits: 41977
Thanks for the feedback, David.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
richard.noordam
richard.noordam
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 443
I'm having trouble with the following bit. I have a fairly complex set of case statments but the following is what I'm having trouble with (most inside portion)

            ELSE
CASE
WHEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID is NULL THEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join AS vw_BASE_WebData_FacultyWebs_Classes_Join_2 WHERE InstructorName = dbo.vw_BASE_CCSSM_REP_Class_D.INSTR_NAME AND WDFC_CI_DeptDiv = dbo.vw_BASE_CCSSM_REP_Class_D.CI_DEPT_DIV AND WDFC_CI_Course_Num = dbo.vw_BASE_CCSSM_REP_Class_D.CI_COURSE_NUM AND WDFC_Class_YRQ = dbo.vw_BASE_CCSSM_REP_Class_D.CLASS_YRQ
ELSE SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID

END



what's the hang up or am I trying something that shouldn't/can't be done this way? All I'm trying to do is see which condition exists in this extra table, and fill the data based on that, essentially returning a URL that goes to a specific class for a student.
Ben Teraberry
Ben Teraberry
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2875 Visits: 1199
richard.noordam (6/2/2010)
I'm having trouble with the following bit. I have a fairly complex set of case statments but the following is what I'm having trouble with (most inside portion)

            ELSE
CASE
WHEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID is NULL THEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join AS vw_BASE_WebData_FacultyWebs_Classes_Join_2 WHERE InstructorName = dbo.vw_BASE_CCSSM_REP_Class_D.INSTR_NAME AND WDFC_CI_DeptDiv = dbo.vw_BASE_CCSSM_REP_Class_D.CI_DEPT_DIV AND WDFC_CI_Course_Num = dbo.vw_BASE_CCSSM_REP_Class_D.CI_COURSE_NUM AND WDFC_Class_YRQ = dbo.vw_BASE_CCSSM_REP_Class_D.CLASS_YRQ
ELSE SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID

END



what's the hang up or am I trying something that shouldn't/can't be done this way? All I'm trying to do is see which condition exists in this extra table, and fill the data based on that, essentially returning a URL that goes to a specific class for a student.


It's a little hard to tell without seeing the rest of the query or having any test data or table definitions ...

But, it seems to me this would be much easier to support and troubleshoot and would have superior performance if instead of doing multiple selects within a CASE statement you handle it in a more set-based fashion. Establish the correct joins in your query and then do a CASE based on the values of the records in the joined tables.

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
richard.noordam
richard.noordam
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 443
specifically in the ELSE Case I get
Error in list of function arguments: 'SELECT' not recognized.
Error in list of function arguments: ')' is not recognized.
Unable to parse query.

I would normally do as you suggested but in this case it isn't as easy as that, and it's a special situation. Back to the point.

Here is the whole case statement. I'm trying to return a specific URL depending on several conditions.

       CASE (SBCTC_MISC_1) 
WHEN '85' THEN 'Campus Meetings; See angelccs.spokane.edu'
WHEN '30' THEN
CASE (SECT)
WHEN 'TD' THEN 'See angelccs.spokane.edu'
ELSE 'See http://angel.spokanefalls.edu/'
END
ELSE
CASE
WHEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID is NULL THEN SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join AS vw_BASE_WebData_FacultyWebs_Classes_Join_2 WHERE InstructorName = dbo.vw_BASE_CCSSM_REP_Class_D.INSTR_NAME AND WDFC_CI_DeptDiv = dbo.vw_BASE_CCSSM_REP_Class_D.CI_DEPT_DIV AND WDFC_CI_Course_Num = dbo.vw_BASE_CCSSM_REP_Class_D.CI_COURSE_NUM AND WDFC_Class_YRQ = dbo.vw_BASE_CCSSM_REP_Class_D.CLASS_YRQ
ELSE SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID

END
END AS URL


the ELSE CASE in the innermost case is the issue, specifically this statement (below) in a WHEN (X) is null, WHERE X is a select statement.

I went down the set based, and couldn't get it to work the way I wanted, (and I'm not an SQL novice, or intermediate and have been doing this, for right about 15 yrs, starting with Oracle and then MSSQL).

In the end I want 1 URL based on the Following logic


CASE (SBCTC_MISC_1) -- SOURCE 1
WHEN '85' THEN URL = angelccs.spokane.edu
WHEN '30' THEN
CASE (SECT)
WHEN 'TD' THEN URL = angelccs.spokane.edu
ELSE URL = http://angel.spokanefalls.edu
END
ELSE
[color=#FF000] CASE
WHEN (SELECT STATEMENT TO DETERMINE first URL condition) is null
Then SELECT SQL URL from source_2
ELSE
Then Select SQL URL from source_3
END[/color]END




the color 'tagged' section is the only section I'm having issue with. Not sure why the color didn't show up. I'm SOOOOO close to the solution on this I can just feel it. Hehe

Note: Even after all has hooked up properly most of the classes STILL won't have a URL and that is ok, and correct.
richard.noordam
richard.noordam
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 443
it was a minor syntax issue:

correct parens fixed. Der....


CASE

WHEN (SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID) is NULL THEN (SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join AS vw_BASE_WebData_FacultyWebs_Classes_Join_2 WHERE InstructorName = dbo.vw_BASE_CCSSM_REP_Class_D.INSTR_NAME AND WDFC_CI_DeptDiv = dbo.vw_BASE_CCSSM_REP_Class_D.CI_DEPT_DIV AND WDFC_CI_Course_Num = dbo.vw_BASE_CCSSM_REP_Class_D.CI_COURSE_NUM AND WDFC_Class_YRQ = dbo.vw_BASE_CCSSM_REP_Class_D.CLASS_YRQ)
ELSE (SELECT WDF_SiteURL FROM dbo.vw_BASE_WebData_FacultyWebs_Classes_Join WHERE CLASSID = dbo.vw_BASE_CCSSM_REP_Stu_Class_D.CLASS_ID)
END



I think I was tired at the end of last week....
hanif.zahid
hanif.zahid
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 3
SELECT EMPLOYEE_ID, Last_Name,
CASE SUM(Salary)
WHEN 0 THEN 'NO Revenue'
ELSE 'Has Revenue'
END as "Revenue"
FROM employees
WHERE employee_id = '100'
group by employee_id,last_name
/

YEE_ID LAST_NAME Revenue
------ ------------------------- -----------
100 King Has Revenue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search