Msg 116, Level 16, State 1, Procedure MarksheetGenerator, Line 59
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 13605, Level 16, State 1, Procedure MarksheetGenerator, Line 28
Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.
Answer - 1
When i have created procedure as well create sub query under main query and retrun json value from sql server as given below.
/*
EXEC MarksheetGenerator
@ClassId =105,
@TerminalId =16,
@GroupId =23,
@AcademicYearId =1,
@SchoolCode ='GR-002'
*/
ALTER PROCEDURE [dbo].[MarksheetGenerator]
(
@ClassId INT =NULL,
@TerminalId INT=NULL,
@GroupId INT=NULL,
@StudentId INT =NULL,
@AcademicYearId INT=NULL,
@SchoolCode NVARCHAR(200)=NULL,
@ActionType NVARCHAR(50)=NULL
)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX);
SET @Result=
(
SELECT
ST.FirstName +' '+ ISNULL(ST.MidName,'') +' '+ ST.LastName,
C.ClassName AS ClassName,
F.FacultyName AS FacultyName,
--ST.SectionName AS Section,
SC.RollNo AS RollNo,
SEC.SectionName AS SectionName
,(
SELECT
M.SubjectId AS SubjectId,
M.ObtainThroryMarks AS ObtainThroryMarks,
M.ObtainPracticalMarks AS ObtainPracticalMarks,
ISNULL(M.ObtainThroryMarks,0) +ISNULL(M.ObtainPracticalMarks,0)AS ObtainedMarks,
SUB.SubjectName AS SubjectName,
SUB.SubjectCode AS SubjectCode,
SUB.FullMark AS FullMark,
SUB.TheoryPassMarks AS TheoryPassMarks,
SUB.PracticalPassMarks AS PracticalPassMarks,
EX.CheckType AS CheckType
FROM MarkSheet AS M
INNER JOIN SetupSubject AS SUB
ON M.SubjectId = SUB.SubjectId
LEFT JOIN ExamAttendance AS EX
ON M.StudentId = EX.StudentId
WHERE M.StudentId=ST.StudentId
AND M.SchoolCode= @SchoolCode
AND EX.AcademicYearId = @AcademicYearId
) AS MarkSheetViewModelReportList
FROM StudentInfo AS ST
INNER JOIN studentclassinfo AS SC
ON ST.StudentId = SC.StudentId
INNER JOIN SetupSection AS SEC
ON ST.SectionId = SEC.SectionId
INNER JOIN SetupClass AS C
ON ST.ClassId = C.ClassId
INNER JOIN SetupFaculty AS F
ON ST.FacultyId = F.FacultyId
WHERE [email protected]
AND [email protected]
AND ST.SchoolCode= @SchoolCode
FOR JSON PATH,ROOT('MarkSheetViewModelReportList')
)
END
First I have added For JSON PATH above the ) AS MarkSheetViewModelReportList like this
FOR JSON PATH
) AS MarkSheetViewModelReportList
After that run again issue was occured as
Msg 13605, Level 16, State 1, Procedure MarksheetGenerator, Line 25
Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.
After That i have change
ST.FirstName +' '+ ISNULL(ST.MidName,'') +' '+ ST.LastName,
Change To
ST.FirstName AS FirstName
ISNULL(ST.MidName,'') AS MidName,
ST.LastName AS LastName
OR
you can do this.
ST.FirstName +''+ISNULL(ST.MidName,'') +''+ ST.LastName AS Name,
and I have run my script it's working fine.I have solved this my problems as above code.
Answer - 2
You should return only one column and one row in the where query where you assign the returned value to a variable. Example:
SELECT * FROM <YOUR-TABLE-1> WHERE DATE INT (select <Column1> FROM Dates)
Answer -3
Try this.
Select t1.FrmDate As FromDate, t1.ToDate as ToDate, t1.Branch + ' ' + t2.WhsName as Description , t1.DocEntry As Code
from BZ_OIAB t1
inner join OWHS t2 on t2.WhsCode=t1.Branch
Where t1.Branch =1
Answer -4
You can't return two (or multiple) columns in your subquery to do the comparison in the WHERE A_ID IN (subquery) clause - which column is it supposed to compare A_ID to? Your subquery must only return the one column needed for the comparison to the column on the other side of the IN. So the query needs to be of the form:
SELECT * From ThisTable WHERE ThisColumn IN (SELECT ThatColumn FROM ThatTable)
You also want to add sorting so you can select just from the top rows, but you don't need to return the COUNT as a column in order to do your sort; sorting in the ORDER clause is independent of the columns returned by the query.
select count(distinct dNum)
from myDB.dbo.AQ
where A_ID in
(SELECT DISTINCT TOP (0.1) PERCENT A_ID
FROM myDB.dbo.AQ
WHERE M > 1 and B = 0
GROUP BY A_ID
ORDER BY COUNT(DISTINCT dNum) DESC)
Comments