Delphi-"Special" SQL statement needed 
Author Message
 Delphi-"Special" SQL statement needed

Hi all,

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

        TABLE   FIELDNAME       TYPE    WIDTH
        -----   ---------       ----    -----
        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.




Thu, 08 Jan 1998 03:00:00 GMT  
 Delphi-"Special" SQL statement needed


(snip)

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

>    TABLE   FIELDNAME       TYPE    WIDTH
>    -----   ---------       ----    -----
>    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;"

(snip)
>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).

When you say hardcoded I assume you are Not using 'XXX.AsString' or
'XXX.AsInteger' in which case it shouldn't be too bad. You would just
have to update the definition of 'XXX'.

Ken Devory Jr.



Fri, 09 Jan 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. "Missing comma" error with SQL statement

2. SQL statements don't accept "/"

3. TQuery SQL statement "Capability Not Supported"

4. The keys "-"/"+"/"*"

5. Newbee Q: Special BitBtn."down" component

6. "AND"-statement in TTable filter?

7. Gwbasic "Play " statement for TP7

8. "odd" statement

9. "data" statement in TP7

10. "data" statement in TP

11. SQL Server/Delphi/"SET" problem

12. ""256 Color Mouse""

 

 
Powered by phpBB® Forum Software