Select and count not working in Crystal, but is in SQL mgmt studio

[ghbeers]

I have the following sql script I am trying to use in Crystal. It works correctly in SQL mgmt studio, but does not work consistently in crystal. When there are no matching entries in the WAIT_LIST table, the results are counts for a bunch of COURSE_SECTIONS_IDs that are not even referenced in the WAIT_LIST table. But when there are one or more matches, the results are 100% correct. I tried many approaches of the select and they all return the same results. I've used this same counting approach many times, but something is different here. What am I missing this time?


DECLARE @WAITCOUNTS AS TABLE
(
SEC_ID varchar(100),
WAITCOUNT decimal (4,0)
)

INSERT INTO @WAITCOUNTS
SELECT DISTINCT S.COURSE_SECTIONS_ID,
COUNT(W.WAIT_LIST_ID) -- or COUNT (*)

FROM COURSE_SECTIONS S, WAIT_LIST W

WHERE S.SEC_TERM = ('2015SP')
AND dbo.SEC_CURRENT_STATUS(S.COURSE_SECTIONS_ID) = 'A'
AND S.COURSE_SECTIONS_ID = W.WAIT_COURSE_SECTION
AND W.WAIT_STATUS = 'A'
GROUP BY S.COURSE_SECTIONS_ID
ORDER BY S.COURSE_SECTIONS_ID

SELECT * FROM @WAITCOUNTS

 

[briangriffin]

I don't know why it would matter, but:
1. Why use the variable table when the results of the query would return the same dataset?
2. Since you're grouping by Course_Sections_ID, you don't really need the Distinct keyword.

Again, these are just observations; I have no real explanation for the behavior you describe. I would create a report based on this command, then select View SQL from the Database menu to ensure it's identical to the command.

SELECT
S.COURSE_SECTIONS_ID,
COUNT(*)

FROM COURSE_SECTIONS S, WAIT_LIST W

WHERE S.SEC_TERM = ('2015SP')
AND dbo.SEC_CURRENT_STATUS(S.COURSE_SECTIONS_ID) = 'A'
AND S.COURSE_SECTIONS_ID = W.WAIT_COURSE_SECTION
AND W.WAIT_STATUS = 'A'

GROUP BY S.COURSE_SECTIONS_ID