Joining tables wth non-matching fields..

[IanMullineaux]

Hi

I am trying to joint 2 tables together but the fields i need to use are not identical..

The EPRFRef field in EPRMain is made up of 16 digits with a letter of the alphabet in position 17. This field has been taken accros into the EPRFVulnerable Adult table but the suffix has been changed to a different character - both fields are of type string.

The first 16 characters are identical and would therefor be ideal to use in the join but i dont know how to do this -

I have attempted to use LEFT to specify the first 16 characters of each field but get an error -

The sql statement is below:

SELECT "EPRFMain"."IncidentNumber", "EPRFMain"."IncidentDate", "EPRFMain"."Callsign", "EPRFMain"."Crew1Pin", "EPRFMain"."Crew2Pin", "EPRFVulnerableAdult"."VAGender", "EPRFVulnerableAdult"."VAAdultDob", "EPRFVulnerableAdult"."VAEthnicity", "EPRFVulnerableAdult"."VAPhysicalAbuse", "EPRFVulnerableAdult"."VASexualAbuse", "EPRFVulnerableAdult"."VAEmotionalAbuse", "EPRFVulnerableAdult"."VAFinancialAbuse", "EPRFVulnerableAdult"."VANeglect", "EPRFVulnerableAdult"."VADiscriminatoryAbuse", "EPRFVulnerableAdult"."VAPhysicalSigns", "EPRFVulnerableAdult"."VAInconsistentStory", "EPRFVulnerableAdult"."VABehaviouralSigns", "EPRFVulnerableAdult"."VAEnvironment", "EPRFVulnerableAdult"."VADisclosure", "EPRFVulnerableAdult"."VAVulnerableWhy", "EPRFVulnerableAdult"."VAConcerned", "EPRFVulnerableAdult"."VAConveyed", "EPRFVulnerableAdult"."VADangerIfNotConveyed", "EPRFVulnerableAdult"."VAAdultName", "EPRFVulnerableAdult"."VAAdultAddress", "EPRFVulnerableAdult"."VADescWhyVulnerable", "EPRFVulnerableAdult"."VAGP", "EPRFVulnerableAdult"."VADescConcerns", "EPRFVulnerableAdult"."VADescEvents", "EPRFVulnerableAdult"."VAReportedTo"
FROM "EPRFwarehouse"."dbo"."EPRFVulnerableAdult" "EPRFVulnerableAdult" INNER JOIN "EPRFwarehouse"."dbo". "EPRFMain" ON LEFT("EPRFVulnerableAdult"."VAEprfRef",16)=LEFT("EPRFMain"."EprfRef",16)

could anyone tell me how to make this work please...

 

[PruSQLer]

Substr("EPRFVulnerableAdult"."VAEprfRef",1,16)=Substr("EPRFMain"."EprfRef",1,16) should do the trick, as long as your RDB supports Substring.

It might not be too efficient though. If the field is Indexed, I believe this function will negate the benefit of said Index.

 

[IanMullineaux]

thanks for the advice - getting a message telling me it doesnt recognise stbstr..

 

[r937]

if your database system does not support SUBSTR, then you should look into the docs for your database system to find out what the substring function is actually called

we can't tell you because this is the ANSI SQL forum and you haven't mentioned which database system you're using

r937.com | rudy.ca

 

[IanMullineaux]

ah right -

i'm using crystal reports but hacing to create a command to sort this problem out - the database is microsoft sql server -

thanks for you time - i'll have a dig and se what i can find

 

[JarlH]

we can't tell you because this is the ANSI SQL forum and you haven't mentioned which database system you're using"...

So why not give him the ANSI SQL answer:

SUBSTRING("EPRFVulnerableAdult"."VAEprfRef" FROM 1 FOR 16) = SUBSTRING("EPRFMain"."EprfRef" FROM 1 FOR 16)

 

[r937]

of course, i shoulda

my bad, eh



r937.com | rudy.ca