Solved
Combine datasets from different stored procedures into one output
Hello,
I have a pipeline that uses two different stored procedures. The issue I'm having is that I need to incorporate the output of the second stored procedure (ranges) into every object of the first stored procedure output (risks).
The output of the first stored procedure(risks) is:
[
{
"SupplierCode":"GVN00000007",
"SupplierTeamLeadCode":"Top 150",
"SupplierLocationCode":"UNITED STATES",
"SupplierLocationStateCode":"Georgia",
"SupplierCategoryCode":"Metals",
"DBRisk":"8",
"OwnershipRisk":10.0,
"RestrictionsRisk":10.0,
"ContractRisk":15.0,
"SourceRisk":8.0,
"GeopoliticalRisk":10.0,
"CapacityRisk":15.0,
"ContributingRisk":0.0,
"FailedMarketRisk":10.0,
"AggregateRisk":86.0,
"RiskLevel":"Low Risk",
"Mitigation_Code":null
},
{
"SupplierCode":"GVN00000541",
"SupplierTeamLeadCode":"Top 150",
"SupplierLocationCode":"UNITED STATES",
"SupplierLocationStateCode":"Minnesota",
"SupplierCategoryCode":"Metals",
"DBRisk":"8",
"OwnershipRisk":5.0,
"RestrictionsRisk":10.0,
"ContractRisk":15.0,
"SourceRisk":8.0,
"GeopoliticalRisk":10.0,
"CapacityRisk":7.5,
"ContributingRisk":0.0,
"FailedMarketRisk":10.0,
"AggregateRisk":73.5,
"RiskLevel":"High Risk",
"Mitigation_Code":"Implementing a contract"
},
{
"SupplierCode":"GVN00000541",
"SupplierTeamLeadCode":"Top 150",
"SupplierLocationCode":"UNITED STATES",
"SupplierLocationStateCode":"Minnesota",
"SupplierCategoryCode":"Metals",
"DBRisk":"8",
"OwnershipRisk":5.0,
"RestrictionsRisk":10.0,
"ContractRisk":15.0,
"SourceRisk":8.0,
"GeopoliticalRisk":10.0,
"CapacityRisk":7.5,
"ContributingRisk":0.0,
"FailedMarketRisk":10.0,
"AggregateRisk":73.5,
"RiskLevel":"High Risk",
"Mitigation_Code":"Increased Inventory"
},
{
"SupplierCode":"GVN00045254",
"SupplierTeamLeadCode":"WCA",
"SupplierLocationCode":"AUSTRALIA",
"SupplierLocationStateCode":null,
"SupplierCategoryCode":"Metals",
"DBRisk":"5",
"OwnershipRisk":7.5,
"RestrictionsRisk":10.0,
"ContractRisk":10.0,
"SourceRisk":5.0,
"GeopoliticalRisk":10.0,
"CapacityRisk":7.5,
"ContributingRisk":10.0,
"FailedMarketRisk":10.0,
"AggregateRisk":75.0,
"RiskLevel":"High Risk",
"Mitigation_Code":"Risk Accepted by Business"
}
]
Output of second stored procedure and mapper - (ranges)
[
{
"ranges":{
"highRiskLimitRange":"0.00-75.00"
"mediumRiskLimitRange":"75.10-85.00"
"lowRiskLimitRange":"85.10-1000.00"
}
}
]
The expected result is:
{
"risks": [
{
"riskDetails": {
"supplierCode": "GVN00000007",
"supplierLocationCode": "UNITED STATES",
"supplierLocationStateCode": "Georgia",
"dbRisk": "8",
"ownershipRisk": 10.0,
"restrictionsRisk": 10.0,
"contractRisk": 15.0,
"sourceRisk": 8.0,
"geopoliticalRisk": 10.0,
"capacityRisk": 15.0,
"contributingRisk": 0.0,
"failedMarketRisk": 10.0
},
"aggregateRiskDetails": {
"aggregateRisk": 86.0,
"riskLevel": "Low Risk",
"ranges": {
"highRiskLimitRange": "0-75",
"mediumRiskLimitRange": "75.10-85",
"lowRiskLimitRange": "85.10-1000"
}
},
"mitigationStrategies": {
"mitigationCodes": []
}
},
{
"riskDetails": {
"supplierCode": "GVN00000541",
"supplierLocationCode": "UNITED STATES",
"supplierLocationStateCode": "Minnesota",
"dbRisk": "8",
"ownershipRisk": 5.0,
"restrictionsRisk": 10.0,
"contractRisk": 15.0,
"sourceRisk": 8.0,
"geopoliticalRisk": 10.0,
"capacityRisk": 7.5,
"contributingRisk": 0.0,
"failedMarketRisk": 10.0
},
"aggregateRiskDetails": {
"aggregateRisk": 73.5,
"riskLevel": "High Risk",
"ranges": {
"highRiskLimitRange": "0-75",
"mediumRiskLimitRange": "75.10-85",
"lowRiskLimitRange": "85.10-1000"
}
},
"mitigationStrategies": {
"mitigationCodes": [
"Implementing a contract",
"Increased Inventory"
]
}
},
{
"riskDetails": {
"supplierCode": "GVN00045254",
"supplierLocationCode": "AUSTRALIA",
"supplierLocationStateCode": "No_State_Code",
"dbRisk": "5",
"ownershipRisk": 7.5,
"restrictionsRisk": 10.0,
"contractRisk": 10.0,
"sourceRisk": 5.0,
"geopoliticalRisk": 10.0,
"capacityRisk": 7.5,
"contributingRisk": 10.0,
"failedMarketRisk": 10.0
},
"aggregateRiskDetails": {
"aggregateRisk": 75.0,
"riskLevel": "High Risk",
"ranges": {
"highRiskLimitRange": "0-75",
"mediumRiskLimitRange": "75.10-85",
"lowRiskLimitRange": "85.10-1000"
}
},
"mitigationStrategies": {
"mitigationCodes": [
"Risk Accepted by Business"
]
}
}
]
}
koryknick- this post has been out there for a while and I was wondering if you might be able to have a look. Your help is always appreciated!
Thank you,
Max
