Converting Informix to SQL Server

I have the following statement that is from Informix, I want to convert it into code that will compile in SQL 2000. I dont know how to do this because it has a subquery. Any help is appreciated. Create view ps_sd_bom_dtl_vw (setid,product_id,prod_component_id,qty_per,descr254,business_unit,list_price,unit_of_measure, current_cost,descr60,descr,descrshort,product_group,mfg_itm_id) as select x0.setid ,x0.product_id ,x1.prod_component_id ,x1.qty_per ,x2.descr254 ,x5.business_unit ,x3.list_price ,x1.unit_of_measure ,x5.current_cost ,x6.descr60 ,x8.descr ,x8.descrshort , x4.product_group ,x9.mfg_itm_id from ps_prodkit_header x0 ,ps_prodkit_comps x1 ,ps_prod_item x2 ,ps_prod_price x3 , ps_prod_pgrp_lnk x4 ,ps_bu_items_inv x5 ,ps_master_item_tbl x6 ,ps_inv_items x7 ,ps_prod_group_tbl x8 ,outer(ps_item_mfg x9 ) where ((((((((((((((((((((x0.setid = x1.setid ) AND (x0.product_id = x1.product_id ) ) AND (x1.setid = x2.setid ) ) AND (x1.product_id = x2.product_id ) ) AND (x2.setid = x3.setid ) ) AND (x2.product_id = x3.product_id ) ) AND (x3.effdt = (select max(x10.effdt ) from ps_prod_price x10 where (((((x3.setid = x10.setid ) AND (x3.product_id = x10.product_id ) ) AND (x3.unit_of_measure = x10.unit_of_measure ) ) AND (x3.business_unit_in = x10.business_unit_in ) ) AND (x10.effdt <= CURRENT_TIMESTAMP ) ) ) ) ) AND (x3.setid = x4.setid ) ) AND (x1.prod_component_id = x4.product_id ) ) AND (x5.inv_item_id = x6.inv_item_id ) ) AND (x6.setid = x7.setid ) ) AND (x6.inv_item_id = x7.inv_item_id ) ) AND (x7.effdt = (select max(x11.effdt ) from ps_inv_items x11 where (((x7.setid = x11.setid ) AND (x7.inv_item_id = x11.inv_item_id ) ) AND (x11.effdt <= CURRENT_TIMESTAMP ) ) ) ) ) AND (x5.inv_item_id = x1.prod_component_id ) ) AND (x4.prod_grp_type = 'RPT' ) ) AND (x4.setid = x8.setid ) ) AND (x4.prod_grp_type = x8.prod_grp_type ) ) AND (x4.product_group = x8.product_group ) ) AND (x8.effdt = (select max(x12.effdt ) from ps_prod_group_tbl x12 where ((((x8.setid = x12.setid ) AND (x8.prod_grp_type = x12.prod_grp_type ) ) AND (x8.product_group = x12.product_group ) ) AND (x12.effdt <= CURRENT_TIMESTAMP ) ) ) ) ) AND (x5.inv_item_id = x9.inv_item_id ) ) GO

barhoc11@gmail.com wrote: >I dont know how to do this because it has a subquery. I haven't dissected the entire statement but having a sub-query per se is not an issue. The first issue I'd tackle is that "outer(ps_item_mfg x9 )" isn't supported (outer is informix-extension syntax). Suggest you replace it with a SQL-92 compliant statement e.g. LEFT JOIN ps_item_mfg x9 ON x5.inv_item_id = x9.inv_item_id (assuming you want to join "ps_item_mfg" to "ps_bu_items_inv" on "inv_item_id" and that you want all the rows from "ps_item_mfg" regardless of whether there's a matching row in "ps_bu_items_inv") Also, this means that you could remove one of those 'ands' from the where clause !

P.S. the 'outer' issue is exactly the same as solved in your other topic.