Query - Error converting data type varchar to float.

Explorer ,
May 18, 2015 May 18, 2015

Copy link to clipboard

Copied

The whole pop up error message is

java.sql.SQLException:[Macromedia][SQLServer JDBC Driver][SQLServer] Error ....

I am using CF10 Report Builder. Here is my query:

SELECT name.first_name, name.last_name, name.soc_sec, name.name_le1, name.camp_cod, LEFT(MMTemp.term_int, 6) AS Term, MMTemp.st_addr, MMTemp.add_addr, MMTemp.city, MMTemp.state, MMTemp.zip, transact.sch_yr AS SchYr, transact.ref_id AS AwardDesc, transact.offered AS AwdAmt, nmact.act_memo, MMTemp.Operator, rpt_schy.token, nmbudget.nmbudget_tuition AS Tuition, nmbudget_RM_BRD AS RoomBoard,

CASE

    WHEN finaideurekaawdcat.finaidawdcat_cod like 'E%' OR finaideurekaawdcat.finaidawdcat_cod like 'OT' THEN 'Gift aid & schlorships'

    WHEN finaideurekaawdcat.finaidawdcat_cod = 'FG' THEN 'Need based aid'

    WHEN finaideurekaawdcat.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   name, MMTemp, nmact, transact, finaideurekaawd, finaideurekaawdcat, rpt_schy, nmbudget

WHERE  (MMTemp.Operator = rpt_schy.token or MMTemp.Operator = Right(MMTemp.Operator,8))

AND MMTemp.soc_sec=name.soc_sec

AND MMTemp.soc_sec=nmact.soc_sec

AND MMTemp.activity_cod=nmact.activity_cod

AND MMTemp.soc_sec=transact.soc_sec

AND MMTemp.activity_cod='AM'

AND MMTemp.soc_sec=nmbudget.soc_sec

AND transact.tcodes='AW'

AND transact.accept<>'v'

AND transact.sch_yr = rpt_schy.sch_yr

AND rpt_schy.token = '#session.token#'

AND transact.awd_id=finaideurekaawd.finaidawddesc_cod

AND finaideurekaawd.finaidawdcat_cod=finaideurekaawdcat.finaidawdcat_cod

UNION

SELECT finaideurekaawddesc.finaideurekaawddesc AS AwardDesc, finaideurekaawd.finaideurekaawdtot AS AwdAmt, name.first_name, name.last_name, name.soc_sec, name.name_le1, name.camp_cod, LEFT(MMTemp.term_int, 6) AS Term, MMTemp.st_addr, MMTemp.add_addr, MMTemp.city, MMTemp.state, MMTemp.zip, MMTemp.activity_cod, MMTemp.Operator, finaideurekaawd.Sch_years AS SchYr, nmact.act_memo, nmbudget.nmbudget_tuition AS Tuition, nmbudget_RM_BRD AS RoomBoard,

CASE

    WHEN finaideurekaawdcat.finaidawdcat_cod like 'E%' OR finaideurekaawdcat.finaidawdcat_cod like 'OT' THEN 'Gift aid & schlorships'

    WHEN finaideurekaawdcat.finaidawdcat_cod = 'FG' THEN 'Need based aid'

    WHEN finaideurekaawdcat.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  MMTemp, name, nmact, finaideurekaawd, finaideureka, finaideurekaawddesc, finaideurekaawdcat, rpt_schy, nmbudget

WHERE (MMTemp.Operator = rpt_schy.token or MMTemp.Operator = Right(MMTemp.Operator,8)) 

AND MMTemp.activity_cod='AM'

AND MMTemp.activity_cod=nmact.activity_cod

  AND finaideurekaawd.sch_years=rpt_schy.sch_yr

AND rpt_schy.token = '#session.token#'

AND MMTemp.soc_sec=name.soc_sec

AND MMTemp.soc_sec=nmbudget.soc_sec

AND name.soc_sec=nmact.soc_sec

AND name.soc_sec=finaideurekaawd.soc_sec

AND name.soc_sec=finaideureka.soc_sec

AND finaideurekaawd.finaidawddesc_cod=finaideurekaawddesc.finaidawddesc_cod

AND finaideurekaawd.finaidawdcat_cod=finaideurekaawdcat.finaidawdcat_cod

ORDER BY name.soc_sec

I do not see anywhere that I am converting varchar to float.

Views

337

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

correct answers 1 Correct Answer

Advocate , May 19, 2015 May 19, 2015
What database fields are defined as type float?

Likes

Translate

Translate
Advocate ,
May 19, 2015 May 19, 2015

Copy link to clipboard

Copied

What database fields are defined as type float?

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
community guidelines
Explorer ,
May 19, 2015 May 19, 2015

Copy link to clipboard

Copied

nmbudget.tuition & nmbudget.RM_BRD

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

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
community guidelines
Explorer ,
May 19, 2015 May 19, 2015

Copy link to clipboard

Copied

Your question lead me to check the sequence of both select statement. Fixed the sequence and now it works.

Thank you for your time!

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
community guidelines
Advocate ,
May 19, 2015 May 19, 2015

Copy link to clipboard

Copied

LATEST

Good to hear!

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