Substrings when linking fields

[shannonlp]

I’ve working with two tables but the linking fields are not identical. The tables that need to be joined are:

EXPDIST_INV.COST_CENT_DSC & LOS_RESPONSIBILITY.LOS

The first three characters of EXPDIST_INV.COST_CENT_DSC need to be joined to LOS_RESPONSIBILITY.LOS (this is a 3 character field).

Is there a way to join fields using a substring in the Database Expert? Or do I need to use the Add Command function?

I tried

Select *
From EXPDIST_INV, LOS_RESPONSIBILITY
Where SUBSTR(EXPDIST_INV.COST_CENT_DSC,3,3) = LOS_RESPONSIBILITY.LOS

but it didn’t like it. My SQL experience is very limited. I'm on Crystal XI.

Thanks for any help - Shannon

 

[synapsevampire]

You have to learn what type of database you have, there are numerous flavors of SQL, and any time you post, include this very basic information.

The database design is bad, your dba should go back to linging lattes at the corner Starbucks, and you should have the new dba create a View on the database with the truncated field built in for joining purposes.

-k

 

[shannonlp]

We are on Oracle 9i. I believe the syntax I posted is correct for O9i but Crystal did not like it.

I was hoping that that Crystal could handle substrings when joining although if the only option is to have a view created so be it. I’ve noticed that Crystal can’t even join tables on fields that are not the same case and have had to have views created even in those situations.

 

[synapsevampire]

The Command object should be fine with it as well.

If you've tested it in SQL Plus,, it should work, a Command doesn't preprocess, it passes what you've supplied to the database.

Our connectivity might muck something up, try the Oracle native connectivity, it's under Oracle Server.

-k