I am using The Report Builder in CF10.
I need to select data from multiple tables. One of the conditions is that the term_int (value of 201508 is converted to school year) = to sch_years (201516) in another table.
Here is my query:
/* Get new student loan information */
SELECT name.first_name, name.last_name, name.mi, name.soc_sec, name.name_le1, finaideurekaawddesc.finaideurekaawddesc AS AwardDesc, MMTemp.st_addr, MMTemp.add_addr, MMTemp.city, MMTemp.state, MMTemp.zip, MMTemp.Operator, finaideurekaawd.finaideurekaawdtot AS AwdAmt, nmact.act_memo, /* nmbudget.nmbudget_tuition AS Tuition, nmbudget_RM_BRD AS RoomBoard,*/ position.val AS Position
CASE
WHEN SUBSTRING(MMTemp.term_int, 5,2) = '08' then 'Fall of '
ELSE 'Spring of ' END AS EntryPeriod,
CASE
WHEN SUBSTRING(MMTemp.term_int, 5,2) = '08' then LEFT(finaideurekaawd.sch_years,4)
ELSE (LEFT(finaideurekaawd.sch_years,2) + RIGHT(finaideurekaawd.sch_years,2)) END AS EntryYear,
CASE
WHEN finaideurekaawddesc.finaidawdcat_cod like 'E%' OR finaideurekaawddesc.finaidawdcat_cod like 'OT' THEN ' Gift aid & schlorships'
WHEN finaideurekaawddesc.finaidawdcat_cod = 'FG' THEN ' Need based aid'
WHEN finaideurekaawddesc.finaidawdcat_cod = 'FL' THEN 'Federal Direct Loans'
ELSE 'Work Study' END AS CatType,
CASE WHEN name.camp_cod = 'AdultLearn' THEN 17911 ELSE 20510 END AS Tuition,
CASE WHEN name.name_le1 = 0 THEN 8835 ELSE 0 END AS RoomBoard
FROM finaideurekaawd, name, MMTemp, nmact, finaideurekaawddesc /*, nmbudget */
CROSS APPLY
(
SELECT
CASE
WHEN SUBSTRING(MMTemp.term_int, 5,2) = '08' then (LEFT(MMTemp.term_int, 4) + SUBSTRING(MMTemp.term_int, 3,2)+1)
ELSE (LEFT(MMTemp.term_int, 4-1) + SUBSTRING(MMTemp.term_int, 5,2)) END AS val
FROM MMTemp
) SchYr
AS position
WHERE (MMTemp.Operator = '#session.token#' OR SUBSTRING('#session.token#',2,10) LIKE MMTemp.Operator)
AND MMTemp.soc_sec=name.soc_sec
AND MMTemp.soc_sec=nmact.soc_sec
AND MMTemp.activity_cod='AM'
AND SchYr.val = finaideurekaawd.sch_years
AND MMTemp.soc_sec=finaideurekaawd.soc_sec
/* AND MMTemp.soc_sec=nmbudget.soc_sec */
AND MMTemp.activity_cod=nmact.activity_cod
AND finaideurekaawd.finaidawddesc_cod=finaideurekaawddesc.finaidawddesc_cod
AND nmact.act_date=(SELECT MAX(nmact.act_date)from nmact where nmact.soc_sec = MMTemp.soc_sec and nmact.activity_cod = 'AM' group by nmact.soc_sec, nmact.activity_cod)
I get a 'Incorrect syntax near the keyword 'CASE' error. I am open to any and all suggestions.
AdChoices