Highlighted

Use an Alias in a WHERE statement

Explorer ,
Jun 15, 2015

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.

TOPICS
Database access

Views

264

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Use an Alias in a WHERE statement

Explorer ,
Jun 15, 2015

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.

TOPICS
Database access

Views

265

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 15, 2015 0

Have something to add?

Join the conversation