Highlighted

Convert String Data

Explorer ,
Apr 29, 2015

Copy link to clipboard

Copied

I am using the CF10 Report Builder.

I need to convert a 6 character string and compare it to a 20 character string.

Input = 201516 needs to be converted to 201508 and 201601. I am trying to handle the conversion in my query and not pass any records that I do not want to count to my report. IF the is a better way I am open to suggestions. Here is the code in my query that gives me a "Conversion failed when converting the varchar value 'E' to data type int'

WHERE name.soc_sec = transact.soc_sec
         AND name.soc_sec = prospect.soc_sec
         AND name.soc_sec = approg.soc_sec
         AND name.soc_sec = nmcrs.soc_sec
         AND name.soc_sec = transact.soc_sec
         AND transact.accept <> 'v'
         AND transact.tcodes = 'AW'
         AND name.last_name not like '%Teste%'
         AND prospect.term_int NOT BETWEEN LEFT(rpt_schy.sch_yr, 4) + '08' AND  LEFT(rpt_schy.sch_yr, 2) + RIGHT(rpt_schy.sch_yr,2)  + '01'
        

AND transact.sch_yr = rpt_schy.sch_yr

Any help would be greatly appreciated.

By process of elimination, if you remove the entire where clause from your query, do you still get the error?

Cheers

Eddie

TOPICS
Reporting

Views

746

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

Convert String Data

Explorer ,
Apr 29, 2015

Copy link to clipboard

Copied

I am using the CF10 Report Builder.

I need to convert a 6 character string and compare it to a 20 character string.

Input = 201516 needs to be converted to 201508 and 201601. I am trying to handle the conversion in my query and not pass any records that I do not want to count to my report. IF the is a better way I am open to suggestions. Here is the code in my query that gives me a "Conversion failed when converting the varchar value 'E' to data type int'

WHERE name.soc_sec = transact.soc_sec
         AND name.soc_sec = prospect.soc_sec
         AND name.soc_sec = approg.soc_sec
         AND name.soc_sec = nmcrs.soc_sec
         AND name.soc_sec = transact.soc_sec
         AND transact.accept <> 'v'
         AND transact.tcodes = 'AW'
         AND name.last_name not like '%Teste%'
         AND prospect.term_int NOT BETWEEN LEFT(rpt_schy.sch_yr, 4) + '08' AND  LEFT(rpt_schy.sch_yr, 2) + RIGHT(rpt_schy.sch_yr,2)  + '01'
        

AND transact.sch_yr = rpt_schy.sch_yr

Any help would be greatly appreciated.

By process of elimination, if you remove the entire where clause from your query, do you still get the error?

Cheers

Eddie

TOPICS
Reporting

Views

747

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
Apr 29, 2015 0
Advocate ,
Apr 30, 2015

Copy link to clipboard

Copied

What is the data type (in the schema) of the following fields?

prospect.term_int

rpt_schy.sch_yr

transact.sch_yr

Cheers

Eddie

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...
Apr 30, 2015 0
Explorer ,
Apr 30, 2015

Copy link to clipboard

Copied

All have string data types

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...
Apr 30, 2015 0
Advocate ,
Apr 30, 2015

Copy link to clipboard

Copied

By process of elimination, if you remove the entire where clause from your query, do you still get the error?

Cheers

Eddie

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...
Apr 30, 2015 0
Explorer ,
Apr 30, 2015

Copy link to clipboard

Copied

I removed the WHERE clause and received a message "Could not allocate space for object '<temporary system object: 422273174667264>' in database 'tempdb' because the 'PRIMARY' filegroup is full.

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...
Apr 30, 2015 0
Advocate ,
Apr 30, 2015

Copy link to clipboard

Copied

Add select top 10 to the select clause. This will reduce the amount of rows returned.

Cheers

Eddie

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...
Apr 30, 2015 0
Explorer ,
Apr 30, 2015

Copy link to clipboard

Copied

I get "incorrect syntax near thekey word 'distinct'" Here is my select

statement:

SELECT TOP 10 distinct name.soc_sec,

finaideurekaawddesc.finaideurekaawddesc, MAX(nmcrs.sch_yr) AS Enrolled_Yr,

transact.semester, transact.offered, name.level_,

SUM(distinct (case when approg.prg_cod <> 'UNGRDTR' AND name.level_ < 3 AND

nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS

FRSO_Enroll,

SUM(distinct (case when approg.prg_cod <> 'UNGRDTR' AND name.level_ = 3 AND

nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS

JR_Enroll,

SUM(distinct (case when approg.prg_cod <> 'UNGRDTR' AND name.level_ > 3 AND

nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS

SRplus_Enroll,

SUM(distinct (case when approg.prg_cod <> 'UNGRDTR' AND name.level_ < 3 AND

nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS

FRSO_Offer,

SUM(distinct (case when approg.prg_cod <> 'UNGRDTR' AND name.level_ = 3 AND

nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS

JR_Offer,

SUM(distinct (case when approg.prg_cod <> 'UNGRDTR' AND name.level_ > 3 AND

nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS

SRPlus_Offer

FROM name, transact, prospect, nmcrs, finaideurekaawddesc, approg, rpt_schy

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Thu, Apr 30, 2015 at 10:15 AM, EddieLotter <forums_noreply@adobe.com>

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...
Apr 30, 2015 0
Advocate ,
Apr 30, 2015

Copy link to clipboard

Copied

For now, remove distinct.

If you still get a syntax error message then tell us what database engine you're using.

Cheers

Eddie

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...
Apr 30, 2015 0
Explorer ,
Apr 30, 2015

Copy link to clipboard

Copied

I started to eliminate other lines of code and discovered that level_ is a character and I was using it as an integer.  Thanks for you help. You pointed me in the correct troubleshooting method!

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...
Apr 30, 2015 0
Advocate ,
Apr 30, 2015

Copy link to clipboard

Copied

You're welcome.

Cheers

Eddie

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...
Apr 30, 2015 0