ORA -1789 query block has incorrect number of result columns

[jsin14]

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

[mkumarnk]

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