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_licen s 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.
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_licen
"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.
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)
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)
ASKER
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
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
ASKER
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.
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_licen s 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
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_licen
"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
ASKER
oracle 8
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade,
Leon
Leon
ASKER