oracle-databasestored-proceduresminus

Minus is giving query block has incorrect number of result columns error in Oracle

I have a query where I want to minus lengths between 2 columns, so I executed the below query.

SELECT RJ_FSA_ID as FSA_ID, ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH, SPAN_REF_NAME,SPAN_NAME 
FROM NE.MV_SPAN@NE 
WHERE RJ_FSA_ID = 'GWLR0115' AND INVENTORY_STATUS_CODE = 'IPL'
AND SPAN_REF_NAME IN ('3x40-F-RG-ST-1L','3x40-F-RG-ST-1L','3x40-F-RG-ST-1L')
GROUP BY RJ_FSA_ID,SPAN_REF_NAME,SPAN_NAME
MINUS
SELECT AA.FSA_ID as FSA_ID, AA.MAINT_ZONE_NE_SPAN_LENGTH AS NE_LENGTH, BB.FSA_UG_ACTUAL FROM TBL_FIBER_INV_JOBS AA
INNER JOIN TBL_FIBER_INV_JOB_PROGRESS BB
ON AA.JOB_ID = BB.JOB_ID
WHERE AA.FSA_ID = 'GWLR0115';

SO while executing it I get error as

ORA-01789: query block has incorrect number of result columns

Solution

  • MINUS finds the results in the first result set that are not in the second result set; to do that it needs to compare the columns and see if they have identical values across the result sets.

    The first query has 4 columns, the second query has 3 columns. The database is correct that you cannot MINUS one result set from another because they have different numbers of columns.

    SELECT RJ_FSA_ID as FSA_ID,
       ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH,
       SPAN_REF_NAME,
       SPAN_NAME 
FROM   NE.MV_SPAN@NE 
WHERE  <rest_of_query>
MINUS
SELECT AA.FSA_ID as FSA_ID,
       AA.MAINT_ZONE_NE_SPAN_LENGTH AS NE_LENGTH,
       BB.FSA_UG_ACTUAL
FROM   TBL_FIBER_INV_JOBS AA
       INNER JOIN <rest_of_query>;

    Either: