Results 1 to 8 of 8

Thread: UNION problem

  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: UNION problem

    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 )
    )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2011
    Posts
    3
    Thanks for being so rude!

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by tomba23 View Post
    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:
    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data, what exactly do you expect from here?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2011
    Posts
    3
    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.

    Quote Originally Posted by flyboy View Post
    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.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by tomba23 View Post
    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.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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