Link to home
Start Free TrialLog in
Avatar of am-notifications
am-notifications

asked on

SQL in VB6

I have a sql statement in VB6 (com objects) that will not work. Does anyone know if the Right outer join that I'm using here is ok in VB6?
When I run this in Toad I get back a record, however when I put it in vb as this

       lsSQL = "select al.Nbr_license, al.return_received, p.resident, p.name_first,  p.initial1, p.name_last FULLNAME, " & _
           "p.addr1, p.addr2, p.addr3 ADDRESS, al.trap_line_id, al.fur_area_id, " & _
           "al.nf_beaver_trapline , al.gen_trap_license, al.nf_renew_trapline, al.lab_beaver_license, fmz.fur_area_desc, tl.trap_line_name, al.rowversion " & _
           "from dba_wims.application_licens al, dba_wims.person p, dba_wims.fur_manage_zones fmz, dba_wims.trap_line  tl " & _
           "Where p.person_id = al.person_id " & _
           "AND fmz.fur_area_id(+) = al.fur_area_id " & _
           "and tl.nbr_trap_line(+)= al.trap_line_id " & _
           "and tl.nbr_fur_area(+) =  al.fur_area_id " & _
           "and al.Nbr_license = " & nbr_license & " "
I get an error
error '80040e07'
/trapperReturn.asp, line 96
I looked up this error and it says data type mismatch but I'm only selecting.

Avatar of am-notifications
am-notifications

ASKER

I think I may have to use "Join" instead of (+) but I'm not sure how to do this.
Avatar of Dirk Haest
1. Are all the linked field the same tyep ?
2. How are the relations between the tables ? (from which table do you need all the data even if there is no data in it)
There are 4 tables
application_licens al, person p, fur_manage_zones fmz, trap_line  tl
(al) is connected to( p) by person_id field
(fmz) is connected to( al) by fur_area_id
(tl) is connected to (al) by ( tl.nbr_trap_line/ al.trap_line_id) and (tl.nbr_fur_area/ al.fur_area_id)
There will always be data in the (al) table but I need data from (fmz) and (tl) even if there's no data.

the linked fields are the same datatype
just to reiterate - I think I may have gotten it backwards above.
 there may be more rows in 'al' than there are in 'fmz' and 'tl' and I want to see them even if there's no match in the'fmz' and 'tl' table.
If you are using + to indicated a Left Join, then the correct syntax should be:

lsSQL = "select al.Nbr_license, al.return_received, p.resident, p.name_first,  p.initial1, p.name_last FULLNAME, " & _
           "p.addr1, p.addr2, p.addr3 ADDRESS, al.trap_line_id, al.fur_area_id, " & _
           "al.nf_beaver_trapline , al.gen_trap_license, al.nf_renew_trapline, al.lab_beaver_license, fmz.fur_area_desc, tl.trap_line_name, al.rowversion " & _
           "from dba_wims.application_licens al, dba_wims.person p, dba_wims.fur_manage_zones fmz, dba_wims.trap_line  tl " & _
           "Where p.person_id = al.person_id " & _
           "AND fmz.fur_area_id *= al.fur_area_id " & _
           "and tl.nbr_trap_line *= al.trap_line_id " & _
           "and tl.nbr_fur_area *=  al.fur_area_id " & _
           "and al.Nbr_license = " & nbr_license & " "

What datebase are you using?

Leon
oracle 8
ASKER CERTIFIED SOLUTION
Avatar of leonstryker
leonstryker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the grade,

Leon