Forum Stats

  • 3,837,338 Users
  • 2,262,249 Discussions
  • 7,900,258 Comments

Discussions

query block has incorrect number of result columns

V prasad
V prasad Member Posts: 619
edited Feb 13, 2011 11:44PM in SQL & PL/SQL
can anyone edit my query to eliminate below error ??


 SELECT FLROOPENHEAD.RONBR RepairOrder,FLROOPENHEAD.DATEOPENED DateOpened,NULL DateClosed, 
 (SELECT FLREPAIRSTAT.DESCRIPTION  FROM FLREPAIRSTAT 
 WHERE FLREPAIRSTAT.REPAIRSTATID = FLROOPENHEAD.REPAIRSTATID) RepairOrderStatus, 
  (SELECT flrt.DESCRIPTION FROM FLREPAIRTYPE flrt 
  
  WHERE flrt.REPAIRTYPEID = FLROOPENHEAD.REPAIRTYPEID) RepairType,  FLROOPENHEAD.REPAIRCENTER  RepairCenter, 
 FLROOPENHEAD.FLVEHICLEID , FLROOPENHEAD.FLATTACHID,'O'
 FROM FLROOPENHEAD   WHERE FLROOPENHEAD.LDEVNBR =2334 
 AND ((extract(DAY FROM SYSTIMESTAMP - TIMEOPENED)*24)+(extract(HOUR FROM SYSTIMESTAMP - TIMEOPENED)) <= 72)

 UNION 

 SELECT FLROHISTHEAD.RONBR RepairOrder,FLROHISTHEAD.DATEOPENED DateOpened, null, FLROHISTHEAD.DATECLOSED DateClosed, 
 (SELECT FLREPAIRSTAT.DESCRIPTION  FROM FLREPAIRSTAT 
 WHERE FLREPAIRSTAT.REPAIRSTATID =FLROHISTHEAD.REPAIRSTATID) RepairOrderStatus, 
 (SELECT flrt.DESCRIPTION FROM FLREPAIRTYPE flrt
  
 WHERE flrt.REPAIRTYPEID = FLROHISTHEAD.REPAIRTYPEID) RepairType, FLROHISTHEAD.REPAIRCENTER  RepairCenter 
 from FLROHISTHEAD WHERE FLROHISTHEAD.LDEVNBR =2334 
 AND ((extract(DAY FROM SYSTIMESTAMP - FLROHISTHEAD.TIMECLOSED)*24) + (extract(HOUR FROM SYSTIMESTAMP - FLROHISTHEAD.TIMECLOSED)) <= 72)

 ORDER BY DateOpened, RepairOrder



i think error was at second Where condition.
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01789: query block has incorrect number of result columns
01789. 00000 -  "query block has incorrect number of result columns"
*Cause:    
*Action:

Answers

  • rbglossip
    rbglossip Member Posts: 670 Bronze Badge
    Try removing the null from the second query of the union. You have null aliased as DateClosed in the first but a column aliased as DateClosed in the second.
    FLROOPENHEAD.DATEOPENED DateOpened,NULL DateClosed,
    FLROHISTHEAD.DATEOPENED DateOpened, null, FLROHISTHEAD.DATECLOSED DateClosed,
    rbglossip
  • Umesh Gupta
    Umesh Gupta Member Posts: 359
    Please post ur table structure and some sample data for clarifiaction..

    Regards

    Umi
    Umesh Gupta
  • user639304
    user639304 Member Posts: 303 Bronze Badge
    Hi OraclePLSQL,

    In your union operation, the first select has four columns and the second select has five columns.

    Hope this helps.
    user639304
  • Achyut K
    Achyut K Member Posts: 320
    Hi,

    On comparison with 1st query and 2nd query,it can be easily noticed that ,1st query is returning 4 columns and 2nd query returns 5 columns in select clause.
    In order to work union ,both of your queries should select equal no of columns.

    Hence replace your query

    Original query(1st ) :
    SELECT FLROOPENHEAD.RONBR RepairOrder,FLROOPENHEAD.DATEOPENED DateOpened,NULL DateClosed, 
     (SELECT FLREPAIRSTAT.DESCRIPTION  FROM FLREPAIRSTAT 
     WHERE FLREPAIRSTAT.REPAIRSTATID = FLROOPENHEAD.REPAIRSTATID) RepairOrderStatus,
    ....
    Relplace with :
     
    SELECT FLROOPENHEAD.RONBR RepairOrder,FLROOPENHEAD.DATEOPENED DateOpened,NULL,/*Here you need missed the comma */
     DateClosed, 
     (SELECT FLREPAIRSTAT.DESCRIPTION  FROM FLREPAIRSTAT 
     WHERE FLREPAIRSTAT.REPAIRSTATID = FLROOPENHEAD.REPAIRSTATID) RepairOrderStatus,
    ....
    Hope this helps

    Regards,
    Achyut
    Achyut K
This discussion has been closed.