Could you please help me with below, am getting ORA-00935: group function is nested too deeply.ts & LicenseParts levels.
But i need the Aggregation at both ServiceParts & LicenseParts levels.
SELECT XMLELEMENT("Contracts",
XMLAGG (
XMLELEMENT("Contract",XMLFOREST(contract_number as "ContractNumber",
name as "CsiNumber",
start_date as "ContractStartDate",
END_DATE as "ContractEndDate",
Grace_Duration As "GracePeriod",
xmlforest(PARTY_ID as "PartyID",PARTY_NAME as "LocalName") as "Party",
XMLELEMENT("Subscription",XMLFOREST(COALESCE(SUBSCRIPTION_ID,' ') AS "SubscriptionID",'OKS' AS "Source",
xmlelement("Order",xmlforest(ORDER_NUMBER as "OrderNumber",
xmlagg( xmlelement("ServicePart",Xmlforest(Service_Line_Id As "OrderLineID",Service_Period_Start As "ServicePeriodStart",
Service_Period_End As "ServicePeriodEnd",Service_Group As "ServiceGroup",
Service_Part_Id As "ServicePartID",Service_Part "ServicePartNumber",
Service_Part_Description "ServicePartDescription",Service_Provisioned_Product "ProvisionedProduct",
SERVICE_PRODUCT_METRIC "ProductMetric",coalesce(null,' ') as "MetricQuantity",
xmlagg( xmlelement("LicensePart",Xmlforest(Coalesce(NULL,' ') As "LicensePeriodStart",Coalesce(NULL,' ') As "LicensePeriodEnd",
License_Part as "LicensePartNumber",License_Part_Description as "LicensePartDescription",
Coalesce(License_Provisioned_Product,' ') As "ProvisionedProduct",
License_Product_Metric As "ProductMetric",License_Block_Quantity as "BlockQuantity",
LICENSE_METRIC_QTY as "MetricQuantity"
))) as "LicenseParts"
))) AS "ServiceParts"
)) As "Orders"
)) As "Subscriptions"
) as "Contracts" ))) v_xml
From Misimd_Oks_Sps_License_View
WHERE 1=1
and ORDER_NUMBER='179339' and SUBSCRIPTION_ID=1292669
group by CONTRACT_NUMBER,name,START_DATE,END_DATE,GRACE_DURATION,PARTY_ID,PARTY_NAME,SUBSCRIPTION_ID,ORDER_NUMBER
,SERVICE_LINE_ID,SERVICE_PERIOD_START,SERVICE_PERIOD_END,SERVICE_GROUP,SERVICE_PART_ID,SERVICE_PART,SERVICE_PART_DESCRIPTION,
SERVICE_PROVISIONED_PRODUCT,SERVICE_PRODUCT_METRIC;