Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following script which gives me a script error:
LOAD [NonSupplyID] AS [CaseRecordID]
,[BillRuleID] AS [BillingRuleID]
,[BillRuleID]
,[PriceAMT]
,if(BillRuleID=48 OR BillRuleID=55 OR BillRuleID=93,[PriceAMT],0) AS NonSupplyPriceA
,if(BillRuleID<>48 and BillRuleID<>55 and BillRuleID<>93,[PriceAMT],0) AS NonSupplyPriceS
;
SQL SELECT *
FROM [PICIS].[Finance].[CaseRecordNonSupply];
CaseRecordNonSupply:
LOAD CaseRecordID, BillingRuleID,BillRuleID,PriceAMT
,Sum(NonSupplyPriceA) AS NonSupplyPriceAnesthetist
,Sum(NonSupplyPriceS) as NonSupplyPriceSurgeon
Resident CaseRecordNonSupply_temp
GROUP BY CaseRecordID;
DROP Table CaseRecordNonSupply_temp;
You need to add all the fields that are not used in aggregation functions to the GROUP BY clause:
CaseRecordNonSupply:
LOAD CaseRecordID, BillingRuleID,BillRuleID,PriceAMT
,Sum(NonSupplyPriceA) AS NonSupplyPriceAnesthetist
,Sum(NonSupplyPriceS) as NonSupplyPriceSurgeon
GROUP BY CaseRecordID, BillingRuleID,BillRuleID,PriceAMT;
LOAD [NonSupplyID] AS [CaseRecordID]
,[BillRuleID] AS [BillingRuleID]
,[BillRuleID]
,[PriceAMT]
,if(BillRuleID=48 OR BillRuleID=55 OR BillRuleID=93,[PriceAMT],0) AS NonSupplyPriceA
,if(BillRuleID<>48 and BillRuleID<>55 and BillRuleID<>93,[PriceAMT],0) AS NonSupplyPriceS ;
SQL SELECT *
FROM [PICIS].[Finance].[CaseRecordNonSupply];
Hi Gysbert,
Thanks very much!!
How stupid of me…
Jonathan