Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with SQL Syntax in a SPROC

Posted on 2007-11-27
3
Medium Priority
?
147 Views
Last Modified: 2010-04-21
I am using SQL Server 2005, and have 2 Tables, Tbl_Instances and Tbl_Call_Escalation_Lists:

      Tbl_Instances has 2700 entries in it.
      Tbl_Call_Escalation_Lists has records that relate to Tbl_Instances.

There may be any number of records in Tbl_Call_Escalation_Lists for each record in Tbl_Instances, BUT, there MUST be at least 1.

Instance_ID in Tbl_Call_Escalation_Lists contains the ID of the related record in Tbl_Instances.

I have a section of code in a long SPROC that is trying to put records in a temporary table with a count of the number of entries in Tbl_Call_Escalation_Lists for each record in Tbl_Instances, and where there are no entries in Tbl_Call_Escalation_Lists, put an entry into the temporary table with a count of 0.

Here is that code:

DECLARE @Tbl2 TABLE(Apps_Cat_ID INT, Instance_ID INT, Resource_Count INT)

DELETE FROM @Tbl2
--Insert all the entries into @Tbl2 where there are Escalation List counts
INSERT INTO @Tbl2
SELECT [Tbl_Call_Escalation_Lists].[Apps_Cat_ID],[Tbl_Call_Escalation_Lists].[Instance_ID],count([Tbl_Call_Escalation_Lists].[ID]) AS Resource_Count
FROM [Tbl_Call_Escalation_Lists]
WHERE (Lookup_To_GrgLst_Support_Types_For_Support_Type = 2)
GROUP BY [Tbl_Call_Escalation_Lists].[Apps_Cat_ID], [Tbl_Call_Escalation_Lists].[Instance_ID]

[[[[[The code above works fine.]]]]]

--Now insert zero counts for the remaining apps
INSERT INTO @Tbl2
SELECT [Tbl_Instances].[Apps_Cat_ID], [Tbl_Instances].[ID] AS Instance_ID, 0 AS Resource_Count
FROM [Tbl_Instances]
WHERE ([ID] NOT IN (SELECT [Instance_ID] AS ID FROM @Tbl2))

[[[[[The code above does NOT work]]]]]

IT seems so simple!!  But, why does the second segment of code not work?  What do I need to change?

BTW, I could probably make the first segment of code put into @Tbl2 one entry for every record in Tbl_Instances, with 0 as the default for Resource_Count, and then update the counts in @Tbl2 with the counts of whatever I find in Tbl_Call_Escalation_Lists.  I will start to explore that, but I still want to know why that code above does not work!
0
Comment
Question by:wsturdev
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 total points
ID: 20357957
Could it be that it's getting the ID's confused in this?
<WHERE ([ID] NOT IN (SELECT [Instance_ID] AS ID FROM @Tbl2))>

You could try:
WHERE ([ID] NOT IN (SELECT [Instance_ID] FROM @Tbl2))

Alternatively, use a join as the source:

INSERT INTO @Tbl2
SELECT [Tbl_Instances].[Apps_Cat_ID], [Tbl_Instances].[ID] AS Instance_ID, 0 AS Resource_Count
FROM [Tbl_Instances]  left join @Tbl2 on [ID]  = Instance_ID
WHERE Instance_ID Is Null
0
 
LVL 1

Author Closing Comment

by:wsturdev
ID: 31411191
The join did it.  Thanks!
0
 
LVL 1

Author Comment

by:wsturdev
ID: 20358022
I added a comment in my acceptance, but it does not seem to have posted.

The join did it.  Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question