Any help will be great! I am trying to UNION two queries and I am getting this error "ORA-01789: query block has incorrect number of result columns" and here is my code:

SELECT
PERSON.NAME_FULL_FORMATTED,
ENCNTR_ALIAS.ALIAS,
CV_Nurse_Ord.DISPLAY,
CV_NURSE_UNIT.DISPLAY,
0,
pi_from_gmt(CONTAINER.DRAWN_DT_TM,( pi_time_zone(1) )),
ACCESSION.ACCESSION,
ORDER_CATALOG.PRIMARY_MNEMONIC,
CV_EVENT.DISPLAY,
CLINICAL_EVENT.RESULT_VAL,
CASE CV_EVENT_CLASS.CDF_MEANING
When 'NUM' then
CASE NVL(Length(trim(CLINICAL_EVENT.RESULT_VAL)), 99999) When 99999 then 0 else
Case NVL(LENGTH(TRIM(TRANSLATE(TRIM(CLINICAL_EVENT.RESU LT_VAL), '. 0123456789', ' '))),0)
When 0 then
to_number(CLINICAL_EVENT.RESULT_VAL)
Else
0
End
End
Else
0
End,
pi_from_gmt(CLINICAL_EVENT.VERIFIED_DT_TM,( pi_time_zone(1) ))

,
CE_PRSNL_VERF.NAME_FULL_FORMATTED
FROM
PERSON,
ENCNTR_ALIAS,
CODE_VALUE CV_ENCNTR_ALIAS_TYPE,
ENCNTR_LOC_HIST ENCNTR_LOC_HIST_ORD,
ORDERS,
ENCOUNTER,
CODE_VALUE CV_Nurse_Ord,
CODE_VALUE CV_NURSE_UNIT,
CLINICAL_EVENT,
CONTAINER,
ACCESSION,
ORDER_CATALOG,
CODE_VALUE CV_EVENT,
CODE_VALUE CV_EVENT_CLASS,
PRSNL CE_PRSNL_VERF,
ENCNTR_LOC_HIST,
CODE_VALUE CV_ORD_ACT_TYPE,
RESOURCE_GROUP RESOURCE_GROUP_SUB,
RESOURCE_GROUP RESOURCE_GROUP_SEC,
CODE_VALUE CV_RES_GRP_SEC,
CODE_VALUE CV_NORMALCY,
ORDER_CONTAINER_R,
ACCESSION_ORDER_R,
PERSON CE_PERSON_VERF
WHERE
( ENCOUNTER.PERSON_ID=PERSON.PERSON_ID AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCOUNTER.ENCNTR_ID=ENCNTR_LOC_HIST.ENCNTR_ID AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID
AND ENCNTR_ALIAS.ACTIVE_IND = 1
AND ENCNTR_ALIAS.END_EFFECTIVE_DT_TM > SYSDATE AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCNTR_ALIAS.ENCNTR_ALIAS_TYPE_CD=CV_ENCNTR_ALIAS_ TYPE.CODE_VALUE )
AND ( ENCOUNTER.LOC_NURSE_UNIT_CD=CV_NURSE_UNIT.CODE_VAL UE AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ACCESSION.ACCESSION_ID=ACCESSION_ORDER_R.ACCESSION _ID )
AND ( ACCESSION_ORDER_R.ORDER_ID=ORDERS.ORDER_ID )
AND ( ORDERS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID AND ENCOUNTER.ACTIVE_IND = 1 AND ORDERS.PERSON_ID = ENCOUNTER.PERSON_ID )
AND ( ORDERS.CATALOG_CD=ORDER_CATALOG.CATALOG_CD )
AND ( ORDERS.CATALOG_TYPE_CD=ORDER_CATALOG.CATALOG_TYPE_ CD )
AND ( ORDER_CATALOG.ACTIVITY_TYPE_CD=CV_ORD_ACT_TYPE.COD E_VALUE )
AND ( ENCNTR_LOC_HIST_ORD.ENCNTR_ID=ORDERS.ENCNTR_ID )
AND ( ENCNTR_LOC_HIST_ORD.TRANSACTION_DT_TM<=ORDERS.ORIG _ORDER_DT_TM
and ENCNTR_LOC_HIST_ORD.active_ind = 1
and ENCNTR_LOC_HIST_ORD.TRANSACTION_DT_TM = (select Max(elh.TRANSACTION_DT_TM)
from encntr_loc_hist elh
where elh.TRANSACTION_DT_TM <= ORDERS.ORIG_ORDER_DT_TM
and ORDERS.ENCNTR_ID = elh.ENCNTR_ID) )
AND ( ENCNTR_LOC_HIST_ORD.LOC_NURSE_UNIT_CD=CV_Nurse_Ord .CODE_VALUE )
AND ( ORDER_CONTAINER_R.ORDER_ID=ORDERS.ORDER_ID )
AND ( CONTAINER.CONTAINER_ID=ORDER_CONTAINER_R.CONTAINER _ID )
AND ( CLINICAL_EVENT.EVENT_CD=CV_EVENT.CODE_VALUE )
AND ( ENCOUNTER.ENCNTR_ID=CLINICAL_EVENT.ENCNTR_ID AND ENCOUNTER.ACTIVE_IND = 1 AND ENCOUNTER.PERSON_ID = CLINICAL_EVENT.PERSON_ID )
AND ( CLINICAL_EVENT.EVENT_CLASS_CD=CV_EVENT_CLASS.CODE_ VALUE )
AND ( CLINICAL_EVENT.NORMALCY_CD=CV_NORMALCY.CODE_VALUE )
AND ( CLINICAL_EVENT.VERIFIED_PRSNL_ID=CE_PERSON_VERF.PE RSON_ID )
AND ( RESOURCE_GROUP_SUB.CHILD_SERVICE_RESOURCE_CD=CLINI CAL_EVENT.RESOURCE_CD )
AND ( RESOURCE_GROUP_SUB.PARENT_SERVICE_RESOURCE_CD=RESO URCE_GROUP_SEC.CHILD_SERVICE_RESOURCE_CD )
AND ( RESOURCE_GROUP_SEC.PARENT_SERVICE_RESOURCE_CD=CV_R ES_GRP_SEC.CODE_VALUE )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID )
AND ( CV_ENCNTR_ALIAS_TYPE.CODE_VALUE=ENCNTR_ALIAS.ENCNT R_ALIAS_TYPE_CD )
AND ( ENCOUNTER.PERSON_ID=PERSON.PERSON_ID )
AND ( PERSON.ACTIVE_IND = 1 )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID
AND ENCNTR_ALIAS.ACTIVE_IND = 1
AND ENCNTR_ALIAS.END_EFFECTIVE_DT_TM>SYSDATE
AND ENCOUNTER.ACTIVE_IND = 1

)
AND ( ENCNTR_LOC_HIST.ACTIVE_IND = 1
)
AND ( ENCNTR_LOC_HIST_ORD.ACTIVE_IND = 1
)
AND ( CE_PRSNL_VERF.PERSON_ID=CE_PERSON_VERF.PERSON_ID )
AND ( ENCOUNTER.ACTIVE_IND = 1 )
AND ( CV_ENCNTR_ALIAS_TYPE.CODE_SET = 319
and CV_ENCNTR_ALIAS_TYPE.CDF_MEANING = 'FIN NBR'
and ENCNTR_ALIAS.end_effective_dt_tm>sysdate
and ENCNTR_ALIAS.active_ind=1 )
AND ( ORDERS.ORDER_ID = CLINICAL_EVENT.ORDER_ID )
AND
(
ENCNTR_LOC_HIST.LOC_FACILITY_CD IN ( 17631048, 17654970 )
AND
ENCOUNTER.ENCNTR_TYPE_CD IN ( 17721922, 17721904, 17721912, 17721934, 309308, 309312, 26880952 )
AND
ORDERS.CATALOG_TYPE_CD IN ( 2513 )
AND
CV_ORD_ACT_TYPE.DISPLAY IN ( 'Blood Bank','General Lab' )
AND
CV_RES_GRP_SEC.DESCRIPTION IN ( 'DRMC Chemistry Section','DRMC Coagulation Section','DRMC Hematology Section','DRMC Urinalysis Section','DRMC Blood Bank Section','DRMC Misc Microbiology Section' )
AND
CV_NORMALCY.DISPLAY IN ( 'CRIT' )
AND
CONTAINER.DRAWN_DT_TM BETWEEN pi_to_gmt(trunc(add_months(sysdate, -1), 'MONTH'), ( pi_time_zone(2)
)) AND pi_to_gmt(trunc(sysdate, 'MONTH') - (1/86400), ( pi_time_zone(2)
))
)
UNION
SELECT /*+ ALL_ROWS */
ENCNTR_ALIAS.ALIAS,
PERSON.NAME_FULL_FORMATTED,
CV_NURSE_UNIT_HIST.DISPLAY,
CV_NURSE_UNIT.DISPLAY,
CV_EVENT.DISPLAY,
CLINICAL_EVENT.RESULT_VAL,
pi_from_gmt(CE_DATE_RESULT.RESULT_DT_TM,( pi_time_zone(1) ))


,
pi_from_gmt(CLINICAL_EVENT.VERIFIED_DT_TM,( pi_time_zone(1) ))


FROM
ENCNTR_ALIAS,
CODE_VALUE CV_ENCNTR_ALIAS_TYPE,
PERSON,
CODE_VALUE CV_NURSE_UNIT_HIST,
ENCNTR_LOC_HIST,
ENCOUNTER,
CODE_VALUE CV_NURSE_UNIT,
CODE_VALUE CV_EVENT,
CLINICAL_EVENT,
CE_DATE_RESULT,
ORGANIZATION
WHERE
( ORGANIZATION.ORGANIZATION_ID=ENCOUNTER.ORGANIZATIO N_ID AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCOUNTER.PERSON_ID=PERSON.PERSON_ID AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCOUNTER.ENCNTR_ID=ENCNTR_LOC_HIST.ENCNTR_ID AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID
AND ENCNTR_ALIAS.ACTIVE_IND = 1
AND ENCNTR_ALIAS.END_EFFECTIVE_DT_TM > SYSDATE AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( ENCNTR_ALIAS.ENCNTR_ALIAS_TYPE_CD=CV_ENCNTR_ALIAS_ TYPE.CODE_VALUE )
AND ( ENCNTR_LOC_HIST.LOC_NURSE_UNIT_CD=CV_NURSE_UNIT_HI ST.CODE_VALUE )
AND ( ENCOUNTER.LOC_NURSE_UNIT_CD=CV_NURSE_UNIT.CODE_VAL UE AND ENCOUNTER.ACTIVE_IND = 1 )
AND ( CLINICAL_EVENT.EVENT_CD=CV_EVENT.CODE_VALUE )
AND ( CLINICAL_EVENT.EVENT_ID=CE_DATE_RESULT.EVENT_ID(+) )
AND ( ENCOUNTER.ENCNTR_ID=CLINICAL_EVENT.ENCNTR_ID AND ENCOUNTER.ACTIVE_IND = 1 AND ENCOUNTER.PERSON_ID = CLINICAL_EVENT.PERSON_ID )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID )
AND ( CV_ENCNTR_ALIAS_TYPE.CODE_VALUE=ENCNTR_ALIAS.ENCNT R_ALIAS_TYPE_CD )
AND ( ENCOUNTER.PERSON_ID=PERSON.PERSON_ID )
AND ( PERSON.ACTIVE_IND = 1 )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID
AND ENCNTR_ALIAS.ACTIVE_IND = 1
AND ENCNTR_ALIAS.END_EFFECTIVE_DT_TM>SYSDATE
AND ENCOUNTER.ACTIVE_IND = 1

)
AND ( ENCNTR_LOC_HIST.ACTIVE_IND = 1
)
AND ( CE_DATE_RESULT.VALID_UNTIL_DT_TM(+) = to_date( '12/31/2100','MM/DD/YYYY')
)
AND ( ENCOUNTER.ACTIVE_IND = 1 )
AND ( CV_ENCNTR_ALIAS_TYPE.CODE_SET = 319
and CV_ENCNTR_ALIAS_TYPE.CDF_MEANING = 'FIN NBR'
and ENCNTR_ALIAS.end_effective_dt_tm>sysdate
and ENCNTR_ALIAS.active_ind=1 )
AND ( ENCNTR_LOC_HIST.BEG_EFFECTIVE_DT_TM = (
SELECT MIN(ELH.BEG_EFFECTIVE_DT_TM)
FROM ENCNTR_LOC_HIST ELH
WHERE ELH.ENCNTR_ID = ENCNTR_LOC_HIST.ENCNTR_ID
AND ELH.ACTIVE_IND = 1) )
AND
(
(
(
ORGANIZATION.ORG_NAME IN ( 'Dubois Regional Medical Center' )
AND
ENCOUNTER.ENCNTR_TYPE_CD IN ( 17721912, 17721896, 17721904, 17721906, 17721922, 17721934 )
)
AND
ENCOUNTER.DISCH_DT_TM BETWEEN @prompt('Enter Discharge Date & Time (cond)(Start):','D',,Mono,Free,Persistent,,User:0) AND @prompt('Enter Discharge Date & Time (cond)(End):','D',,Mono,Free,Persistent,,User:1)
)
AND
CLINICAL_EVENT.EVENT_CD IN ( 17888607, 17888613, 17796540, 17796516, 17796510, 17796504, 17888625, 17796528, 17796486, 72362584 )
)

posted code is an ugly mess

>I am getting this error "ORA-01789: query block has incorrect number of result columns"
Oracle is too dumb to lie.

Believe the error code/message or file a bug report.
Since we don't have your tables or data, what exactly do you expect from here?

Thanks for being so rude!

The error message is quite straightforward, is not it? As UNION returns all rows from that two queries (without duplicates), its number of columns and corresponding types must match.

Although the query you posted is almost unreadable, it is possible to detect, that the second query returns 8 columns, while the first one returns more than 10 (I do not know it exactly; sorry, I was unable to get over that CASE expression).

After determining, what columns shall be present in the query result, just correct it to introduce those (and only those) columns in both queries.

Since we don't have your tables or data, what exactly do you expect from here?

Thank you. I did not write either of the queries, PowerInsight generated both of them that two users created. I was just assigned to combine the two into one report.

"Combine" is very fuzzy word. As nobody here knows at all, what these queries represent, what is their relationship and what is the exact expected result set, it is on you to firstly discover what "combine" exactly means.

Either way as flyboy said the NUMBER of columns from each query must be the same and the types. If the 3rd column in query 1 is a date, the 3rd column in query 2 must be a date and so on.