Convert String Data

[Cozmo2]

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.

[EddieLotter]

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

prospect.term_int

rpt_schy.sch_yr

transact.sch_yr

Cheers

Eddie

[Cozmo2]

All have string data types

[EddieLotter · Accepted solution]

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

Cheers

Eddie

[Cozmo2]

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.

[EddieLotter]

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

Cheers

Eddie

[Cozmo2]

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>

[EddieLotter]

For now, remove distinct.

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

Cheers

Eddie

[Cozmo2]

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!

[EddieLotter]

You're welcome.

Cheers

Eddie