Confusing sql

[alison]

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)

[pando]

that is because you are joining a varchar2 to a number and Oracle is applying an implicit to_number in your varchar2 column

[alison]

I thought it was something like that, the confusing bit for me is why does it work with the numbers 49 and 45 but not with the 55..

[pando]

you must look values in m3.abicd and not others, other values works probably because the m3.abicd for that value is number and the implicit to_number works

[alison]

Thanks for your help, I put a to_char() around the vm.bodytypecd and this seems to work.

Just having a blond moment (no offence meant - I am blond...)

Allie

[slimdave]

I think it was the original designer who had the blonde moment. Or senior moment.

SQL> desc vehiclemodel
Name Null? Type
------------------------------- -------- ----
...
ABICD NUMBER(38)
...

SQL> desc mktlistitem
Name Null? Type
------------------------------- -------- ----
...
ABICD VARCHAR2(16)
...

What's going on with this abicd column?

[alison]

I've been doing some more work on this script, however during testing I've discovered that it's producing a cartasian merge join, any help or advice on how to tune - prevent this would be great - and yes I understand that there are no indexes, it's not the tuning for speed that I care about right now - just the sql working...

thanks v much.

SELECT v.id,
v.registrationnumber,
v.dateofregistration,
vm.name vehicle_model,
vm.abicd,
vma.manufname vehicle_manufacturer,
vma.vmabicd manufacturer_abi_code,
vm.cc engin_size,
m.transmission,
m2.transmission fueltype,
vm.doors,
v.numofseats,
m4.transmission paintworkcolour,
m3.transmission vehicle_body_style,
v.vinnumber,
m5.transmission overnight_parking_provision,
m6.transmission overnight_parking_location,
v.odometerreading,
v.dateofodometerreading,
(SELECT count(*) FROM vehiclemodifications vmof
WHERE vmof.vehicleid(+) = v.id) numofmodifications,
(SELECT count(*) FROM vehiclesecuritydevice vsd
WHERE vsd.vehicleid(+) = v.id) nomofsecuritydevices,
vu.annualdistancepleasure,
vu.annualdistancebusiness,
v.purchaseprice,
v.currentvalue,
a.addressline1,
a.addressline2,
a.addressline3,
a.addressline4,
a.postcode
FROM vehicle v,
vehiclemodel vm,
vehiclemanufacturer vma,
vehicleusage vu,
mktlistitem m,
mktlistitem m2,
mktlistitem m3,
mktlistitem m4,
mktlistitem m5,
mktlistitem m6,
address a
WHERE v.modelcd = vm.abicd
AND vm.vehiclemanufid = vma.vmabicd
AND v.vehicleusageid = vu.id
AND (vm.transtypedc = m.abicd
AND m.mktlistnameid = 49)
AND (vm.fueltypedc = m2.abicd
AND m2.mktlistnameid = 45)
AND (to_char(v.bodystylecd) = m3.abicd
AND m3.mktlistnameid = 55)
AND (v.paintworkcolourcd = m4.abicd
AND m4.mktlistnameid = 9)
AND (to_char(vu.overnightparkingprovisioncd) = m5.abicd
AND m5.mktlistnameid = 12)
AND (to_char(vu.overnightparkinglocationcd) = m6.abicd
AND m6.mktlistnameid = 1012)
AND a.id = vu.addressid;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=293 Card=3 Bytes=963)
1 0 HASH JOIN (Cost=293 Card=3 Bytes=963)
2 1 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
3 1 HASH JOIN (Cost=281 Card=122 Bytes=36112)
4 3 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
5 3 HASH JOIN (Cost=269 Card=122 Bytes=33062)
6 5 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
7 5 HASH JOIN (Cost=257 Card=122 Bytes=30012)
8 7 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
9 7 HASH JOIN (Cost=245 Card=122 Bytes=26962)
10 9 TABLE ACCESS (FULL) OF 'VEHICLEMANUFACTURER' (Cost=1 Card=356 Bytes=3560)
11 9 HASH JOIN (Cost=243 Card=122 Bytes=25742)
12 11 TABLE ACCESS (FULL) OF 'VEHICLEMODEL' (Cost=67 Card=37352 Bytes=1157912)
13 11 HASH JOIN (Cost=87 Card=8771 Bytes=1578780)
14 13 TABLE ACCESS (FULL) OF 'ADDRESS' (Cost=1 Card=159 Bytes=9063)
15 13 HASH JOIN (Cost=85 Card=8771 Bytes=1078833)
16 15 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=1 Card=123 Bytes=7626)
17 15 MERGE JOIN (CARTESIAN) (Cost=83 Card=7416 Bytes=452376)
18 17 TABLE ACCESS (FULL) OF 'MKTLISTITEM' (Cost=11 Card=72 Bytes=1800)
19 17 SORT (JOIN) (Cost=72 Card=103 Bytes=3708)
20 19 TABLE ACCESS (FULL) OF 'VEHICLEUSAGE' (Cost=1 Card=103 Bytes=3708)

[alison]

Sorry to ping my own thread but if anyone could help with this it would be greatly appreciated.

Allie