Referencing other tables

[kerino]

Hi,
I have a table T1, which has a column 'dNum', and computed column.
I also have another table CT, which just contains a single field 'Conv' holding a conversion factor.
How can I define the computed column to hole (dNum * CT.Conv)?
If I use ...
'CREATE TABLE T1 (' ...etc.
'Comp1 COMPUTED BY (dNum * CT.Conv)'
I get an error saying CT.Conv does not belong to the referenced table.
How do I do this please?

Many thanks

[btrewern]

Which DB are you using?

I don't expect you will be able to do this whatever database you are using. Try using a view instead.

In delphi you would do this using a calculated field and doing the calculation in the OnCalcFields event handler.

Regards,

Ben

[kerino]

Hi Ben,
I forgot to say I'm using Firebird, Zeos, Delphi5.
What the problem is, is that I have a column which holds a dimension in inches, but I need to display it in inches, cm, mm etc. (user selectable)
I thought a computed column would do the trick, but I'm open to suggestions.
I'll take a look at Views, as I haven't used them before.

Many thanks
Kerin

[kerino]

Hi,
I've had a look at creating a view, but still seem to have the same problem i.e. how do I reference a field from another table.
Is there another way to do this?
The actual conversion factor is a variable in the Delphi program, so is there any way I can reference this in the computed field?

Many thanks - Keirn

[sandeep_c24]

He Keirn

If you want to use computed columns in database try this

CREATE TABLE T1 (' ...etc.
Comp1 COMPUTED BY (dNum * (select c.Conv from CT c where SOME_CONDITION))

As far as computed columns are concerned you can do what you want in the OnCalculate event.

Regards

Sandeep

[kerino]

Hi Sandeep,

Exactly what I wanted - it works great !!

Many thanks
Kerin