Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Querying an SQL database

Former Member
0 Kudos

Hi

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]

2 REPLIES 2

horst_keller
Product and Topic Expert
Product and Topic Expert

ABAP Development?

Jelena
Active Contributor
0 Kudos

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