Hi guys,

I have a piece of code that's driving me mad... any clues will be great..

I'm running a piece of code that looks like this.

select v.id,
v.registrationnumber,
v.dateofregistration,
vm.name,
vm.cc,
vm.doors,
vm.abicd,
vma.manufname,
vma.vmabicd,
m.transmission,
m2.transmission fueltype,
vm.doors,
v.numofseats
from vehicle v, vehiclemodel vm, vehiclemanufacturer vma,
mktlistitem m, mktlistitem m2, mktlistitem m3
where v.modelcd = vm.abicd
and vm.vehiclemanufid = vma.vmabicd
and (vm.transtypedc = m.abicd
and m.mktlistnameid = 49)
and (vm.fueltypedc = m2.abicd
and m2.mktlistnameid = 45)
/

and it works fine, however when I add another column and it's where clause I get the following error (bodytype column in the list and last where clause)

select v.id,
v.registrationnumber,
v.dateofregistration,
vm.name,
vm.cc,
vm.doors,
vm.abicd,
vma.manufname,
vma.vmabicd,
m.transmission,
m2.transmission fueltype,
m3.transmission bodytype
from vehicle v, vehiclemodel vm, vehiclemanufacturer vma,
mktlistitem m, mktlistitem m2, mktlistitem m3
where v.modelcd = vm.abicd
and vm.vehiclemanufid = vma.vmabicd
and (vm.transtypedc = m.abicd
and m.mktlistnameid = 49)
and (vm.fueltypedc = m2.abicd
and m2.mktlistnameid = 45)
and (vm.bodytypecd = m3.abicd
and m3.mktlistnameid = 55)
/and (vm.bodytypecd = m3.abicd
*
ERROR at line 21:
ORA-01722: invalid number

I've also added the desc of the 2 tables included in the join.

SQL> desc vehiclemodel
Name Null? Type
------------------------------- -------- ----
ID NUMBER(38)
NAME VARCHAR2(80)
CC NUMBER(38)
YEARFROM DATE
YEARTO DATE
BODYTYPECD NUMBER(38)
FUELTYPEDC NUMBER(38)
TRANSTYPEDC NUMBER(38)
DOORS NUMBER(38)
ABICD NUMBER(38)
VEHICLEMANUFID NUMBER(38)
STARTDATE DATE
EXPIRYDATE DATE
MARKERS NUMBER(38)

SQL> desc mktlistitem
Name Null? Type
------------------------------- -------- ----
ID NUMBER(38)
TRANSMISSION VARCHAR2(128)
ABICD VARCHAR2(16)
STARTDATE DATE
EXPIRYDATE DATE
MARKERS NUMBER(38)
MKTLISTNAMEID NUMBER(38)
PARENTLISTITEMID VARCHAR2(16)