This is part of a much larger query, but this snippet shows the error. Without the aggregation and Group by, the statement works. Once I add the aggregation and group by, I get this error:
- [Code: -206, SQL State: 42703] "COMPANYN" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.22.29
- [Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "COMPANYN".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.22.29
Below is the SQL:
SELECT s.SERVICE_NAME servicename,
CASE
WHEN (r.company IS NULL
OR r.company = ''
OR r.company = 'BTS')
AND r.CREATED_BY like '%JBlake%' THEN 'ABC'
ELSE r.company
END companyN,
r.CREATED_BY,
count(1) NoOfHits
FROM ac.SSO_REQUEST r,
ac.SSO_SERVICE s
WHERE s.id = r.DSO_SERVICE_ID
AND r.CREATE_DATE > TO_DATE('06302018', 'MMDDYYYY')
AND r.CREATE_DATE < TO_DATE('07012019', 'MMDDYYYY')
group by s.SERVICE_NAME, companyN, r.CREATED_BY
If I comment out the aggregation and group by, the query works. I think the alias of companyN is causing a problem in the group by.
SELECT s.SERVICE_NAME servicename,
CASE
WHEN (r.company IS NULL
OR r.company = ''
OR r.company = 'BTS')
AND r.CREATED_BY like '%JBlake%' THEN 'ABC'
ELSE r.company
END companyN,
r.CREATED_BY--,
--count(1) NoOfHits
FROM ac.SSO_REQUEST r,
ac.SSO_SERVICE s
WHERE s.id = r.DSO_SERVICE_ID
AND r.CREATE_DATE > TO_DATE('06302018', 'MMDDYYYY')
AND r.CREATE_DATE < TO_DATE('07012019', 'MMDDYYYY')
--group by s.SERVICE_NAME, companyN, r.CREATED_BY
