Highlighted

Query - Error converting data type varchar to float.

Explorer ,
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.

What database fields are defined as type float?

Views

330

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

Query - Error converting data type varchar to float.

Explorer ,
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.

What database fields are defined as type float?

Views

331

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
May 18, 2015 0
Advocate ,
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
Reply
Loading...
May 19, 2015 0
Explorer ,
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
Reply
Loading...
May 19, 2015 0
Explorer ,
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
Reply
Loading...
May 19, 2015 0
haxtbh LATEST
Advocate ,
May 19, 2015

Copy link to clipboard

Copied

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
Reply
Loading...
May 19, 2015 0