Board index » delphi » Delphi-"Special" SQL needed

Delphi-"Special" SQL needed

Hi all,

I have 2 tables linked by a key field INVNO. However, this system
was not properly set up initially so:

        -----   ---------       ----    -----
        TBL1    INVNO           CHAR      8
        TBL2    INVNO           NUMERIC   8

I want to use SQL to link the two tables together via this
key. Normally, if the tables' key fields are of the same type,
it should be as simple as:

     sql = "SELECT * FROM tbl1 a, tbl2 b WHERE a.invno = b.invno;"

Given the above setup, I need to convert one of the key field
either form CHAR to NUMERIC or vice versa. So the sql statement would
have to be modified to say

     sql = "SELECT * FROM tbl1 a, tbl2 b WHERE a.invno = "
     sql = sql + val(b.invno)

        --- OR ---

     sql = "SELECT * FROM tbl1 a, tbl2 b WHERE b.invno = "
     sql = sql + str(b.invno) + ";"

where VAL() and STR() are functions for converting the parameter
to numeric and string respectively.

The problem is that I cannot seem to be able to do it. Does anyone
have any clues? This will also be a problem if you try to do it
in dBASE for Windows or Paradox for Windows.

I know I can change one of the tables' key fields to be 'compatiable'
with the other. However, this is not feasible as the coding has been
pretty extensive (i.e. hard-coded to convert from one from to another
within the program unfortunately).

Thanks for your help in advance.

7/23/95 @ 1500 hrs


Re:Delphi-"Special" SQL needed


As it is the SQL tables which are incorrect you must modify
the SQL statement and not the DELPHI code eg.

SQL := 'SELECT * FROM Tbl1 a,Tbl2 b WHERE a.INVNO ='
SQL := SQL+ 'CONVERT(varchar(8),b.INVNO)'

Hope this works
Cheers !

Other Threads