7 Replies - 1040 Views - Last Post: 08 July 2015 - 07:58 AM

#1 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Having an issue with a SELECT CASE in SQL Server 2008

Posted 07 July 2015 - 12:30 PM

We have a database file for MSAccess that has a view which calls multiple other views to get counts for members by job number, because we keep over a years worth of data. I need to replicate that view in SQL Server because we are getting ready to receive data a lot more frequently. I have the following Function

CREATE FUNCTION TOTALCount
(
	@jobNumber nvarchar(20)
)
RETURNS TABLE
AS
RETURN
(
	SELECT MEMBERS.RID,
		ISNull([cncpt.CountOfRID], 0) AS ClaimNCountCoPayTrue_Result,
		ISNull([cncpf.CountOfRID], 0) AS ClaimNCountCoPayFalse_Result,
		[cyc.CountOfRID] AS ClaimYCount_Result,
		ISNull([cycpt.CountOfRID], 0) AS ClaimYCountCoPayTrue_Result,
		ISNull([cycpf.CountOfRID], 0) AS ClaimYCountCoPayFalse_Result,
		ISNull([pwCount.CountOfRID], 0) AS POWERCount_Result,
		ISNull([pvCount.CountOfProviderName], 0) AS ProviderCount_Result,
		ISNull([pwCount.CountOfRID], 0) + ISNull([pvCount.CountOfProviderName], 0) + ((ISNull([cncpt.CountOfRID], 0) * 4) + (ISNull([cncpf.CountOfRID], 0) * 3) + (SELECT CASE WHEN [cyc.CountOfRID] > 0 THEN (ISNull([cycpf.CountOfRID], 0) * 3 + ISNull([cycpt.CountOfRID], 0) * 4 + 7 + 6) ELSE 4 + 6 END) AS TotalCount_Result
		FROM  MEMBERS m
		LEFT JOIN POWERCount(@jobNumber) pwCount
		ON m.RID = pwCount.RID
		LEFT JOIN ProviderCount(@jobNumber) pvCount
		ON m.RID = pvCount.RID
		LEFT JOIN ClaimNCountCoPayFalse(@jobNumber) cncpf
		ON m.RID = cncpf.RID
		LEFT JOIN ClaimNCountCoPayTrue(@jobNumber) cncpt
		ON m.RID = cncpt.RID
		LEFT JOIN ClaimYCountCoPayFalse(@jobNumber) cycpf
		ON m.RID = cycpf.RID
		LEFT JOIN ClaimYCountCoPayTrue(@jobNumber) cycpt
		ON m.RID = cycpt.RID
		LEFT JOIN ClaimYCount(@jobNumber) cyc
		ON m.RID = cyc.RID
)



I keep getting the following error
Msg 156, Level 15, State 1, Procedure TOTALCount, Line 17
Incorrect syntax near the keyword 'AS'.


From what I can tell I have my syntax correct, any idea on what is actually wrong with this query? I can provide information on the other Functions called from this one if necessary.

-- EDIT: I've also tried using the Alias names for the columns in the first select query for the case statement, but it returned the same error.

This post has been edited by ybadragon: 07 July 2015 - 12:31 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Having an issue with a SELECT CASE in SQL Server 2008

#2 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 07 July 2015 - 01:48 PM

I just found the following syntax as well, but it isn't working either. Now I'm getting an error "Incorrect syntax near FROM"

TotalCount_Result = (ISNull([pwCount.CountOfRID], 0) + ISNull([pvCount.CountOfProviderName], 0) + ((ISNull([cncpt.CountOfRID], 0) * 4) + (ISNull([cncpf.CountOfRID], 0) * 3) + (CASE WHEN [cyc.CountOfRID] > 0 THEN (ISNull([cycpf.CountOfRID], 0) * 3 + ISNull([cycpt.CountOfRID], 0) * 4 + 7 + 6) ELSE 4 + 6 END))

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is online

  • blow up my boots
  • member icon

Reputation: 6549
  • View blog
  • Posts: 26,557
  • Joined: 12-December 12

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 07 July 2015 - 01:57 PM

I probably can't help but, as a suggestion, you might try splitting it into two parts:
Temp_Result = CASE .. END, other + stuff + Temp_Result AS TotalCount_Result

(It would be nice if you could go back a few steps and make all those counts yield 0 rather than having to check for null with each subsequent query ;) )

This post has been edited by andrewsw: 07 July 2015 - 02:06 PM

Was This Post Helpful? 0
  • +
  • -

#4 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 07 July 2015 - 09:53 PM

I will try splitting it out when I get to work today, (been working on an audio driver issue for the past 4 hours...). I will also try to remove the necessity of checking for null in the other calls. Will post back with results.
Was This Post Helpful? 0
  • +
  • -

#5 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 08 July 2015 - 06:54 AM

Ok, so I did that and it still got the same error. I didn't realize SSMS has a query designer, so I think I'm going to try to do that to get my results and look at the SQL generated by it to see what I've been doing wrong.
Was This Post Helpful? 0
  • +
  • -

#6 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 08 July 2015 - 07:25 AM

Figured it out finally. It didn't like that I had the function alias name inside brackets.

Below is my final result

CREATE FUNCTION TOTALCount
(
	@jobNumber nvarchar(20)
)
RETURNS TABLE
AS
RETURN
(
	SELECT m.RID,
		ISNull(cncpt.CountOfRID, 0) AS ClaimNCountCoPayTrue_Result,
		cyc.CountOfRID AS ClaimYCount_Result,
		ISNull(cycpt.CountOfRID, 0) AS ClaimYCountCoPayTrue_Result,
		ISNull(cycpf.CountOfRID, 0) AS ClaimYCountCoPayFalse_Result,
		ISNull(pwCount.CountOfRID, 0) AS POWERCount_Result,
		ISNull(pvCount.CountOfProviderName, 0) AS ProviderCount_Result,
		--Temp_Result = CASE WHEN cyc.CountOfRID > 0 THEN (ISNull(cycpf.CountOfRID, 0) * 3 + ISNull(cycpt.CountOfRID, 0) * 4 + 7 + 6) ELSE 4 + 6 END,
		ISNull(pwCount.CountOfRID, 0) + ISNull(pvCount.CountOfProviderName, 0) + ISNull(cncpt.CountOfRID, 0) * 4 + ISNull(cncpf.CountOfRID, 0) * 3 + CASE WHEN cyc.CountOfRID > 0 THEN (ISNull(cycpf.CountOfRID, 0) * 3 + ISNull(cycpt.CountOfRID, 0) * 4 + 7 + 6) ELSE 4 + 6 END AS 'TotalCount_Result'
		FROM  MEMBERS m
		LEFT JOIN POWERCount(@jobNumber) pwCount
		ON m.RID = pwCount.RID
		LEFT JOIN ProviderCount(@jobNumber) pvCount
		ON m.RID = pvCount.RID
		LEFT JOIN ClaimNCountCoPayFalse(@jobNumber) cncpf
		ON m.RID = cncpf.RID
		LEFT JOIN ClaimNCountCoPayTrue(@jobNumber) cncpt
		ON m.RID = cncpt.RID
		LEFT JOIN ClaimYCountCoPayFalse(@jobNumber) cycpf
		ON m.RID = cycpf.RID
		LEFT JOIN ClaimYCountCoPayTrue(@jobNumber) cycpt
		ON m.RID = cycpt.RID
		LEFT JOIN ClaimYCount(@jobNumber) cyc
		ON m.RID = cyc.RID
)


This post has been edited by ybadragon: 08 July 2015 - 07:27 AM

Was This Post Helpful? 1
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7181
  • View blog
  • Posts: 14,969
  • Joined: 16-October 07

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 08 July 2015 - 07:51 AM

Glad you got it. What's with all those procedure calls?!? Why is every field _Result?

Where I to deal with this, I'd consider a subquery. e.g.
SELECT a.RID,
        a.ClaimNCountCoPayTrue_Result,
        a.ClaimYCount_Result,
        a.ClaimYCountCoPayTrue_Result,
        a.ClaimYCountCoPayFalse_Result,
        a.POWERCount_Result,
        a.ProviderCount_Result,
        a.POWERCount_Result
            + a.ProviderCount_Result
            + a.ClaimYCountCoPayTrue_Result * 4
            + a.ClaimYCountCoPayFalse_Result * 3
            + (
                CASE
                    WHEN a.ClaimYCount_Result > 0 THEN
                        a.ClaimYCountCoPayTrue_Result * 4
                        + a.ClaimYCountCoPayFalse_Result * 3
                        + 7 + 6
                    ELSE 4 + 6
                END) AS TotalCount_Result
    FROM (
        SELECT m.RID,
               ISNull(cncpt.CountOfRID, 0) AS ClaimNCountCoPayTrue_Result,
               cyc.CountOfRID AS ClaimYCount_Result,
               ISNull(cycpt.CountOfRID, 0) AS ClaimYCountCoPayTrue_Result,
               ISNull(cycpf.CountOfRID, 0) AS ClaimYCountCoPayFalse_Result,
               ISNull(pwCount.CountOfRID, 0) AS POWERCount_Result,
               ISNull(pvCount.CountOfProviderName, 0) AS ProviderCount_Result
            FROM  MEMBERS m
               LEFT JOIN POWERCount(@jobNumber) pwCount ON m.RID = pwCount.RID
               LEFT JOIN ProviderCount(@jobNumber) pvCount ON m.RID = pvCount.RID
               LEFT JOIN ClaimNCountCoPayFalse(@jobNumber) cncpf ON m.RID = cncpf.RID
               LEFT JOIN ClaimNCountCoPayTrue(@jobNumber) cncpt ON m.RID = cncpt.RID
               LEFT JOIN ClaimYCountCoPayFalse(@jobNumber) cycpf ON m.RID = cycpf.RID
               LEFT JOIN ClaimYCountCoPayTrue(@jobNumber) cycpt ON m.RID = cycpt.RID
               LEFT JOIN ClaimYCount(@jobNumber) cyc ON m.RID = cyc.RID
        ) a


This post has been edited by baavgai: 08 July 2015 - 07:51 AM

Was This Post Helpful? 0
  • +
  • -

#8 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Having an issue with a SELECT CASE in SQL Server 2008

Posted 08 July 2015 - 07:58 AM

The reason for them being _Result is just for separation so I know exactly what the Column names reference which Function calls without calling the column the name of the function itself. I'm not super familiar with SQL queries, so I didn't think of using the subquery.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1