JOIN EXPRESSION

[Guest]

I need help on writing a JOIN statement in ACCESS.

I have attached a portion of a query used in Teradata SQL Assistant 6.2.

cast(substr(cast(a11.mdse_nbr as char(4)),2, 3) as int) = a12.sub

This portion of the SQl represents the join expression needed in ACCESS. My
problem Is that I do not know how to replicate the statement.

Essentially what I need to do is join the last 3 digits of a11.mdse_nbr and
a12.sub.

Both fields are numeric. The equivalent in VBA to grab the 3 digits would
be right(a11.mdse_nbr,3).

I have no idea what the cast nor the substr statements do.
Can someone please help me in this?

 

[Per Larsen]

I need help on writing a JOIN statement in ACCESS.

I have attached a portion of a query used in Teradata SQL Assistant 6.2.

cast(substr(cast(a11.mdse_nbr as char(4)),2, 3) as int) = a12.sub

This portion of the SQl represents the join expression needed in ACCESS. My
problem Is that I do not know how to replicate the statement.

Essentially what I need to do is join the last 3 digits of a11.mdse_nbr and
a12.sub.

Have you tried:
JOIN CInt(Right(all.msde_nbr, 3) = a12.sub

or maybe 'INNER JOIN CInt...'

hth
PerL