I need help in converting Sybase SQL to Oracle SQL. I tried doing that 

myself but I dont get the right recordset back as I do from Sybase SQL. And 

both the databases have same records in all tables. Just migrated everything 

from Sybase to Oracle.



I am working on database migration project. The Code was written by some 

other company. Now, I am stuck with SQL statement in ASP code.





Any help will be highly appreciated. Thanks.



SYBASE SQL =>

select distinct att.attribute_name, av.asset_id, av.census_id, 

av.asset_name, mt.downloadable as mime_downloadable, av.downloadable, 

av.short_description, av.object, av.obj_preview, mt.icon, mt.new_icon, 

av.last_update, mt.mime_type, mt.method_id, av.asset_size, 

av.long_description, att.attribute_id, lang.attribute_name as attr_lang from 

Asset av,MimeType mt,GroupAsset ga, AssetAttribute aa2, AssetAttribute aa3, 

AssetAttribute aa4, Attribute att, Attribute lang

where av.approved=1 and av.active=1 and av.archive=0 and

(getdate() between av.effective_date and av.end_date or getdate() >= 

av.effective_date and av.end_date is null)

and av.mime_type_id=mt.mime_type_id

and av.asset_id = ga.asset_id and ga.group_id IN (1,5271)

and ga.asset_id = aa2.asset_id and av.asset_id *= aa3.asset_id

and av.asset_id = aa4.asset_id  and aa4.attribute_id = lang.attribute_id

and lang.attr_type_id = 3813  and aa2.attribute_id = 4357

and aa3.attribute_id = -1 and aa2.attribute_id = att.attribute_id

order by av.last_update desc, av.short_description

</END OF SYBASE SQL>







My version of ORACLE SQL =>



select distinct att.attribute_name, av.asset_id, av.census_id, 

av.asset_name, mt.downloadable as mime_downloadable, av.downloadable, 

av.short_description, av.object, av.obj_preview, mt.icon,mt.new_icon, 

av.last_update, mt.mime_type, mt.method_id, av.asset_size, 

av.long_description, att.attribute_id, lang.attribute_name as attr_lang

from Asset av,MimeType mt,GroupAsset ga, AssetAttribute aa2, AssetAttribute 

aa3, AssetAttribute aa4, Attribute att, Attribute lang

where av.approved=1 and av.active=1 and av.archive=0 and

(sysdate between av.effective_date and av.end_date or sysdate >= 

av.effective_date and av.end_date is null)

and av.mime_type_id=mt.mime_type_id

and av.asset_id = ga.asset_id and ga.group_id IN (1,5271)

and ga.asset_id = aa2.asset_id and av.asset_id(+) = aa3.asset_id

and av.asset_id = aa4.asset_id  and aa4.attribute_id = lang.attribute_id

and lang.attr_type_id = 3813  and aa2.attribute_id = 4357

and aa3.attribute_id = -1 and aa2.attribute_id = att.attribute_id

order by av.last_update desc, av.short_description

</END OF ORACLE SQL>