• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Use an Alias in a Where Statement

Explorer ,
Jun 11, 2015 Jun 11, 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
Reporting

Views

624

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
community guidelines

correct answers 1 Correct answer

Advocate , Jun 15, 2015 Jun 15, 2015

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

Votes

Translate

Translate
Advocate ,
Jun 11, 2015 Jun 11, 2015

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

Votes

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
community guidelines
Explorer ,
Jun 11, 2015 Jun 11, 2015

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?

Votes

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
community guidelines
Advocate ,
Jun 15, 2015 Jun 15, 2015

Copy link to clipboard

Copied

LATEST

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

Votes

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
community guidelines
Resources
Documentation