Confusing sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Confusing sql

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200

    Confusing sql

    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)
    What's the sound of one hand clapping - "CL"

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that is because you are joining a varchar2 to a number and Oracle is applying an implicit to_number in your varchar2 column

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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..
    What's the sound of one hand clapping - "CL"

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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
    What's the sound of one hand clapping - "CL"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think it was the original designer who had the blonde moment. Or senior moment.

    Code:
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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)
    What's the sound of one hand clapping - "CL"

  8. #8
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Sorry to ping my own thread but if anyone could help with this it would be greatly appreciated.

    Allie
    What's the sound of one hand clapping - "CL"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width