I have created a stored-procedure where I want to execute and display data based on some status and dates. In that Query I am using GROUP BY function. But it's giving me error as
ORA-00904: "TFC"."OFFERED_DATE": invalid identifier
Query
SELECT DISTINCT TO_CHAR(TFC.SPANID) , TO_CHAR(TFC.MZ_CODE) AS MAINT_ZONE_CODE, TO_CHAR(TFC.MZ_NAME) AS MAINT_ZONE_NAME,
SUM(TFC.MHO_HANDOVER_CERT) AS NE_LENGTH,
TFC.CREATED_DATE AS OFFERED_DATE
FROM APP_LCO.tbl_fip_checklist TFC
WHERE LENGTH(trim(TFC.SPANID)) > 8
AND LENGTH(trim(TFC.SPANID)) < 21
AND tfc.status = 'APPROVED'
MINUS
SELECT TO_CHAR(BB.LINK_ID) AS SPAN_ID,
TO_CHAR(BB.MAINTENANCEZONECODE) AS MAINT_ZONE_CODE,
TO_CHAR(BB.MAINTENANCEZONENAME) AS MAINT_ZONE_NAME,
MAINT_ZONE_NE_SPAN_LENGTH AS NE_LENGTH,
BB.CREATED_DATE
FROM TBL_FIBER_INV_JOBS BB
WHERE SPAN_TYPE = 'INTRACITY'
group by TFC.SPANID,TFC.MZ_CODE, TFC.MZ_NAME, TFC.MHO_HANDOVER_CERT, TFC.OFFERED_DATE;
That's because you can't group by alias - you have to use column name, and that's tfc.created_date.
You misplaced GROUP BY clause; it can't be at the end of the 2nd query (as you use MINUS set operator of two SELECTs), but the one that contains aggregates - and that's the 1st query:
Apart from that, there's no use in distinct in a query that contains group by clause.
So:
SELECT TO_CHAR (tfc.spanid), --> remove DISTINCT
TO_CHAR (tfc.mz_code) AS maint_zone_code,
TO_CHAR (tfc.mz_name) AS maint_zone_name,
SUM (tfc.mho_handover_cert) AS ne_length,
tfc.created_date AS offered_date --> column name is CREATED_DATE
FROM app_lco.tbl_fip_checklist tfc
WHERE LENGTH (TRIM (tfc.spanid)) > 8
AND LENGTH (TRIM (tfc.spanid)) < 21
AND tfc.status = 'APPROVED'
GROUP BY TO_CHAR (tfc.spanid), --> move GROUP BY here
TO_CHAR (tfc.mz_code),
TO_CHAR (tfc.mz_name),
tfc.created_date --> use column name (not its alias) here
MINUS
SELECT TO_CHAR (bb.link_id) AS span_id,
TO_CHAR (bb.maintenancezonecode) AS maint_zone_code,
TO_CHAR (bb.maintenancezonename) AS maint_zone_name,
maint_zone_ne_span_length AS ne_length,
bb.created_date
FROM tbl_fiber_inv_jobs bb
WHERE span_type = 'INTRACITY'
[EDIT] How to use that query as a source (a CTE) for another SELECT statement:
WITH
temp
AS
-- your current query begins here ...
( SELECT TO_CHAR (tfc.spanid) spanid,
TO_CHAR (tfc.mz_code) AS maint_zone_code,
TO_CHAR (tfc.mz_name) AS maint_zone_name,
SUM (tfc.mho_handover_cert) AS ne_length,
tfc.created_date AS offered_date
FROM app_lco.tbl_fip_checklist tfc
WHERE LENGTH (TRIM (tfc.spanid)) > 8
AND LENGTH (TRIM (tfc.spanid)) < 21
AND tfc.status = 'APPROVED'
GROUP BY TO_CHAR (tfc.spanid),
TO_CHAR (tfc.mz_code),
TO_CHAR (tfc.mz_name),
tfc.created_date
MINUS
SELECT TO_CHAR (bb.link_id) AS span_id,
TO_CHAR (bb.maintenancezonecode) AS maint_zone_code,
TO_CHAR (bb.maintenancezonename) AS maint_zone_name,
maint_zone_ne_span_length AS ne_length,
bb.created_date
FROM tbl_fiber_inv_jobs bb
WHERE span_type = 'INTRACITY')
-- ... and ends here
SELECT spanid,
maint_zone_code,
maint_zone_name,
SUM (ne_length),
offered_date
FROM temp
GROUP BY spanid,
maint_zone_code,
maint_zone_name,
offered_date;
[EDIT #2] You commented that you still get duplicate rows; you shouldn't have. Here's a simple example which shows that there are no duplicates:
SQL> WITH
2 temp (spanid,
3 maint_zone_code,
4 maint_zone_name,
5 ne_length,
6 offered_date)
7 AS
8 (SELECT 'MUMB_1208', 'MB01', 'Navi Mumbai', 23, DATE '2023-07-31' FROM DUAL
9 UNION ALL
10 SELECT 'VDDR_0001', 'DR01', 'Vadodara-1', 3, DATE '2023-06-08' FROM DUAL
11 UNION ALL
12 SELECT 'VDDR_0001', 'DR01', 'Vadodara-1', 4, DATE '2023-06-08' FROM DUAL
13 UNION ALL
14 SELECT 'VDDR_0001', 'DR01', 'Vadodara-1', 5, DATE '2023-06-08' FROM DUAL)
15 SELECT spanid,
16 maint_zone_code,
17 maint_zone_name,
18 SUM (ne_length),
19 offered_date
20 FROM temp
21 GROUP BY spanid,
22 maint_zone_code,
23 maint_zone_name,
24 offered_date;
SPANID MAIN MAINT_ZONE_ SUM(NE_LENGTH) OFFERED_
--------- ---- ----------- -------------- --------
MUMB_1208 MB01 Navi Mumbai 23 31.07.23
VDDR_0001 DR01 Vadodara-1 12 08.06.23
SQL>