strange issue

[CoSpringsGuy]

Crystal 2012, Intersystems Cache DB

linked two tables together on a left join Col_Inv_Stats_MTD.Collector_ID and User_Master.USERID

I need all records in left table that do not have a match on the above link so my record selection says
isnull({User_Master.USER_ID})

so for two reasons i needed to change it a little. 1) I dont need the right table at all. I just used it initially to find the IDs that NEVER exist in that table. 2) I know know the 4 IDs I need for future reporting.

so I changed my record selection code to:

{Col_Inv_Stats_MTD.COLLECTOR_ID} in ["AGE","ATT","LEG","REG"] // nothing returns
then tried
{Col_Inv_Stats_MTD.COLLECTOR_ID} = "AGE" // nothing returns

those are 4 of the IDs that WERE returned with the initial code... I get no results even though I know they are there

I also tried to creat SQL variable {fn CONVERT( Col_Inv_Stats_MTD . COLLECTOR_ID ,SQL_VARCHAR )} and replaced {Col_Inv_Stats_MTD.COLLECTOR_ID} above in all examples and still get nothing

this code does work however
instr({Col_Inv_Stats_MTD.COLLECTOR_ID},"AEE") > 0
or
instr({Col_Inv_Stats_MTD.COLLECTOR_ID},"ATT") > 0
or
instr({Col_Inv_Stats_MTD.COLLECTOR_ID},"LEG") > 0
or
instr({Col_Inv_Stats_MTD.COLLECTOR_ID},"REG") > 0


Anyone have an explanation for why this might be happening? I guess I can work around it but it is very odd to me and Im hoping someone else can explain it to me.




_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

WOW! OK discovered more but makes the issue even more confusing
with my record selection working as i wanted it to if I drop Col_Inv_Stats_MTD.COLLECTOR_ID straight into the report I get the following results
AGE
ATT
LEG
REG
exactly as I would expect
So after I posted the above I played around a bit and placed the SQL expression right next to that field.
SQL Expression is {fn CONVERT( Col_Inv_Stats_MTD . COLLECTOR_ID ,SQL_VARCHAR )} and replaced {Col_Inv_Stats_MTD.COLLECTOR_ID}
//NOTE: thats the exact same field alread in the report
So I would have expected it to return
AGE AGE
ATT ATT
LEG LEG
REG REG

but instead it returned
AGE AGE
ATT ATTY
LEG LEGL
REG REGC

Im so confused


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

with that new revelation the following code works but Im still baffled

{%collectorID} in ["AGENCY","LEGL","ATTY","REGC"]

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[IanWaterman]

Is the field size in one table too small ie Varchar(3) and as a result it gets cropped

Ian

 

[CoSpringsGuy]

Ian,

It has to be something like that. Im sure it is database related and beyond my minimal expertise. Crystal is only seeing three characters. But the field itself has to be more than three because the SQL expression is seeing more. Just odd. I will have to keep this in the back of my mind. Make me wonder how many other times this has been the case but it just hasnt been an obvious issue like this time.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[fisheromacse]

very odd. i saw similar once using a view that recast some data types.
It took me forever it seemed like, to figure out why my data wasn't matching.
Nice to know i am not the only one who runs into these sorts of odd things.