Need Equalent MS Access query for an Oracle Query

[prab.raja]

This is a Oracle query... its working in Oracle but didnt work in access...

select v.code_number,v.vehicle_number,v.company_code,r.fc_valid_to,
i.next_due_date
from (vehicledetails v left outer join rtodetails r
on v.code_number=r.code_number AND v.code_number='SMR5'
left outer join insurancedetails i
on v.code_number=i.code_number);


I need equalent Access query for this.... anyone plz help....

 

[KeithG]

Try this

select v.code_number,v.vehicle_number,v.company_code,r.fc _valid_to,
i.next_due_date
from ((vehicledetails as v left outer join rtodetails as r
on v.code_number=r.code_number) left outer join insurancedetails as i
on v.code_number=i.code_number) Where v.code_number=’SMR5’

 

[prab.raja]

Thanks... But not Giving Expected result....

Try this

select v.code_number,v.vehicle_number,v.company_code,r.fc _valid_to,
i.next_due_date
from ((vehicledetails as v left outer join rtodetails as r
on v.code_number=r.code_number) left outer join insurancedetails as i
on v.code_number=i.code_number) Where v.code_number=’SMR5’

Thanks...
But its not giving Expected result....

Original Oracle query:

select v.code_number,v.vehicle_number,v.company_code,r.fc _valid_to,
i.next_due_date
from (vehicledetails v left outer join rtodetails r
on v.code_number=r.code_number AND v.code_number='SMR5'
left outer join insurancedetails i
on v.code_number=i.code_number);

Actually this ll give all the records from vehicledetails and give records from rtodetails which satisfies both condition(v.code_number=r.code_number AND v.code_number='SMR5') if condition not satisfied it ll return null for the column 'r.fc _valid_to'..(tat is 'r.fc _valid_to' contains value only for v.code_number='SMR5' for all other records it would b null) and also include records from insurancedetails which satisfies v.code_number=i.code_number
--------------------------------------------------------------------------
select v.code_number,v.vehicle_number,v.company_code,r.fc _valid_to,
i.next_due_date
from ((vehicledetails as v left outer join rtodetails as r
on v.code_number=r.code_number) left outer join insurancedetails as i
on v.code_number=i.code_number) Where v.code_number=’SMR5’

but this ll give all the records from vehicledetails and give records from rtodetails which satisfies v.code_number=r.code_number if condition not satisfied it ll return null for the column 'r.fc _valid_to'..(tat is 'r.fc _valid_to' contains value not only for v.code_number='SMR5') and also include records from insurancedetails which satisfies v.code_number=i.code_number then it ll filter the result for v.code_number='SMR5'(tat is it ll return only one row 'SMR5' only)

 

[KeithG]

Can you explain what result set you want the SQL to return. I did not understand the explaination above. In your original query it looks like you supplied criteria in your JOIN expression but I put this as criteria in the WHERE clause and I think that is what is causing your problem. I think you may need to create a seperate query for all records with a code_number of SMS5 and use that in your SQL instead of the actual table. But I am not too clear on what result set you want returned.

 

[Bungle]

You need a sub-query to handle the WHERE statement. I think this should do what you want:

select z.code_number, z.vehicle_number, z.company_code, z.fc_valid_to, i.next_due_date from
((select v.code_number, v.vehicle_number, v.company_code, r.fc_valid_to
from vehicledetails as v left join rtodetails as r
on v.code_number=r.code_number where v.code_number='SMR5') as z
left outer join insurancedetails as i
on z.code_number=i.code_number);

 

[prab.raja]

Thanks to all....
i got it....

the working query is below...


SELECT x.code_number, x.vehicle_number, x.company_code, z.fc_valid_to, y.next_due_date
FROM ([select code_number, vehicle_number, company_code from vehicledetails]. AS x LEFT JOIN [SELECT v.code_number, r.fc_valid_to
FROM vehicledetails AS v LEFT JOIN rtodetails AS r ON v.code_number=r.code_number where v.code_number='SMR5']. AS z ON x.code_number=z.code_number) LEFT JOIN [SELECT v.code_number, i.next_due_date
FROM vehicledetails AS v inner JOIN insurancedetails AS i ON v.code_number=i.code_number where i.code_number='smr5']. AS y ON x.code_number=y.code_number;