SQL in VB6

am-notifications
am-notifications used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I think I may have to use "Join" instead of (+) but I'm not sure how to do this.
Dirk HaestProject manager

Commented:
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)

Author

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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

Author

Commented:
oracle 8
You may want to try 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 " & _
           "JOIN dba_wims.person p " & _
               "ON p.person_id = al.person_id " & _
           "RIGHT JOIN dba_wims.fur_manage_zones fmz " & _
               "ON fmz.fur_area_id = al.fur_area_id " & _
           "RIGHT JOIN dba_wims.trap_line  tl " & _
               "ON tl.nbr_trap_line = al.trap_line_id " & _
               "AND tl.nbr_fur_area =  al.fur_area_id " & _
           "AND al.Nbr_license = " & nbr_license


Leon
Thanks for the grade,

Leon

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial