Simple load and group script

[Anonymous]

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;  

[Gysbert_Wassenaar]

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];

[Anonymous · Accepted solution]

Hi Gysbert,

Thanks very much!!

How stupid of me…

Jonathan