ORA -1789 query block has incorrect number of result columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ORA -1789 query block has incorrect number of result columns

Hybrid View

  1. #1
    Join Date
    Sep 2003
    Location
    Australia
    Posts
    9

    ORA -1789 query block has incorrect number of result columns

    Hi,

    I have added a column in an existing form module. but getting the following error
    :ORA-01789 query block has incorrect number of result columns

    I have added new column INVRECEIVED_DATE. The select statement I am using is:

    V_DETAIL_SQL :=

    'select TD.PAYMENT_REFERENCE PAYMENT_REFERENCE,' ||
    'TD.VENDOR_CODE CARRIER_CODE,' ||
    'TD.VENDOR_NAME SNAME,' ||
    'TD.COST_CODE COST_CODE,' ||
    'TD.INVOICE_AMOUNT INVOICE_AMOUNT,' ||
    'TD.GST_AMOUNT GST_AMOUNT,' ||
    'TD.GST_AMOUNT OLD_GST_AMOUNT,' ||
    'TD.TAX_CODE TAX_CODE,' ||
    'TD.APPROVED_DATE APPROVED_DATE,' ||
    'TD.VENDOR_CODE OLD_CARRIER_CODE,' ||
    'TD.VENDOR_NAME OLD_CARRIER_NAME,' ||
    'TD.COST_CODE OLD_COST_CODE,' ||
    'TD.INVOICE_AMOUNT OLD_INVOICE_AMOUNT,' ||
    'TD.APPROVED_DATE OLD_APPROVED_DATE,' ||
    'TD.TRAVEL_DATE DATE1,' ||
    'TD.ARRIVAL_DATE DATE2,' ||
    'CCD.NAME DNAME,' ||
    'CCA.NAME ANAME,' ||
    'TD.PLACE_OF_DEPARTURE DEPARTURE,' ||
    'TD.PLACE_OF_ARRIVAL ARRIVAL,' ||
    'TD.BOOKING_REGION_CODE REGION,' ||
    ':GLOBAL.T_ID DETAIL_TYPE,' ||
    'TD.INVRECEIVED_DATE INVRECEIVED_DATE' || -- ADDED ON 02/10/2003

    ' from TRAVEL_DETAIL TD,' ||
    ' COMMUNITY_CODE CCD, COMMUNITY_CODE CCA' ||

    ' where TD.CLIENT_IDENTIFICATION = :GLOBAL.CLIENT_IDENTIFICATION' ||
    ' and TD.PLACE_OF_DEPARTURE = CCD.COMMUNITY_CODE' ||
    ' and TD.PLACE_OF_ARRIVAL = CCA.COMMUNITY_CODE' ||
    ' and NVL(TD.INVOICE_NUMBER,:GLOBAL.NULL_ID) = :GLOBAL.NULL_ID' ||
    ' and ((:HEADER.SERVICE_PROVIDER_CODE = :GLOBAL.X8' ||
    ' and TD.PAYMENT_METHOD = :GLOBAL.R_ID)' ||
    ' or (:HEADER.SERVICE_PROVIDER_CODE <> :GLOBAL.X8' ||
    ' and TD.PAYMENT_METHOD IN (:GLOBAL.A_ID,:GLOBAL.W_ID)))' ||

    ' UNION'||

    ' select PD.PAYMENT_REFERENCE PAYMENT_REFERENCE,' ||
    'PD.CARRIER_CODE CARRIER_CODE,' ||
    'PD.CARRIER_NAME SNAME,' ||
    'PD.COST_CODE COST_CODE,' ||
    'PD.INVOICE_AMOUNT INVOICE_AMOUNT,' ||
    'PD.GST_AMOUNT GST_AMOUNT,' ||
    'PD.GST_AMOUNT OLD_GST_AMOUNT,' ||
    'PD.TAX_CODE TAX_CODE,' ||
    'PD.APPROVED_DATE APPROVED_DATE,' ||
    'PD.CARRIER_CODE OLD_CARRIER_CODE,' ||
    'PD.CARRIER_NAME OLD_CARRIER_NAME,' ||
    'PD.COST_CODE OLD_COST_CODE,' ||
    'PD.INVOICE_AMOUNT OLD_INVOICE_AMOUNT,' ||
    'PD.APPROVED_DATE OLD_APPROVED_DATE,' ||
    'PD.TRANSPORT_DATE DATE1,' ||
    'TO_DATE(NULL) DATE2,' ||
    'CCD.NAME DNAME,' ||
    'CCA.NAME ANAME,' ||
    'PD.PLACE_OF_DEPARTURE DEPARTURE,' ||
    'PD.PLACE_OF_ARRIVAL ARRIVAL,' ||
    'PD.BOOKING_REGION_CODE REGION,' ||
    ':GLOBAL.P_ID DETAIL_TYPE,' ||
    'PD.INVRECEIVED_DATE,INVRECEIVED_DATE' || -- ADDED ON 02/10/2003

    ' from TRANSPORT_DETAIL PD,' ||
    ' COMMUNITY_CODE CCD, COMMUNITY_CODE CCA' ||


    ' where PD.CLIENT_IDENTIFICATION = :GLOBAL.CLIENT_IDENTIFICATION' ||
    ' and PD.PLACE_OF_DEPARTURE = CCD.COMMUNITY_CODE(+)' ||
    ' and PD.PLACE_OF_ARRIVAL = CCA.COMMUNITY_CODE(+)' ||
    ' and nvl(PD.INVOICE_NUMBER,:GLOBAL.NULL_ID) = :GLOBAL.NULL_ID' ||
    ' and ((:HEADER.SERVICE_PROVIDER_CODE = :GLOBAL.X8' ||
    ' and PD.PAYMENT_METHOD = :GLOBAL.R_ID)' ||
    ' or (:HEADER.SERVICE_PROVIDER_CODE <> :GLOBAL.X8' ||
    ' and PD.PAYMENT_METHOD IN (:GLOBAL.A_ID,:GLOBAL.W_ID)))' ||

    ' UNION' ||

    ' select AD.PAYMENT_REFERENCE PAYMENT_REFERENCE,' ||
    'AD.ACCOMMODATION_CODE CARRIER_CODE,' ||
    'AD.ACCOMMODATION_NAME SNAME,' ||
    'AD.COST_CODE COST_CODE,' ||
    'AD.INVOICE_AMOUNT INVOICE_AMOUNT,' ||
    'AD.GST_AMOUNT GST_AMOUNT,' ||
    'AD.GST_AMOUNT OLD_GST_AMOUNT,' ||
    'AD.TAX_CODE TAX_CODE,' ||
    'AD.APPROVED_DATE APPROVED_DATE,' ||
    'AD.ACCOMMODATION_CODE OLD_CARRIER_CODE,' ||
    'AD.ACCOMMODATION_NAME OLD_CARRIER_NAME,' ||
    'AD.COST_CODE OLD_COST_CODE,' ||
    'AD.INVOICE_AMOUNT OLD_INVOICE_AMOUNT,' ||
    'AD.APPROVED_DATE OLD_APPROVED_DATE,' ||
    'AD.BEGIN_DATE DATE1,' ||
    'AD.END_DATE DATE2,' ||
    'NULL DNAME,' ||
    'NULL ANAME,' ||
    'NULL DEPARTURE,' ||
    'NULL ARRIVAL,' ||
    'AD.BOOKING_REGION_CODE REGION,' ||
    ':GLOBAL.A_ID DETAIL_TYPE,' ||
    'AD.INVRECEIVED_DATE,INVRECEIVED_DATE ' || -- ADDED ON 02/10/2003

    ' from ACCOMMODATION_DETAIL AD' ||

    ' where AD.CLIENT_IDENTIFICATION = :GLOBAL.CLIENT_IDENTIFICATION' ||
    ' and nvl(AD.INVOICE_NUMBER,:GLOBAL.NULL_ID) = :GLOBAL.NULL_ID' ||
    ' and ((:HEADER.SERVICE_PROVIDER_CODE = :GLOBAL.X8' ||
    ' and AD.PAYMENT_METHOD = :GLOBAL.R_ID)' ||
    ' or (:HEADER.SERVICE_PROVIDER_CODE <> :GLOBAL.X8' ||
    ' and AD.PAYMENT_METHOD IN (:GLOBAL.A_ID,:GLOBAL.W_ID)))';

    MESSAGE('Query.....',NO_ACKNOWLEDGE);
    SYNCHRONIZE;


    Just a question: Does all the column in a select statement should be in sequence as they are appearing form module.

    Thanks
    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110
    Hi,

    You must have missed 1 or more columns in any one of the query which u are using in UNION.

    For example

    select x,y from abc
    union
    select a from xyz will lead to this error.i.e. first query u are using 2 columns and in second 1 column and using union u are trying to join it...

    I think this would help u..

    Manoj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width