|
Hi All,
I am getting the following error when I am running the stored procedure.
Msg 217, Level 16, State 1, Procedure rptClassInformation_sel, Line 26. Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
When I remove the following part, it is not throwing that error. Can you please let me know where am I missing, am I making something wrong, is it related to Line 26 as in the message.
WITH CTE AS
(
SELECT FILTER.GroupKey,
FILTER.ClassScheduleId,
SUM(SlotCount) AS SlotCount
FROM #SlotInformation INFO
INNER JOIN #Base FILTER
ON FILTER.ClassScheduleId = INFO.ClassScheduleId
AND (
@Level = 'Class'
OR FILTER.UseInAggs = 1
)
WHERE SlotPlanTypeId IN (@SLOTPLAN_ECEAP, @SLOTPLAN_ECEAPSPED)
GROUP BY
FILTER.GroupKey,
FILTER.ClassScheduleId
)
INSERT INTO #Results
(GroupKey, Identifier, Name, Value)
SELECT WEEK.GroupKey,
'NIEER' AS Identifier,
'Number of ECEAP slots in ECEAP classes meeting 8 or more hours per day' AS Name,
SUM(CASE WHEN WEEK.AverageHoursPerDay >= 8 THEN CTE.SlotCount ELSE 0 END) AS Value
FROM #WeeklyOverview WEEK
INNER JOIN CTE
ON CTE.GroupKey = WEEK.GroupKey
AND CTE.ClassScheduleId = WEEK.ClassScheduleId
GROUP BY
WEEK.GroupKey
UNION ALL
SELECT WEEK.GroupKey,
'NIEER' AS Identifier,
'Number of ECEAP slots in ECEAP classes meeting at least 4 hours but fewer than 8 hours per day' AS Name,
SUM(CASE WHEN WEEK.AverageHoursPerDay >= 4 AND WEEK.AverageHoursPerDay < 8 THEN CTE.SlotCount ELSE 0 END) AS Value
FROM #WeeklyOverview WEEK
INNER JOIN CTE
ON CTE.GroupKey = WEEK.GroupKey
AND CTE.ClassScheduleId = WEEK.ClassScheduleId
GROUP BY
WEEK.GroupKey
UNION ALL
SELECT WEEK.GroupKey,
'NIEER' AS Identifier,
'Number of ECEAP slots in ECEAP classes meeting fewer than 4 hours per day' AS Name,
SUM(CASE WHEN WEEK.AverageHoursPerDay < 4 THEN CTE.SlotCount ELSE 0 END) AS Value
FROM #WeeklyOverview WEEK
INNER JOIN CTE
ON CTE.GroupKey = WEEK.GroupKey
AND CTE.ClassScheduleId = WEEK.ClassScheduleId
GROUP BY
WEEK.GroupKey;
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 22-Jun-15 12:59pm.
|
|
|
|
|
A common table expression can only be used in the statement which immediately follows its declaration. In your code, that's the SELECT * FROM CTE; statement.
If you want to re-use it in the INSERT INTO statement, then you have to re-state it:
WITH CTE As
(
...
)
SELECT * FROM CTE;
WITH CTE As
(
...
)
INSERT INTO #Results
...
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am sorry but that was not the issue even without putting the select statement error was coming. I put that select statement in process of debugging it.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi all 
Currently now I used MS SQL Server Management Studio 2008.
I have an existing table with data already store on that table.
Table Name :
Application
Column :
Categories (nvarchar(1), null)
Sequence (nvarchar(1), null)
Detail (nvarchar(150), null)
I would like to ask how to add primary key to column Categories and Sequence ? Do I need to use alter query?
Thank you in advance !
|
|
|
|
|
|
Alright ! i got it now. Thank you Peter 
|
|
|
|
|
Hi,
for creating primary key u must need to maintain that columns as not null,
so first execute this query to make it as not null
<pre lang="sql"> ALTER TABLE D_D_ext
ALTER COLUMN Categories NVARCHAR(1) NOT NULL ;
ALTER TABLE D_D_ext
ALTER COLUMN Sequence NVARCHAR(1) NOT NULL ;
Then execute this query for creating Primary key
ALTER TABLE D_D_ext
ADD CONSTRAINT pk_D_D_ext_Categories_Sequence PRIMARY KEY (Categories, Sequence)
Thanks
dhamu
|
|
|
|
|
Hi dhamu 
alright ! i try to apply the suggestion that you give. thank you for your response
|
|
|
|
|
You can't define a primary key on a nullable column, so first you will need to ensure there are no null values in the Categories column and in the Sequence column. Then you can alter the table so these columns are not null:
ALTER TABLE [Application] ALTER COLUMN Categories NVARCHAR(1) NOT NULL
ALTER TABLE [Application] ALTER COLUMN Sequence NVARCHAR(1) NOT NULL
Then you can create your primary key:
ALTER TABLE [Application] ADD CONSTRAINT PK_Application PRIMARY KEY (Categories, Sequence)
|
|
|
|
|
I'm developing a c# application to handle communication between two systems each one is interacting with the sql server database , the first one is a scada system used for supervision equipment (alerton envision for bactalk 3.0) and the second system is a coswin 8i which is a software package for a cmms system , thanks in advance . I add that I intend to work with windows services with a timer to ensure the recuperation of the lines that are added every 2 min to the table "Alarm" of the scada system and add them into the table "Intervenant" of the cmms system in order to get these informations as a service request interface at Coswin. I looked for the first time the server of the scada system and it wasn't something that gives hope for me especially since I could not find the interface of a dbms sql server , I found it integrated in the structure of the scada system also I don't find any sql server services in the Computer Management window on the computer server. If you can come to my assistance especially as it's a job I need to do for my final project studies , thank you very much .
|
|
|
|
|
Did you get any success with your project???
I'm developing a c# application to pull out data from the Alerton Envision Bactalk system, but there is no much information how to establish a connection between my interface and Alerton database, any help will be very appreciate.
|
|
|
|
|
Hi,
I was asked a question the other day which was, does using SQL Server Replication improve query response time. I answered no, because I couldn't quite see how it would. I thought I'd better do a bit of research before giving a definitive answer though
Thanks,
|
|
|
|
|
JammoD87 wrote: I was asked a question the other day
By someone in HR/recruiter I presume, I would be interested if I could see a relationship between the two functions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The question is not a valid question, it is not possible to answer, 'question does not compute'... - it is the equivalent of asking which is colder a banana or a strawberry.
In essence there is not enough information being provided within the question in order to answer it.
In the circumstances I would have asked for two examples database installations with details, one that is replicated and one that is not, I would then have more of an idea regarding which had an improved response time.
To give you some idea I use replication for a reporting server - I replicate the tables and stored procedures from the production server onto the replicated server so that when running reports the production server does not get a performance hit.
So in the setup I use replication does improve performance on the production server.
However in and of itself replication is nothing but a tool - used well it will help, used poorly it will hinder.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 15-Jun-15 3:37am.
|
|
|
|
|
I agree with what the others have said - it is a tool, and may or may not help.
In a past position, we used replication and the answer would be - it all depends on where you are and what you're querying.
We had replication from the computer room to two 'remote' sites - one about 1/4 mile away, the other about 30 miles away.
So, if I queried both the computer room database and the local database while at a 'remote' site on comparable database server hardware, I would expect the local response would be appreciably better.
If both systems were local, as suggested with a prodution and reporting server, then it may depend on the system load. For us, data input was a relatively level process, but between 7:00 and 8:30 in the morning, the reporting process hammered the systems.
|
|
|
|
|
Thanks for all the replies, I'm gonna go back and ask some more questions!! 
|
|
|
|
|
Hi All,
I have a table as Marks, depending upon their marks they got I have to count how many of them got failed, passed and distinction and another table is Class which specifies the class.
Class table has values like
Can anybody please help me in achieving this structure in SQL Server?
<h2>Id | Class</h2>
1 | 7th Class
2 | 8th Class
4 | 10th Class
Marks Table has values as
StudentId | ClassId | Marks |
1 | 1 | 35
2 | 1 | 90
3 | 2 | 75
4 | 2 | 30
5 | 4 | 99
6 | 4 | 25
7 | 4 | 36
8 | 4 | 70
9 | 4 | 65
10 | 4 | 55
Now the resulting table should look as below
Class | Failed | Passed |Distinction
7 th Class count count count
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You need to define what counts as a fail, a pass, and a distinction.
You also need to specify which DBMS you're using.
Assuming a recent version of Microsoft SQL Server, something like this should work:
DECLARE @DistinctionThreshold int = 90;
DECLARE @PassThreshold int = 70;
WITH cteCounts As
(
SELECT
ClassId,
SUM(CASE
WHEN Marks < @PassThreshold THEN 1
ELSE 0
END) As Failed,
SUM(CASE
WHEN Marks >= @PassThreshold And Marks < @DistinctionThreshold THEN 1
ELSE 0
END) As Passed,
SUM(CASE
WHEN Marks >= @DistinctionThreshold THEN 1
ELSE 0
END) As Distinction
FROM
dbo.Marks
GROUP BY
ClassId
)
SELECT
C.Class,
M.Failed,
M.Passed,
M.Distinction
FROM
dbo.Class As C
INNER JOIN cteCounts As M
ON M.ClassId = C.ClassId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
CREATE TABLE Class
(
Id BIGINT PRIMARY KEY
,Name VARCHAR(50)
)
INSERT INTO Class (Id, Name) VALUES (1, '7th Class')
INSERT INTO Class (Id, Name) VALUES (2, '8th Class')
INSERT INTO Class (Id, Name) VALUES (4, '10th Class')
CREATE TABLE Marks
(
StudentId BIGINT,
ClassId BIGINT,
Marks BIGINT
)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (1, 1, 35)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (2, 1, 90)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (3, 2, 75)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (4, 2, 30)Try something like
SELECT c.Name,
(SELECT COUNT( * ) FROM Marks WHERE ClassId = c.Id AND Marks >= 50) AS [Passed],
(SELECT COUNT( * ) FROM Marks WHERE ClassId = c.Id AND Marks < 50) AS [Failed]
FROM Class c
If you try the query here[^], you'll not just see the actual result, but also get timings and a nice execution plan.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
How to compare same database with different version inside visual studio. I have one project of database, it has different versions. I have to compare any two different version.
Thanks
|
|
|
|
|
Presuming you are using SQL Server (you do not tell us what database) you need to compare the schemas of the 2 databases by looping through the various sysobject and views. There are also a number of tools that will do the job for you, some of them free!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes Mycroft I am using SQL Server, let me try your idea.
Thanks
|
|
|
|
|
I am having the value in column 1 - 12 digits saved as per the selected month, While fetching the data I need to show the month name in select statement.. Please help ASAP Need DB2 Query
SELECT (SELECT MAX(BAUKID) FROM CRPDTA.F58SM039 ) AS RMAX
,BAUKID AS ID
,BADO AS DOCODE
,BAAA AS AMOUNT
,BAOBSZ AS NOOFJOBCODE
,BAS1FU AS EMPPOSNAME
,BAFB03 AS EMPCATEGORY
,BAMNTH AS MONTH ---(Here I need month name ,BAMNTH this contain any number in 1 to 12 )
FROM CRPDTA.F58SM039
GBD
|
|
|
|
|
Is this DB2 running on a mainframe (or m/frame included in any cross-platform stuff) or is it on a PC/Server platform?
|
|
|
|
|
Hello? Is there anybody there?
Which version of DB2 are you running? (It is relevent to how this could be answered)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
