Joining mis-matched columns

[Ian Mitchell]

I need to do a join on t_object.pdata1 and t_diagram.diagram_ID - as a way to find out which diagrams are Composites (Thanks QUERTY for the hints in 'Inside EA' - how would we manage without this?).
BUT
The data types of the columns are different - PDATA1 is a varchar, Diagram_ID is a number.
Stackoverflow etc seem to suggest that it's possible to do a join (in Access - this is an EAPX file at the moment, but will need to work in other DBMSs as well later) where the column data types are different, by 'casting' in the SQL, but I can't seem to get this to work.
Does anyone have experience of doing this kind of cast/join ?

[Ian Mitchell]

Strange that the act of putting something onto this forum magically helps me find an answer myself.
Seems that coercing strings into numbers isn't allowed ( sensible, I suppose, even though I know it will work in my case, but maybe Access is protecting me from myself..)
..but coercing numbers into strings is allowed.

Key bit of SQL seems to be:
T_OBJECT.PDATA1 = CStr(T_DIAGRAM.DIAGRAM_ID)
So, I make both sides into strings, and join on them.

So the full SQL to get the Activity-type composite diagrams (see 'Inside EA for more explanation) is:
Dim s As String = " Select T_DIAGRAM.PACKAGE_ID,T_OBJECT.OBJECT_ID as OBJECT_ID,  t_OBJECT.PDATA1 as DIAGRAM_ID, T_DIAGRAM.DIAGRAM_TYPE , T_DIAGRAM.STYLEEX  "
        s = s & " FROM T_OBJECT, T_DIAGRAM  "
        s = s & " WHERE "
        s = s & " T_OBJECT.NTYPE=8 "  'This is what tells us that PDATA1 is the ID of the Composite diagram
        s = s & " AND "
        s = s & " T_DIAGRAM.PACKAGE_ID in " & instring
        s = s & " AND "
        s = s & " T_OBJECT.PDATA1 = CStr(T_DIAGRAM.DIAGRAM_ID) "
...where instring is a string of numbers of the packages we want to search into.

[Geert Bellekens]

Sometimes you can also get away with "LIKE" instead of "="

The Cstr function might be pretty database specific.

Geert

[Eve]

The Cstr function might be pretty database specific.

Indeed it's pretty much one unique syntax for each database type you support. (But number to string is a good idea with all of them)

[Ian Mitchell]

 

[qwerty]

As an exception: not Sparx' fault. The deeper you dig into databases the more flavors you find. If you stay at the surface its pretty unique. Of course, Mickeysoft plays it's Jokers like the "*" wild card. And the Oscar goes to....

q.