Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am facing a general script error every time I run the below query. also the right string function is not working as expected. The field has 9 numbers and I just need 8 from right. While running the script it takes lot of time to run and often gets stucked for some minutes.
Cost_Tmp:
LOAD COST_ID,
CSTC_NO,
Text(PLANT_CD) as PLANT_CD,
COST_ORDER_NO as ORDER_NO, //Key Field
ACCT_NO,
Last_YM,
COST_AMT,
COST_QTY
FROM
[..\..\01. Extract\01. QVD\Order_Cost.qvd]
(qvd);
Inner join(Cost_Tmp)
LOAD ORDER_NO, //Key Field
ORDER_TYPE,
EQP_NO,
FIRST_DT
FROM
[..\..\01. Extract\01. QVD\Order.qvd]
(qvd);
Left join(Cost_Tmp)
LOAD Text(PLANT_CD) as PLANT_CD,
Region as Plant_Region,
Area as Plant_Area,
District as Plant_District
FROM
[..\..\01. Extract\01. QVD\Plant.qvd]
(qvd) where match (Region, 'US-Northern' , 'US-Southern');
//Exit script; (Runs properly till here but gets strucked after this and doesn't proceed further to noconcatenate)
NoConcatenate
Cost_Utilisation:
load COST_ID, CSTC_NO, ORDER_NO as CST_ORDER_NO, right(EQP_NO,8) as EQP_NO, ACCT_NO,Last_YM,ORDER_TYPE,FIRST_DT,PLANT_CD,
sum(COST_AMT) as Total_Amount,
sum(COST_QTY) as Total_Quantity,
If((Year(FIRST_DT)*100+Month(FIRST_DT))=Last_YM,1,0) as FLAG
Resident Cost_Tmp
where match (ACCT_NO, '1', '2','3', '4')
Group by COST_ID, CSTC_NO, ORDER_NO, EQP_NO, ACCT_NO,POST_YM,ORDER_TYPE,FIRST_TECO_DT,PLANT_CD;
concatenate(Cost_Utilisation)
load EQP_NO, Read_YM, Sum(READING) as READING, Sum(MONTHLY_HRS) as MONTHLY_HRS
Resident EQP Group by EQP_NO, Read_YM;
//order by EQP_NO, CAL_DT;
Drop Table Cost_Tmp;
Drop Table EQP;
May be share that image which you are facing the error?
These two fields - Last_YM, FIRST_DT - need to be in the Group By, or should be removed from the load. If they are used only in the conditional for FLAG, then thet don't need to be in the Group By. So either this
Cost_Utilisation:
load COST_ID, CSTC_NO, ORDER_NO as CST_ORDER_NO, right(EQP_NO,8) as EQP_NO, ACCT_NO,Last_YM,ORDER_TYPE,FIRST_DT,PLANT_CD,
sum(COST_AMT) as Total_Amount,
sum(COST_QTY) as Total_Quantity,
If((Year(FIRST_DT)*100+Month(FIRST_DT))=Last_YM,1,0) as FLAG
Resident Cost_Tmp
where match (ACCT_NO, '1', '2','3', '4')
Group by COST_ID, CSTC_NO, ORDER_NO, EQP_NO, ACCT_NO,POST_YM,ORDER_TYPE,FIRST_TECO_DT,PLANT_CD, Last_YM, FIRST_DT;
or this:
Cost_Utilisation:
load COST_ID, CSTC_NO, ORDER_NO as CST_ORDER_NO, right(EQP_NO,8) as EQP_NO, ACCT_NO,ORDER_TYPE,PLANT_CD,
sum(COST_AMT) as Total_Amount,
sum(COST_QTY) as Total_Quantity,
If((Year(FIRST_DT)*100+Month(FIRST_DT))=Last_YM,1,0) as FLAG
Resident Cost_Tmp
where match (ACCT_NO, '1', '2','3', '4')
Group by COST_ID, CSTC_NO, ORDER_NO, EQP_NO, ACCT_NO,POST_YM,ORDER_TYPE,FIRST_TECO_DT,PLANT_CD;
Hi Jonathan,
I have been using the same keeping them in load and in group by too but still the same error, also the right(EQP_NO,8) as EQP_NO is not picking up the right 8 digit numbers.
Cost_Utilisation:
load COST_ID, CSTC_NO, ORDER_NO as CST_ORDER_NO, right(EQP_NO,8) as EQP_NO, ACCT_NO,Last_YM,ORDER_TYPE,FIRST_DT,PLANT_CD,
sum(COST_AMT) as Total_Amount,
sum(COST_QTY) as Total_Quantity,
If((Year(FIRST_DT)*100+Month(FIRST_DT))=Last_YM,1,0) as FLAG
Resident Cost_Tmp
where match (ACCT_NO, '1', '2','3', '4')
Group by COST_ID, CSTC_NO, ORDER_NO, EQP_NO, ACCT_NO, Last_YM, ORDER_TYPE,FIRST_DT,PLANT_CD;