UNION problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UNION problem

Author

Topic

tomba23
Starting Member

2 Posts

Posted - 2011-12-09 : 09:53:50

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.RESULT_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_VALUE 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.CODE_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.PERSON_ID )
AND ( RESOURCE_GROUP_SUB.CHILD_SERVICE_RESOURCE_CD=CLINICAL_EVENT.RESOURCE_CD )
AND ( RESOURCE_GROUP_SUB.PARENT_SERVICE_RESOURCE_CD=RESOURCE_GROUP_SEC.CHILD_SERVICE_RESOURCE_CD )
AND ( RESOURCE_GROUP_SEC.PARENT_SERVICE_RESOURCE_CD=CV_RES_GRP_SEC.CODE_VALUE )
AND ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID )
AND ( CV_ENCNTR_ALIAS_TYPE.CODE_VALUE=ENCNTR_ALIAS.ENCNTR_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.ORGANIZATION_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_HIST.CODE_VALUE )
AND ( ENCOUNTER.LOC_NURSE_UNIT_CD=CV_NURSE_UNIT.CODE_VALUE 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.ENCNTR_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 )
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 10:07:22

seems like number of columns in both sides of UNION is not same
Also you're using Oracle and this is a sql server forum
you may be better off posting it in some oracle related forums like www.dbforums.com for oracle specific help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tomba23
Starting Member

2 Posts

Posted - 2011-12-09 : 10:15:59

Thank you

quote:
Originally posted by visakh16

seems like number of columns in both sides of UNION is not same
Also you're using Oracle and this is a sql server forum
you may be better off posting it in some oracle related forums like www.dbforums.com for oracle specific help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-12 : 05:36:05

Both union and union all require same number of columns with datatype compatible in all RDBMSs

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources