I wanted to know why my data is not filter in 3rd level by 2nd level primary key. In first level i'm passing Item Id as a PREFNO then after i'm retrieving data according to ITEM_ID. 2nd level finding primary key of LEVEL_ID using ITEM_ID and retrieving Dimensions according to LEVEL_ID. But my Dimensions getting repeating its mean in filtering part is not working. when i run my Dimensions query along its not repeating. but adding the same query to en trier xmltype query its getting repeated
SELECT XMLAGG(XMLELEMENT ("UomDimensions",
XMLFOREST ("DimensionId" AS "DimensionId",
"DimensionName" AS "DimensionName",
"DimensionValue" AS "DimensionValue")))
FROM (SELECT P.DIMENSION_ID AS "DimensionId",
P.DIMENSION_DESC AS "DimensionName",
0 AS "DimensionValue"
FROM BIZZXE_V2_SCH.DIMENSION_SETTINGS O, BIZZXE_V2_SCH.DIMENSIONS P
WHERE P.DIMENSION_ID = O.DIMENSION_ID
AND O.SETTING_PROPERTY_ID = 42
AND O.SETTING_VALUE = 1
AND P.DIMENSION_ID NOT IN (
SELECT P.DIMENSION_ID
FROM 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
WHERE A.ITEM_ID = X.ITEM_ID
AND X.LEVEL_ID = Q.LEVEL_ID
-- AND X.LEVEL_ID = K.LEVEL_ID
AND Q.DIMENSION_ID = P.DIMENSION_ID
AND A.ITEM_ID = PREF_NO )
UNION
SELECT P.DIMENSION_ID AS "DimensionId",
P.DIMENSION_DESC AS "DimensionName",
Q.DIMENSION_VALUE AS "DimensionValue"
FROM 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
WHERE A.ITEM_ID = PREF_NO
AND A.ITEM_ID = Z.ITEM_ID
AND Q.DIMENSION_ID = P.DIMENSION_ID
-- AND Q.LEVEL_ID = K.LEVEL_ID
AND Q.LEVEL_ID = Z.LEVEL_ID))
This is my whole query
SELECT
XMLELEMENT ( "OBJECT",
XMLELEMENT ( "Item",
XMLFOREST( 1 AS "LocationId",
A.ITEM_ID AS "Id",
A.ITEM_ID AS "ItemId",
A.REMARKS AS "Remarks",
A.CODE AS "ItemCode",
A.NAME AS "ItemDescription",
A.SHORT_NAME AS "ItemShortName",
A.HS_CODE_ID AS "HsCodeId",
B.HS_CODE AS "HsCodeName",
A.BRAND_ID AS "ItemBrandId",
C.BRAND_DESCRIPTION AS "ItemBrandName",
A.CAT_ID AS "CategoryId",
D.ITEM_CAT_DESC AS "CategoryName",
A.VARIANT_TEMP_ID AS "VariantTemplateId",
E.VARIANT_TEMP_NAME AS "VarinatTemplateName",
(SELECT
XMLAGG(XMLELEMENT ("ItemUomLevels",
XMLFOREST (K.UOM_LEVEL AS "UomLevelId",
K.UOM_ID AS "UomId",
L.DESCRIPTION AS "Uom",
K.LONG_NAME AS "UomLongName",
K.CONV_FACTR AS "ConversionFactor",
K.STATUS_ID AS "UomLevelStatus",
K.SYS_USAGE_ID AS "UomLevelSystemUsageTypeId",
H.SYS_USAGE_NAME AS "UomLevelSystemUsageType",
K.IMAGE AS "UomLevelImage",
K.UOM_LEVEL_VOLUME AS "UomLevelVolume",
(SELECT
UNIQUE CASE
WHEN S.ITEM_ID<>0 THEN 1
END AS "UomLevelEditable"
FROM
BIZZXE_V2_SCH.PO_REQUEST_ITEMS S
WHERE
S.ITEM_Id = PREF_NO
AND S.UOM_ID= L.UOM_ID) AS "UomLevelEditable",
(SELECT
XMLAGG(XMLELEMENT ("UomDimensions",
XMLFOREST ("DimensionId" AS "DimensionId",
"DimensionName" AS "DimensionName",
"DimensionValue" AS "DimensionValue")))
FROM
(SELECT
P.DIMENSION_ID AS "DimensionId",
P.DIMENSION_DESC AS "DimensionName",
0 AS "DimensionValue"
FROM
BIZZXE_V2_SCH.DIMENSION_SETTINGS O,
BIZZXE_V2_SCH.DIMENSIONS P
WHERE
P.DIMENSION_ID = O.DIMENSION_ID
AND O.SETTING_PROPERTY_ID = 42
AND O.SETTING_VALUE = 1
AND P.DIMENSION_ID NOT IN ( SELECT
P.DIMENSION_ID
FROM
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
WHERE
A.ITEM_ID = X.ITEM_ID
AND X.LEVEL_ID = Q.LEVEL_ID
-- AND X.LEVEL_ID = K.LEVEL_ID
AND Q.DIMENSION_ID = P.DIMENSION_ID
AND A.ITEM_ID = PREF_NO )
UNION
SELECT
P.DIMENSION_ID AS "DimensionId",
P.DIMENSION_DESC AS "DimensionName",
Q.DIMENSION_VALUE AS "DimensionValue"
FROM
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
WHERE
A.ITEM_ID = PREF_NO
AND A.ITEM_ID = Z.ITEM_ID
AND Q.DIMENSION_ID = P.DIMENSION_ID
-- AND Q.LEVEL_ID = K.LEVEL_ID
AND Q.LEVEL_ID = Z.LEVEL_ID )) AS "UomDimensionsList")))
FROM
BIZZXE_V2_SCH.ITEM_UOM_LEVELS K,
BIZZXE_V2_SCH.UOMS L,
BIZZXE_V2_SCH.ITEM_SYSTEM_USAGES H
WHERE
A.ITEM_ID = PREF_NO
AND A.ITEM_ID = K.ITEM_ID
AND K.SYS_USAGE_ID = H.SYS_USAGE_ID
AND K.UOM_ID = L.UOM_ID)AS "ItemUomLevelsList")))
INTO
varXml
FROM
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
WHERE
A.ITEM_ID = PREF_NO
AND A.HS_CODE_ID = B.HS_CODE_ID
AND C.BRAND_ID = A.BRAND_ID
AND D.ITEM_CAT_ID = A.CAT_ID
AND A.VARIANT_TEMP_ID = E.VARIANT_TEMP_ID;
SELECT XMLELEMENT (
"OBJECT", XMLELEMENT (
"Item", XMLFOREST( 1 AS "LocationId",
A.ITEM_ID AS "Id",
A.ITEM_ID AS "ItemId",
A.REMARKS AS "Remarks",
A.CODE AS "ItemCode",
A.NAME AS "ItemDescription",
A.SHORT_NAME AS "ItemShortName",
A.HS_CODE_ID AS "HsCodeId",
B.HS_CODE AS "HsCodeName",
A.BRAND_ID AS "ItemBrandId",
C.BRAND_DESCRIPTION AS "ItemBrandName",
A.CAT_ID AS "CategoryId",
D.ITEM_CAT_DESC AS "CategoryName",
A.VARIANT_TEMP_ID AS "VariantTemplateId",
E.VARIANT_TEMP_NAME AS "VarinatTemplateName",
(SELECT XMLAGG(XMLELEMENT ("AllocationTypes",
XMLFOREST ("AllocationTypeId" AS "AllocationTypeId",
"AllocationValue" AS "AllocationValue",
"AllocationType" AS "AllocationType",
"AllocationSystemUsageTypeId" AS "AllocationSystemUsageTypeId",
"AllocationSystemUsageType" AS "AllocationSystemUsageType")))
FROM (SELECT
F.ALLOCATION_ID AS "AllocationTypeId",
F.VALUE AS "AllocationValue",
G.ALLOCATION_DESC AS "AllocationType",
F.SYS_USAGE_ID AS "AllocationSystemUsageTypeId",
H.SYS_USAGE_NAME AS "AllocationSystemUsageType"
FROM BIZZXE_V2_SCH.ITEMS A, BIZZXE_V2_SCH.ITEM_ALLOCATIONS F, BIZZXE_V2_SCH.ALLOCATIONS G, BIZZXE_V2_SCH.ITEM_SYSTEM_USAGES H
WHERE A.ITEM_ID = F.ITEM_ID
AND A.ITEM_ID = PREF_NO
AND F.SYS_USAGE_ID = H.SYS_USAGE_ID
AND G.ALLOCATION_ID = F.ALLOCATION_ID
UNION
SELECT
G.ALLOCATION_ID AS "AllocationTypeId",
0 AS "AllocationValue",
G.ALLOCATION_DESC AS "AllocationType",
0 AS "AllocationSystemUsageTypeId",
NULL AS "AllocationSystemUsageType"
FROM BIZZXE_V2_SCH.ALLOCATIONS G
WHERE G.ALLOCATION_ID NOT IN
(SELECT G.ALLOCATION_ID AS "AllocationTypeId"
FROM BIZZXE_V2_SCH.ITEMS A, BIZZXE_V2_SCH.ITEM_ALLOCATIONS G
WHERE A.ITEM_ID = G.ITEM_ID
AND A.ITEM_ID = PREF_NO)))AS "AllocationTypesList",
(SELECT XMLAGG(XMLELEMENT ("VariantDimensionMatrix",
XMLFOREST ("SkuPrefixCode" AS "SkuPrefixCode",
"SkuPrefixDescription" AS "SkuPrefixDescription",
"ValidSku" AS "SkuSelected")))
FROM
( SELECT
I.SKU_PREFIX_CODE AS "SkuPrefixCode",
I.SKU_DESCRIPTION AS "SkuPrefixDescription",
'0' AS "ValidSku"
FROM BIZZXE_V2_SCH.ITEMS A, BIZZXE_V2_SCH.VARIANT_TEMP_DIMENSIONS I, BIZZXE_V2_SCH.VARIANT_TEMPLATES E
WHERE I.VARIANT_TEMP_ID = E.VARIANT_TEMP_ID
AND I.VARIANT_TEMP_ID = A.VARIANT_TEMP_ID
AND A.ITEM_ID = PREF_NO
AND I.SKU_PREFIX_CODE NOT IN
( SELECT
J.SKU_PREFIX_CODE AS "SkuPrefixCode"
FROM BIZZXE_V2_SCH.ITEMS A, BIZZXE_V2_SCH.ITEM_VARINTS J
WHERE A.ITEM_ID = J.ITEM_ID
AND A.ITEM_ID = PREF_NO)
UNION
SELECT
J.SKU_PREFIX_CODE AS "SkuPrefixCode",
J.SKU_PREFIX_DESC AS "SkuPrefixDescription",
J.VALID_SKU AS "ValidSku"
FROM BIZZXE_V2_SCH.ITEMS A, BIZZXE_V2_SCH.ITEM_VARINTS J
WHERE A.ITEM_ID = PREF_NO
AND A.ITEM_ID = J.ITEM_ID))AS "VariantDimensionMatrixList",
(SELECT XMLAGG(XMLELEMENT ("ItemUomLevels",
XMLFOREST (K.UOM_LEVEL AS "UomLevelId",
K.UOM_ID AS "UomId",
L.DESCRIPTION AS "Uom",
K.LONG_NAME AS "UomLongName",
K.CONV_FACTR AS "ConversionFactor",
K.STATUS_ID AS "UomLevelStatus",
K.SYS_USAGE_ID AS "UomLevelSystemUsageTypeId",
H.SYS_USAGE_NAME AS "UomLevelSystemUsageType",
K.IMAGE AS "UomLevelImage",
K.UOM_LEVEL_VOLUME AS "UomLevelVolume",
(SELECT
UNIQUE CASE WHEN S.ITEM_ID<>0 THEN 1
END AS "UomLevelEditable"
FROM BIZZXE_V2_SCH.PO_REQUEST_ITEMS S WHERE S.ITEM_Id = PREF_NO AND S.UOM_ID= L.UOM_ID) AS "UomLevelEditable",
(SELECT XMLAGG(XMLELEMENT ("UomLevelControls",
XMLFOREST (M.CONTROL_ID AS "ControlId",
M.CONTROL_NAME AS "ControlName")))
FROM 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
WHERE A.ITEM_ID = PREF_NO
AND A.ITEM_ID = K.ITEM_ID
AND K.LEVEL_ID = N.LEVEL_ID
AND Y.LEVEL_ID = N.LEVEL_ID
AND N.CONTROL_ID = M.CONTROL_ID) AS "UomLevelControlsList",
(SELECT XMLAGG(XMLELEMENT ("UomDimensions",
XMLFOREST ("DimensionId" AS "DimensionId",
"DimensionName" AS "DimensionName",
"DimensionValue" AS "DimensionValue",
"Level" AS "Level")))
FROM (SELECT P.DIMENSION_ID AS "DimensionId",
P.DIMENSION_DESC AS "DimensionName",
0 AS "DimensionValue",
0 AS "Level"
FROM BIZZXE_V2_SCH.DIMENSION_SETTINGS O, BIZZXE_V2_SCH.DIMENSIONS P
WHERE P.DIMENSION_ID = O.DIMENSION_ID
AND O.SETTING_PROPERTY_ID = 42
AND O.SETTING_VALUE = 1
AND P.DIMENSION_ID NOT IN (
SELECT P.DIMENSION_ID
FROM 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
WHERE A.ITEM_ID = X.ITEM_ID
AND X.LEVEL_ID = Q.LEVEL_ID
AND A.ITEM_ID = PREF_NO )
UNION
SELECT P.DIMENSION_ID AS "DimensionId",
P.DIMENSION_DESC AS "DimensionName",
Q.DIMENSION_VALUE AS "DimensionValue",
Q.LEVEL_ID AS "Level"
FROM 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
WHERE A.ITEM_ID = PREF_NO
AND A.ITEM_ID = Z.ITEM_ID
AND Q.DIMENSION_ID = P.DIMENSION_ID
AND Q.LEVEL_ID = Z.LEVEL_ID) DUAL WHERE K.LEVEL_ID="Level") AS "UomDimensionsList")))
FROM BIZZXE_V2_SCH.ITEM_UOM_LEVELS K, BIZZXE_V2_SCH.UOMS L, BIZZXE_V2_SCH.ITEM_SYSTEM_USAGES H
WHERE A.ITEM_ID = PREF_NO
AND A.ITEM_ID = K.ITEM_ID
AND K.SYS_USAGE_ID = H.SYS_USAGE_ID
AND K.UOM_ID = L.UOM_ID)AS "ItemUomLevelsList")))
INTO varXml
FROM 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
WHERE A.ITEM_ID = PREF_NO
AND A.HS_CODE_ID = B.HS_CODE_ID
AND C.BRAND_ID = A.BRAND_ID
AND D.ITEM_CAT_ID = A.CAT_ID
AND A.VARIANT_TEMP_ID = E.VARIANT_TEMP_ID;