Synthax error for query execution 2

[BenjaminLim]

Encounter the following error when executing my query.

ORA-00979: not a GROUP BY expression

Following is the query.

2nd question : For returning of all numbers from a field that contains besides number other characters such as /()-

example :
12-123131-(0) to be returned as 121231310

Pls advice.

========================

SELECT COM.Company_Name, PAY.Bank_Code, PAY.Branch_Code,
YNP.Employee_Id, EMP.Employee_Name,
Translate(CBC.CMP_Account_Num, '0123456789/()-','0123456789') Account_Num,
Translate(PAY.Account_Num,'0123456789/()-','0123456789') Account_No,
SUM(DECODE(CONCAT(YNP.Major_Group_Code, YNP.Alln_Dedn_Code),
'NPYNPY', YNP.Alln_Dedn_Amt, 0)) -
SUM(DECODE(CONCAT(YNP.Major_Group_Code, YNP.Alln_Dedn_Code),
'CHQCHQ', YNP.Alln_Dedn_Amt, 'WHDWHD', YNP.Alln_Dedn_Amt,0)) Amount
FROM HRPY_PAY_PROCESS YNP, PER_EMPLOYEES EMP,
HRPY_PAYROLL_MASTER PAY,
PER_JOB_SAL_HISTORIES JSH, PER_COMPANIES COM,
HRPY_COMPANY_BNK_CPF CBC, HRPY_TMP_SEC_EMPLOYEES TMP
WHERE YNP.Pay_Period = TO_NUMBER(TO_CHAR(199701))
AND YNP.Major_Group_Code IN ('NPY', 'CHQ', 'WHD')
AND YNP.Alln_Dedn_Code IN ('NPY', 'CHQ', 'WHD')
AND JSH.Employee_Id = YNP.Employee_Id
AND JSH.Employee_Id NOT IN ( SELECT RPH.Employee_Id
FROM HRPY_RESIGN_PAY_HD RPH
WHERE RPH.Pay_Period = YNP.Pay_Period)
AND JSH.Effective_Date
IN (SELECT MAX(JSH1.Effective_Date )
FROM PER_JOB_SAL_HISTORIES JSH1
WHERE JSH1.Employee_Id = JSH.Employee_Id
AND JSH1.Effective_Date <=
LAST_DAY(TO_DATE(TO_CHAR(199701)) ))
AND JSH.Company_Code = '30'
AND EMP.Employee_Id = YNP.Employee_Id
AND PAY.Employee_Id = YNP.Employee_Id
AND COM.Company_Code = JSH.Company_Code
AND CBC.Company_Code = JSH.Company_Code
AND CBC.Staff_Type = JSH.Staff_Type
AND JSH.Employee_Id = TMP.Employee_Id
AND USER = TMP.Logon_Id
GROUP BY COM.Company_Name, PAY.Bank_Code, PAY.Branch_Code,
YNP.Employee_Id, EMP.Employee_Name;

 

[Kenrae]

First question:
All fields returned in a query must be in the group by clause, except agregate functions (SUM, MAX and so on). You're selecting

Translate CBC.CMP_Account_Num, '0123456789/()-','0123456789') Account_Num

or other fields which aren't in the group by clause.

Second question:
I don't know an Oracle function that makes what you need, but maybe you could use replace function. If you could replace something with null it would do the job, but I haven't tested it myself.
If it's not possible, you can always code your own function.

 

[BenjaminLim]

1. I have tried but it fails -> no such column name

2. How to use replace? Show example.

Thanks,

 

[sem]

1. Your group by clause have to coiunside with grouping columns:

group by COM.Company_Name, PAY.Bank_Code, PAY.Branch_Code,
YNP.Employee_Id, EMP.Employee_Name, Translate(CBC.CMP_Account_Num, '0123456789/()-','0123456789'),Translate(PAY.Account_Num,'0123456789/()-','0123456789')

1 For returning non-numerics, you may use the same
translate() function by comparing the result with source.

 

[Kenrae]

replace('bed','b','r')

returns 'red'.
But sem solution seems better to me.