oracle11g - PL SQL Retrieve Table data to XML TYPE -
i wanted know why data not filter in 3rd level 2nd level primary key. in first level i'm passing item id prefno after i'm retrieving data according item_id. 2nd level finding primary key of level_id using item_id , retrieving dimensions according level_id. dimensions getting repeating mean in filtering part not working. when run dimensions query along not repeating. adding same query en trier xmltype query getting repeated
select xmlagg(xmlelement ("uomdimensions", xmlforest ("dimensionid" "dimensionid", "dimensionname" "dimensionname", "dimensionvalue" "dimensionvalue"))) (select p.dimension_id "dimensionid", p.dimension_desc "dimensionname", 0 "dimensionvalue" bizzxe_v2_sch.dimension_settings o, bizzxe_v2_sch.dimensions p p.dimension_id = o.dimension_id , o.setting_property_id = 42 , o.setting_value = 1 , p.dimension_id not in ( select p.dimension_id bizzxe_v2_sch.dimensions p, bizzxe_v2_sch.item_uom_level_dimnsions q, bizzxe_v2_sch.item_uom_levels x, bizzxe_v2_sch.items a.item_id = x.item_id , x.level_id = q.level_id -- , x.level_id = k.level_id , q.dimension_id = p.dimension_id , a.item_id = pref_no ) union select p.dimension_id "dimensionid", p.dimension_desc "dimensionname", q.dimension_value "dimensionvalue" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_uom_levels z, bizzxe_v2_sch.dimensions p, bizzxe_v2_sch.item_uom_level_dimnsions q a.item_id = pref_no , a.item_id = z.item_id , q.dimension_id = p.dimension_id -- , q.level_id = k.level_id , q.level_id = z.level_id)) this whole query
select xmlelement ( "object", xmlelement ( "item", xmlforest( 1 "locationid", a.item_id "id", a.item_id "itemid", a.remarks "remarks", a.code "itemcode", a.name "itemdescription", a.short_name "itemshortname", a.hs_code_id "hscodeid", b.hs_code "hscodename", a.brand_id "itembrandid", c.brand_description "itembrandname", a.cat_id "categoryid", d.item_cat_desc "categoryname", a.variant_temp_id "varianttemplateid", e.variant_temp_name "varinattemplatename", (select xmlagg(xmlelement ("itemuomlevels", xmlforest (k.uom_level "uomlevelid", k.uom_id "uomid", l.description "uom", k.long_name "uomlongname", k.conv_factr "conversionfactor", k.status_id "uomlevelstatus", k.sys_usage_id "uomlevelsystemusagetypeid", h.sys_usage_name "uomlevelsystemusagetype", k.image "uomlevelimage", k.uom_level_volume "uomlevelvolume", (select unique case when s.item_id<>0 1 end "uomleveleditable" bizzxe_v2_sch.po_request_items s s.item_id = pref_no , s.uom_id= l.uom_id) "uomleveleditable", (select xmlagg(xmlelement ("uomdimensions", xmlforest ("dimensionid" "dimensionid", "dimensionname" "dimensionname", "dimensionvalue" "dimensionvalue"))) (select p.dimension_id "dimensionid", p.dimension_desc "dimensionname", 0 "dimensionvalue" bizzxe_v2_sch.dimension_settings o, bizzxe_v2_sch.dimensions p p.dimension_id = o.dimension_id , o.setting_property_id = 42 , o.setting_value = 1 , p.dimension_id not in ( select p.dimension_id bizzxe_v2_sch.dimensions p, bizzxe_v2_sch.item_uom_level_dimnsions q, bizzxe_v2_sch.item_uom_levels x, bizzxe_v2_sch.items a.item_id = x.item_id , x.level_id = q.level_id -- , x.level_id = k.level_id , q.dimension_id = p.dimension_id , a.item_id = pref_no ) union select p.dimension_id "dimensionid", p.dimension_desc "dimensionname", q.dimension_value "dimensionvalue" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_uom_levels z, bizzxe_v2_sch.dimensions p, bizzxe_v2_sch.item_uom_level_dimnsions q a.item_id = pref_no , a.item_id = z.item_id , q.dimension_id = p.dimension_id -- , q.level_id = k.level_id , q.level_id = z.level_id )) "uomdimensionslist"))) bizzxe_v2_sch.item_uom_levels k, bizzxe_v2_sch.uoms l, bizzxe_v2_sch.item_system_usages h a.item_id = pref_no , a.item_id = k.item_id , k.sys_usage_id = h.sys_usage_id , k.uom_id = l.uom_id)as "itemuomlevelslist"))) varxml bizzxe_v2_sch.items a, bizzxe_v2_sch.hs_codes b, bizzxe_v2_sch.brands c, bizzxe_v2_sch.item_categories d, bizzxe_v2_sch.variant_templates e a.item_id = pref_no , a.hs_code_id = b.hs_code_id , c.brand_id = a.brand_id , d.item_cat_id = a.cat_id , a.variant_temp_id = e.variant_temp_id;
select xmlelement ( "object", xmlelement ( "item", xmlforest( 1 "locationid", a.item_id "id", a.item_id "itemid", a.remarks "remarks", a.code "itemcode", a.name "itemdescription", a.short_name "itemshortname", a.hs_code_id "hscodeid", b.hs_code "hscodename", a.brand_id "itembrandid", c.brand_description "itembrandname", a.cat_id "categoryid", d.item_cat_desc "categoryname", a.variant_temp_id "varianttemplateid", e.variant_temp_name "varinattemplatename", (select xmlagg(xmlelement ("allocationtypes", xmlforest ("allocationtypeid" "allocationtypeid", "allocationvalue" "allocationvalue", "allocationtype" "allocationtype", "allocationsystemusagetypeid" "allocationsystemusagetypeid", "allocationsystemusagetype" "allocationsystemusagetype"))) (select f.allocation_id "allocationtypeid", f.value "allocationvalue", g.allocation_desc "allocationtype", f.sys_usage_id "allocationsystemusagetypeid", h.sys_usage_name "allocationsystemusagetype" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_allocations f, bizzxe_v2_sch.allocations g, bizzxe_v2_sch.item_system_usages h a.item_id = f.item_id , a.item_id = pref_no , f.sys_usage_id = h.sys_usage_id , g.allocation_id = f.allocation_id union select g.allocation_id "allocationtypeid", 0 "allocationvalue", g.allocation_desc "allocationtype", 0 "allocationsystemusagetypeid", null "allocationsystemusagetype" bizzxe_v2_sch.allocations g g.allocation_id not in (select g.allocation_id "allocationtypeid" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_allocations g a.item_id = g.item_id , a.item_id = pref_no)))as "allocationtypeslist", (select xmlagg(xmlelement ("variantdimensionmatrix", xmlforest ("skuprefixcode" "skuprefixcode", "skuprefixdescription" "skuprefixdescription", "validsku" "skuselected"))) ( select i.sku_prefix_code "skuprefixcode", i.sku_description "skuprefixdescription", '0' "validsku" bizzxe_v2_sch.items a, bizzxe_v2_sch.variant_temp_dimensions i, bizzxe_v2_sch.variant_templates e i.variant_temp_id = e.variant_temp_id , i.variant_temp_id = a.variant_temp_id , a.item_id = pref_no , i.sku_prefix_code not in ( select j.sku_prefix_code "skuprefixcode" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_varints j a.item_id = j.item_id , a.item_id = pref_no) union select j.sku_prefix_code "skuprefixcode", j.sku_prefix_desc "skuprefixdescription", j.valid_sku "validsku" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_varints j a.item_id = pref_no , a.item_id = j.item_id))as "variantdimensionmatrixlist", (select xmlagg(xmlelement ("itemuomlevels", xmlforest (k.uom_level "uomlevelid", k.uom_id "uomid", l.description "uom", k.long_name "uomlongname", k.conv_factr "conversionfactor", k.status_id "uomlevelstatus", k.sys_usage_id "uomlevelsystemusagetypeid", h.sys_usage_name "uomlevelsystemusagetype", k.image "uomlevelimage", k.uom_level_volume "uomlevelvolume", (select unique case when s.item_id<>0 1 end "uomleveleditable" bizzxe_v2_sch.po_request_items s s.item_id = pref_no , s.uom_id= l.uom_id) "uomleveleditable", (select xmlagg(xmlelement ("uomlevelcontrols", xmlforest (m.control_id "controlid", m.control_name "controlname"))) bizzxe_v2_sch.items a, bizzxe_v2_sch.uom_controls m, bizzxe_v2_sch.item_uom_level_controls n,bizzxe_v2_sch.item_uom_levels y a.item_id = pref_no , a.item_id = k.item_id , k.level_id = n.level_id , y.level_id = n.level_id , n.control_id = m.control_id) "uomlevelcontrolslist", (select xmlagg(xmlelement ("uomdimensions", xmlforest ("dimensionid" "dimensionid", "dimensionname" "dimensionname", "dimensionvalue" "dimensionvalue", "level" "level"))) (select p.dimension_id "dimensionid", p.dimension_desc "dimensionname", 0 "dimensionvalue", 0 "level" bizzxe_v2_sch.dimension_settings o, bizzxe_v2_sch.dimensions p p.dimension_id = o.dimension_id , o.setting_property_id = 42 , o.setting_value = 1 , p.dimension_id not in ( select p.dimension_id bizzxe_v2_sch.dimensions p, bizzxe_v2_sch.item_uom_level_dimnsions q, bizzxe_v2_sch.item_uom_levels x, bizzxe_v2_sch.items a.item_id = x.item_id , x.level_id = q.level_id , a.item_id = pref_no ) union select p.dimension_id "dimensionid", p.dimension_desc "dimensionname", q.dimension_value "dimensionvalue", q.level_id "level" bizzxe_v2_sch.items a, bizzxe_v2_sch.item_uom_levels z, bizzxe_v2_sch.dimensions p, bizzxe_v2_sch.item_uom_level_dimnsions q a.item_id = pref_no , a.item_id = z.item_id , q.dimension_id = p.dimension_id , q.level_id = z.level_id) dual k.level_id="level") "uomdimensionslist"))) bizzxe_v2_sch.item_uom_levels k, bizzxe_v2_sch.uoms l, bizzxe_v2_sch.item_system_usages h a.item_id = pref_no , a.item_id = k.item_id , k.sys_usage_id = h.sys_usage_id , k.uom_id = l.uom_id)as "itemuomlevelslist"))) varxml bizzxe_v2_sch.items a, bizzxe_v2_sch.hs_codes b, bizzxe_v2_sch.brands c, bizzxe_v2_sch.item_categories d, bizzxe_v2_sch.variant_templates e a.item_id = pref_no , a.hs_code_id = b.hs_code_id , c.brand_id = a.brand_id , d.item_cat_id = a.cat_id , a.variant_temp_id = e.variant_temp_id;
Comments
Post a Comment