Querying an SQL database

Former Member — Wed, 01 Nov 2017 16:25:25 GMT

I am getting an error when querying an SQL database stating that only SELECT statements are permitted when attempting to run the following query. Can I not use variables?

DECLARE @acadyear AS VARCHAR(20);

SET @acadyear = '2012/2013';

DECLARE @studentcount AS INTEGER;

DECLARE @date AS SMALLDATETIME;

DECLARE @eventstart AS SMALLDATETIME;

DECLARE @eventend AS SMALLDATETIME;

SET @eventstart = dfsi.dbo.Getyearstartdate(@Acadyear);

SET @eventend = dfsi.dbo.Getyearenddate(@Acadyear);

SET @date = CASE

WHEN Getdate() > @eventend THEN @eventend

ELSE Getdate()

END;

SELECT @acadyear AS academicyear,

'Attendance' as Cat_text,

'Average' as text,

'Whole School' as text_Whole,

Count(DISTINCT person_id) AS groupcount,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(attendancetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS attendance,

Sum(attendancetotal) AS attendancetotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(presenttotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS present,

Sum(presenttotal) AS presenttotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(aeatotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS aea,

Sum(aeatotal) AS aeatotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(authorisedabsencetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS aa,

Sum(authorisedabsencetotal) AS authorisedabsencetotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(unauthorisedabsencetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS uaa,

Sum(unauthorisedabsencetotal) AS unauthorisedabsencetotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(latebeforetotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS latebefore,

Sum(latebeforetotal) AS latebeforetotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(lateaftertotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS lateafter,

Sum(lateaftertotal) AS lateaftertotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(latestotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS lates,

Sum(latestotal) AS latestotal,

CASE

WHEN Sum(possible) = 0 THEN 0

ELSE Cast(Sum(missingtotal) * 100 / Sum(possible) AS DECIMAL(5, 2))

END AS missing,

Sum(missingtotal) AS missingtotal,

Sum(possible) AS possibletotal

FROM (SELECT *,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(( present + aea ) * 100 / possible AS DECIMAL(5, 2))

END AS attendancepc,

( present + aea ) AS attendancetotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(present * 100 / possible AS DECIMAL(5, 2))

END AS presentpc,

present AS presenttotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(aea * 100 / possible AS DECIMAL(5, 2))

END AS aeapc,

aea AS aeatotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(authorisedabsence * 100 / possible AS DECIMAL(5, 2))

END AS authorisedpc,

authorisedabsence AS authorisedabsencetotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(( unauthorisedabsence ) * 100 / possible AS DECIMAL(5, 2))

END AS unauthorisedpc,

unauthorisedabsence AS unauthorisedabsencetotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(( latebefore + lateafter ) * 100 / possible AS DECIMAL(5, 2))

END AS latepc,

( latebefore + lateafter ) AS latestotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(latebefore * 100 / possible AS DECIMAL(5, 2))

END AS latebeforepc,

latebefore AS latebeforetotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(lateafter * 100 / possible AS DECIMAL(5, 2))

END AS lateafterpc,

lateafter AS lateaftertotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(missing * 100 / possible AS DECIMAL(5, 2))

END AS missingpc,

missing AS missingtotal,

CASE

WHEN possible = 0 THEN 0

ELSE Cast(unexplained * 100 / possible AS DECIMAL(5, 2))

END AS unexplainedpc,

unexplained AS unexplainedtotal

FROM (SELECT sd.person_id,

chosen_forename,

chosen_surname,

gender,

year_group,

reg,

house,

@eventstart AS yearstart,

@date AS date,

Sum(present) AS present,

Sum(present) - Sum(lates) AS present_minus_Lates,

Sum(attended) AS attended,

Sum(authorisedabsence) AS authorisedabsence,

Sum(unauthorisedabsence) AS unauthorisedabsence,

Sum(aea) AS aea,

Sum(latebefore) AS latebefore,

Sum(lateafter) AS lateafter,

Sum(lates) AS lates,

Sum(Minutes_late) AS Minutes_late,

Cast(Avg(Minutes_late)AS DECIMAL(5, 1)) AS Minutes_late_Ave,

Sum(possible) AS possible,

Sum(not_required) AS not_required,

Sum(missing) AS missing,

Sum(unexplained) AS unexplained

FROM (SELECT * FROM dfsi.dbo.attendance_summary_cache a

WHERE Summary_Type = 'Person Day'

AND session_start >= @eventstart

AND session_start <= @date

--[APPENDFILTER(TERM, Term_or_holiday)]

--[APPENDFILTER(ATTENDANCEDATE, session_start)]

--[APPENDCLIENTFILTER(MARKDATE, session_start)]

--[APPENDCLIENTFILTER(ATTENDANCEDATE, session_start)]

--[APPENDCLIENTFILTER(TERM, Term_or_holiday)]

) dt1

JOIN dfsi.dbo.Stud_details_cache sd

ON sd.person_id = dt1.att_person_id and sd.acadyear = @acadyear

WHERE 1=1

--[APPENDFILTER(YG,year_group)]

--[APPENDFILTER(GENDER,GENDER)]

--[APPENDFILTER(HOUSE,HOUSE)]

--[APPENDFILTER(FSM,FSM)]

--[APPENDFILTER(PREMIUM,PREMIUM)]

--[APPENDFILTER(SEN,SEN_STATUS)]

--[APPENDFILTER(GIFTED,GIFTED)]

--[APPENDFILTER(ETHNICITY,ETHNICITY)]

--[APPENDFILTER(EAL,EAL)]

--[APPENDFILTER(AttendanceDate,session_start)]

--[APPENDFILTER(TERM,Term_or_holiday)]

--[APPENDCLIENTFILTERS]

Group by sd.person_id,

chosen_forename,

chosen_surname,

gender,

year_group,

reg,

house

) dt2)dt3

[Private information removed by Moderator]

Re: Querying an SQL database

retired_member — Wed, 01 Nov 2017 20:37:46 GMT

ABAP Development?

Re: Querying an SQL database

Jelena_Perfiljeva — Thu, 02 Nov 2017 21:05:32 GMT

Shouldn't this be posted on MSDN instead? Very much confused by this question... And why oh why copy-paste pages of SQL?

topic Re: Querying an SQL database in Application Development and Automation Discussions

Querying an SQL database

Re: Querying an SQL database

Re: Querying an SQL database