Use an Alias in a Where Statement

[Cozmo2]

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.

[EddieLotter]

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

[Cozmo2]

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?

[EddieLotter · Accepted solution]

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