Need Help with SQL to VB conversion

[khader82]

Hey there,

I am in the process of converting a few of the SQL queries into VB for the automation process..The following is the SQL query

select a.field1,b.field2,c.fileld3
from
a,b,c
where
a.field1 *= b.field2

I am not sure of how to convert the data that is bolded into VB code.also
am not sure what it means..

Can someone please help

[si_the_geek]

I think that means 'left outer join' for some databases, so presumably this is the equivalent:

select a.field1,b.field2,c.fileld3
from a Left Outer Join b on (a.field1 = b.field2), c

Note that table c does not have a relationship specified (as it didn't in your example), so you will get a cartesian product.

[khader82]

Hey thanks for the reply.. but this doesnt seem to work i am putting down the actual query in SQL Server and the VB version with the table name..

SQL Server query -

select
cus_name,
rou_name,
st_sub_type,
ct_name,
rou_usid,
ps_libelle,
it_name,
at_libelle,
int_ip_address

from
router,
planning,
chassis_type,
hardware,
sita_type, interface,
access_type,
provisioning_status,
customer,
interface_type

where
har_rou_pk = rou_pk
and har_ct_pk = ct_pk
and rou_pla_pk = pla_pk
and pla_ps_pk = ps_pk
and int_at_pk *= at_pk
and int_rou_pk = rou_pk
and rou_cus_pk = cus_pk
and cus_sita_nb in (39975)
and rou_st_pk = st_pk
and rou_last = 1
and int_it_pk = it_pk
and ps_libelle <> 'OUT'


This is what i have with VB so far .. i have problem with tht * (bolded) how do i get the equivalent in VB..

"select customer.cus_name,router.rou_name,sita_type.st_sub_type,chassis_type.ct_name,router.rou_usid,provisi oning_status.ps_libelle,interface_type.it_name,access_type.at_libelle,interface.int_ip_address from router,planning,chassis_type,hardware,sita_type,interface,access_type,provisioning_status,customer,i nterface_type where hardware.har_rou_pk = router.rou_pk and hardware.har_ct_pk = chassis_type.ct_pk and router.rou_pla_pk = planning.pla_pk and planning.pla_ps_pk = provisioning_status.ps_pk and interface.int_at_pk = access_type.at_pk and interface.int_rou_pk = router.rou_pk and router.rou_cus_pk = customer.cus_pk and customer.cus_sita_nb in (39975) and router.rou_st_pk = site_type.st_pk and router.rou_last = 1 and interface.int_it_pk = interface_type.it_pk and provisioning_status.ps_libelle <> 'OUT' "


Please help me out with this.

Thanks for ur time..

[si_the_geek]

Which version of SQL Server are you using? (I didn't think it supported joins like that )

Have you tried using the original query from VB?

[khader82]

The SQL SERVER QUERY was generated frm MSQRY32 ..

select
cus_name,
rou_name,
st_sub_type,
ct_name,
rou_usid,
ps_libelle,
it_name,
at_libelle,
int_ip_address

from
router,
planning,
chassis_type,
hardware,
sita_type, interface,
access_type,
provisioning_status,
customer,
interface_type

where
har_rou_pk = rou_pk
and har_ct_pk = ct_pk
and rou_pla_pk = pla_pk
and pla_ps_pk = ps_pk
and int_at_pk *= at_pk
and int_rou_pk = rou_pk
and rou_cus_pk = cus_pk
and cus_sita_nb in (39975)
and rou_st_pk = st_pk
and rou_last = 1
and int_it_pk = it_pk
and ps_libelle <> 'OUT'





and the VB query is what i wrote..

"select customer.cus_name,router.rou_name,sita_type.st_sub_type,chassis_type.ct_name,router.rou_usid,provisi oning_status.ps_libelle,interface_type.it_name,access_type.at_libelle,interface.int_ip_address from router,planning,chassis_type,hardware,sita_type,interface,access_type,provisioning_status,customer,i nterface_type where hardware.har_rou_pk = router.rou_pk and hardware.har_ct_pk = chassis_type.ct_pk and router.rou_pla_pk = planning.pla_pk and planning.pla_ps_pk = provisioning_status.ps_pk and interface.int_at_pk = access_type.at_pk and interface.int_rou_pk = router.rou_pk and router.rou_cus_pk = customer.cus_pk and customer.cus_sita_nb in (39975) and router.rou_st_pk = site_type.st_pk and router.rou_last = 1 and interface.int_it_pk = interface_type.it_pk and provisioning_status.ps_libelle <> 'OUT' "

[khader82]

Helloo... any one there to help me out with this ??

[si_the_geek]

Have you got answers to my questions?

[khader82]

hey MOD !!! thats what i said..i dont have any VB queries with me..i only have query frm SQLQRY32.exe .. i need the equivalent in VB..

[khader82]

Oops it MSQRY32.exe

[si_the_geek]

You still have not even come close to answering my questions.

I don't care that you are using MS Query to execute queries, what matters is the version of SQL Server that you are using. If you cant find out, execute this SQL statement and tell us the results:

Select @@version

Have you tried adjusting your query to use the "LEFT OUTER JOIN" syntax that I posted?