find and solve || findandsolve.com
Please wait.....

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

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)

Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

Comments



Report Response