Copy link to clipboard
Copied
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.
Cozmo2 wrote:
How do I move this question to a database forum?
Copy the text of your question and paste it into a new post in a database forum; preferably a forum that deals specifically with your database engine.
Cheers
Eddie
Copy link to clipboard
Copied
You should post your database query questions in a database forum rather than a Web application development forum. You will get better coverage.
I suspect your database engine does not like you trying to add the numeric value 1 to the string value SUBSTRING(MMTemp.term_int,3,2).
Cheers
Eddie
Copy link to clipboard
Copied
I removed the adding of 1 to a string. It didn't make a difference. How do I move this question to a database forum?
Copy link to clipboard
Copied
Cozmo2 wrote:
How do I move this question to a database forum?
Copy the text of your question and paste it into a new post in a database forum; preferably a forum that deals specifically with your database engine.
Cheers
Eddie