|
bug in query generator |
Post Reply 
|
| Author |
 Share Topic Printable Version Delicious Digg Facebook Furl Google Boomarks Google Buzz MySpace Newsvine reddit StumbleUpon Translate Twitter Windows Live Yahoo Bookmarks Topic Search  Topic Options
|
DanW 
Newbie 
Joined: 04-Aug-2009 Posts: 6 |
 Post Options
 Quote Reply
 Topic: bug in query generatorPosted: 04-Aug-2009 at 8:23am |
|
Just a heads-up, I came across a bug with the query generator, where if a field name is exactly 30 characters long and the generated query contains an aliased name for that field, the alias has a number appended on to it, which pushes it over the 30 char Oracle limit.
Here's the offending line, I have included the whole query and the log message as well, but this is the actual problem:
"Extent36".CONF_RECEIVE_METHOD_OTHER_DESC AS CONF_RECEIVE_METHOD_OTHER_DESC1, (The generated "AS" alias is 31 chars long, exceeding the Oracle limit)
Here's the rest of the query and the actual error message:
SELECT
"UnionAll2".C2 AS C1, "UnionAll2".C3 AS C2, "UnionAll2".C4 AS C3, "UnionAll2".C5 AS C4, "UnionAll2".C6 AS C5, "UnionAll2".C7 AS C6, "UnionAll2".C8 AS C7, "UnionAll2".C9 AS C8, "UnionAll2".C10 AS C9, "UnionAll2".C11 AS C10, "UnionAll2".C12 AS C11, "UnionAll2".C13 AS C12, "UnionAll2".C14 AS C13, "UnionAll2".C15 AS C14, "UnionAll2".C16 AS C15, "UnionAll2".C17 AS C16, "UnionAll2".C18 AS C17, "UnionAll2".C19 AS C18, "UnionAll2".C20 AS C19, "UnionAll2".C21 AS C20, "UnionAll2".C22 AS C21, "UnionAll2".C23 AS C22, "UnionAll2".C24 AS C23, "UnionAll2".C25 AS C24, "UnionAll2".C26 AS C25, "UnionAll2".C27 AS C26, "UnionAll2".C28 AS C27, "UnionAll2".C29 AS C28, "UnionAll2".C30 AS C29, "UnionAll2".C31 AS C30, "UnionAll2".C32 AS C31, "UnionAll2".C33 AS C32, "UnionAll2".C34 AS C33, "UnionAll2".C35 AS C34, "UnionAll2".C36 AS C35, "UnionAll2".C37 AS C36, "UnionAll2".C38 AS C37, "UnionAll2".C39 AS C38, "UnionAll2".C40 AS C39, "UnionAll2".C41 AS C40, "UnionAll2".C42 AS C41, "UnionAll2".C43 AS C42, "UnionAll2".C44 AS C43, "UnionAll2".C45 AS C44, "UnionAll2".C46 AS C45, "UnionAll2".C47 AS C46, "UnionAll2".C48 AS C47, "UnionAll2".C49 AS C48, "UnionAll2".C50 AS C49, "UnionAll2".C51 AS C50, "UnionAll2".C52 AS C51, "UnionAll2".C53 AS C52, "UnionAll2".C54 AS C53, "UnionAll2".C55 AS C54, "UnionAll2".C56 AS C55, "UnionAll2".C57 AS C56, "UnionAll2".C58 AS C57, "UnionAll2".C59 AS C58, "UnionAll2".C60 AS C59, "UnionAll2".C61 AS C60, "UnionAll2".C62 AS C61, "UnionAll2".C63 AS C62, "UnionAll2".C64 AS C63, "UnionAll2".C65 AS C64, "UnionAll2".C66 AS C65, "UnionAll2".C67 AS C66, "UnionAll2".C68 AS C67, "UnionAll2".C69 AS C68, "UnionAll2".C70 AS C69, "UnionAll2".C71 AS C70, "UnionAll2".C72 AS C71, "UnionAll2".C73 AS C72, "UnionAll2".C74 AS C73, "UnionAll2".C75 AS C74, "UnionAll2".C76 AS C75, "UnionAll2".C77 AS C76, "UnionAll2".C78 AS C77, "UnionAll2".C79 AS C78, "UnionAll2".C80 AS C79, "UnionAll2".C81 AS C80, "UnionAll2".C82 AS C81, "UnionAll2".C83 AS C82, "UnionAll2".C84 AS C83, "UnionAll2".C85 AS C84, "UnionAll2".C86 AS C85, "UnionAll2".C87 AS C86, "UnionAll2".C88 AS C87, "UnionAll2".C89 AS C88, "UnionAll2".C90 AS C89, "UnionAll2".C91 AS C90, "UnionAll2".C92 AS C91, "UnionAll2".C93 AS C92, "UnionAll2".C94 AS C93, "UnionAll2".C95 AS C94, "UnionAll2".C96 AS C95, "UnionAll2".C97 AS C96, "UnionAll2".C98 AS C97, "UnionAll2".C99 AS C98, "UnionAll2".C100 AS C99, "UnionAll2".C101 AS C100, "UnionAll2".C102 AS C101, "UnionAll2".C1 AS C102, "UnionAll2".C103 AS C103, "UnionAll2".C104 AS C104, "UnionAll2".C105 AS C105, "UnionAll2".C106 AS C106, "UnionAll2".C107 AS C107, "UnionAll2".C108 AS C108, "UnionAll2".C109 AS C109, "UnionAll2".C110 AS C110, "UnionAll2".C111 AS C111, "UnionAll2".C112 AS C112, "UnionAll2".C113 AS C113, "UnionAll2".C114 AS C114, "UnionAll2".C115 AS C115, "UnionAll2".C116 AS C116, "UnionAll2".C117 AS C117, "UnionAll2".C118 AS C118, "UnionAll2".C119 AS C119, "UnionAll2".C120 AS C120, "UnionAll2".C121 AS C121, "UnionAll2".C122 AS C122, "UnionAll2".C123 AS C123, "UnionAll2".C124 AS C124, "UnionAll2".C125 AS C125, "UnionAll2".C126 AS C126, "UnionAll2".C127 AS C127, "UnionAll2".C128 AS C128, "UnionAll2".C129 AS C129, "UnionAll2".C130 AS C130, "UnionAll2".C131 AS C131, "UnionAll2".C132 AS C132, "UnionAll2".C133 AS C133, "UnionAll2".C134 AS C134, "UnionAll2".C135 AS C135, "UnionAll2".C136 AS C136, "UnionAll2".C137 AS C137, "UnionAll2".C138 AS C138, "UnionAll2".C139 AS C139, "UnionAll2".C140 AS C140, "UnionAll2".C141 AS C141, "UnionAll2".C142 AS C142, "UnionAll2".C143 AS C143, "UnionAll2".C144 AS C144, "UnionAll2".C145 AS C145, "UnionAll2".C146 AS C146, "UnionAll2".C147 AS C147, "UnionAll2".C148 AS C148, "UnionAll2".C149 AS C149, "UnionAll2".C150 AS C150, "UnionAll2".C151 AS C151, "UnionAll2".C152 AS C152, "UnionAll2".C153 AS C153, "UnionAll2".C154 AS C154, "UnionAll2".C155 AS C155, "UnionAll2".C156 AS C156, "UnionAll2".C157 AS C157, "UnionAll2".C158 AS C158, "UnionAll2".C159 AS C159, "UnionAll2".C160 AS C160, "UnionAll2".C161 AS C161, "UnionAll2".C162 AS C162, "UnionAll2".C163 AS C163, "UnionAll2".C164 AS C164, "UnionAll2".C165 AS C165, "UnionAll2".C166 AS C166, "UnionAll2".C167 AS C167, "UnionAll2".C168 AS C168, "UnionAll2".C169 AS C169, "UnionAll2".C170 AS C170, "UnionAll2".C171 AS C171, "UnionAll2".C172 AS C172, "UnionAll2".C173 AS C173, "UnionAll2".C174 AS C174, "UnionAll2".C175 AS C175, "UnionAll2".C176 AS C176, "UnionAll2".C177 AS C177, "UnionAll2".C178 AS C178, "UnionAll2".C179 AS C179, "UnionAll2".C180 AS C180, "UnionAll2".C181 AS C181, "UnionAll2".C182 AS C182, "UnionAll2".C183 AS C183, "UnionAll2".C184 AS C184, "UnionAll2".C185 AS C185, "UnionAll2".C186 AS C186, "UnionAll2".C187 AS C187, "UnionAll2".C188 AS C188, "UnionAll2".C189 AS C189, "UnionAll2".C190 AS C190, "UnionAll2".C191 AS C191, "UnionAll2".C192 AS C192, "UnionAll2".C193 AS C193, "UnionAll2".C194 AS C194, "UnionAll2".C195 AS C195, "UnionAll2".C196 AS C196, "UnionAll2".C197 AS C197, "UnionAll2".C198 AS C198, "UnionAll2".C199 AS C199, "UnionAll2".C200 AS C200, "UnionAll2".C201 AS C201, "UnionAll2".C202 AS C202, "UnionAll2".C203 AS C203 FROM (SELECT "UnionAll1".C1 AS C1, "UnionAll1".DEAL_CONFIRMATION_ID AS C2, "UnionAll1".DEAL_CONFIRM_ID AS C3, "UnionAll1".DEAL_CONFIRM_ID1 AS C4, "UnionAll1".DEAL_CONFIRM_ID2 AS C5, "UnionAll1".DEAL_HISTORY_ID AS C6, "UnionAll1".DATE_ENTERED AS C7, "UnionAll1".DATE_OF_DEAL AS C8, "UnionAll1".FLOW_DATE_START AS C9, "UnionAll1".FLOW_DATE_END AS C10, "UnionAll1".DEAL_NUMBER AS C11, "UnionAll1".BUS_ASSOCIATE_ID AS C12, "UnionAll1".SUPPLY_CONTACT_ID AS C13, "UnionAll1".CONTACT_OTHER AS C14, "UnionAll1".MO_CONTACT_ID AS C15, "UnionAll1".PIPELINE_ID AS C16, "UnionAll1".ZONE_ID AS C17, "UnionAll1".LOCATION_ID AS C18, "UnionAll1".TRANSFER_POINT_ID AS C19, "UnionAll1".DEAL_TYPE_ID AS C20, "UnionAll1".SUPPLY_CONTRACT_ID AS C21, "UnionAll1".GMS_BROKER_ACCT_NBR AS C22, "UnionAll1".RATE_PLAN_ID AS C23, "UnionAll1".PROMOTED AS C24, "UnionAll1".DELIVERED AS C25, "UnionAll1".CANCELED AS C26, "UnionAll1".DEAL_TERMS_CHANGED AS C27, "UnionAll1".DEAL_COMMENT AS C28, "UnionAll1".LOCKED_BY AS C29, "UnionAll1".ENTERED_BY AS C30, "UnionAll1".LAST_UPDATE_USER AS C31, "UnionAll1".LAST_UPDATE_TMS AS C32, "UnionAll1".WHAT_CHANGED AS C33, "UnionAll1".NEED_ACCOUNT AS C34, "UnionAll1".RESUBMITTED AS C35, "UnionAll1".STORAGE_TRANSFER AS C36, "UnionAll1".MO_CONTACT_OTHER AS C37, "UnionAll1".SPECIAL_CONDITIONS AS C38, "UnionAll1".ATTACHED_SHEET AS C39, "UnionAll1".HAS_ATTACHED_SHEET AS C40, "UnionAll1".HISTORY_TMS AS C41, "UnionAll1".HISTORY_USER AS C42, "UnionAll1".ROW_VERSION AS C43, "UnionAll1".DEAL_CONFIRMATION_ID1 AS C44, "UnionAll1".BUS_ASSOCIATE_CD AS C45, "UnionAll1".SUPPLY_CONTACT_NAME AS C46, "UnionAll1".MO_CONTACT_NAME AS C47, "UnionAll1".PIPELINE_CD AS C48, "UnionAll1".ZONE_CD AS C49, "UnionAll1".LOCATION_CD AS C50, "UnionAll1".TRANSFER_POINT_CD AS C51, "UnionAll1".DEAL_TYPE AS C52, "UnionAll1".SUPPLY_CONTRACT_NBR AS C53, "UnionAll1".DEAL552CODE_ID AS C54, "UnionAll1".DEAL_HISTORY_ID1 AS C55, "UnionAll1".DEAL_CONFIRM_ID3 AS C56, "UnionAll1".DEAL_CONFIRM_ID4 AS C57, "UnionAll1".DEAL_CONFIRM_ID5 AS C58, "UnionAll1".DEAL_CONFIRM_ID6 AS C59, "UnionAll1".DEAL_CONFIRM_ID7 AS C60, "UnionAll1".DEAL_CONFIRM_ID8 AS C61, "UnionAll1".DEAL_CONFIRM_ID9 AS C62, "UnionAll1".DEAL_CONFIRM_ID10 AS C63, "UnionAll1".DEAL_CONFIRM_ID11 AS C64, "UnionAll1".DEAL_CONFIRM_ID12 AS C65, "UnionAll1".DEAL_CONFIRM_ID13 AS C66, "UnionAll1".DEAL_CONFIRM_ID14 AS C67, "UnionAll1".C2 AS C68, "UnionAll1".C3 AS C69, "UnionAll1".DEAL_ID AS C70, "UnionAll1".C4 AS C71, "UnionAll1".DEAL_CONFIRM_ID15 AS C72, "UnionAll1".ACTION_DATE AS C73, "UnionAll1".CONF_SENT_DATE AS C74, "UnionAll1".CONF_SEND_METHOD_OTHER_DESC AS C75, "UnionAll1".CONF_RECEIVE_METHOD_OTHER_DESC AS C76, "UnionAll1".CONF_RECEIVE_DATE AS C77, "UnionAll1".CONF_COMMENT AS C78, "UnionAll1".ENTERED_BY1 AS C79, "UnionAll1".LAST_UPDATE_USER1 AS C80, "UnionAll1".LAST_UPDATE_TMS1 AS C81, "UnionAll1".CONFIRMATION_PDF AS C82, "UnionAll1".ROW_VERSION1 AS C83, "UnionAll1".DEAL_CONF_STATUS_ID AS C84, "UnionAll1".DEAL_CONF_STATUS AS C85, "UnionAll1".LAST_UPDATE_USER2 AS C86, "UnionAll1".LAST_UPDATE_TMS2 AS C87, "UnionAll1".DEAL_CONF_SEND_STATUS_ID AS C88, "UnionAll1".DEAL_CONF_SEND_STATUS AS C89, "UnionAll1".LAST_UPDATE_USER3 AS C90, "UnionAll1".LAST_UPDATE_TMS3 AS C91, "UnionAll1".DEAL_CONF_RECEIVE_METHOD_ID AS C92, "UnionAll1".DEAL_CONF_RECEIVE_METHOD AS C93, "UnionAll1".LAST_UPDATE_USER4 AS C94, "UnionAll1".LAST_UPDATE_TMS4 AS C95, "UnionAll1".CONTACT_METHOD_ID AS C96, "UnionAll1".METHOD AS C97, "UnionAll1".LAST_UPDATE_USER5 AS C98, "UnionAll1".LAST_UPDATE_TMS5 AS C99, "UnionAll1".DEAL_ID1 AS C100, "UnionAll1".DEAL_CONFIRM_ID16 AS C101, "UnionAll1".DEAL_ID2 AS C102, "UnionAll1".C5 AS C103, "UnionAll1".DEAL_PRICING_HISTORY_ID AS C104, "UnionAll1".DEAL_PRICING_ID AS C105, "UnionAll1".DEAL_ID3 AS C106, "UnionAll1".VOLUME_MIN AS C107, "UnionAll1".VOLUME_MAX AS C108, "UnionAll1".VOLUME_TYPE_ID AS C109, "UnionAll1".PRICE AS C110, "UnionAll1".DEAL_PRICE_TYPE_ID AS C111, "UnionAll1".PRICE_DESCRIPTION AS C112, "UnionAll1".PRICE_INDEX_PIPELINE_ID AS C113, "UnionAll1".PRICE_INDEX_LOCATION_ID AS C114, "UnionAll1".PRICE_INDEX_PRICE_TYPE_ID AS C115, "UnionAll1".PRICE_INDEX_HML_FLAG AS C116, "UnionAll1".PRICE_INDEX_ADDER AS C117, "UnionAll1".ENTERED_BY2 AS C118, "UnionAll1".LAST_UPDATE_USER6 AS C119, "UnionAll1".LAST_UPDATE_TMS6 AS C120, "UnionAll1".WHAT_CHANGED1 AS C121, "UnionAll1".HISTORY_TMS1 AS C122, "UnionAll1".HISTORY_USER1 AS C123, "UnionAll1".DEAL_CONFIRMATION_ID2 AS C124, "UnionAll1".DEAL_PRICE_TYPE AS C125, "UnionAll1".PIPELINE_CD1 AS C126, "UnionAll1".LOCATION_CD1 AS C127, "UnionAll1".INDEX_PRICE_TYPE AS C128, "UnionAll1".VOLUME_TYPE AS C129, "UnionAll1".ROW_VERSION2 AS C130, "UnionAll1".DEAL_HISTORY_ID2 AS C131, "UnionAll1".C6 AS C132, "UnionAll1".C7 AS C133, "UnionAll1".C8 AS C134, "UnionAll1".C9 AS C135, "UnionAll1".C10 AS C136, "UnionAll1".C11 AS C137, "UnionAll1".C12 AS C138, "UnionAll1".C13 AS C139, "UnionAll1".C14 AS C140, "UnionAll1".C15 AS C141, "UnionAll1".C16 AS C142, "UnionAll1".C17 AS C143, "UnionAll1".C18 AS C144, "UnionAll1".C19 AS C145, "UnionAll1".C20 AS C146, "UnionAll1".C21 AS C147, "UnionAll1".C22 AS C148, "UnionAll1".C23 AS C149, "UnionAll1".C24 AS C150, "UnionAll1".C25 AS C151, "UnionAll1".C26 AS C152, "UnionAll1".C27 AS C153, "UnionAll1".C28 AS C154, "UnionAll1".C29 AS C155, "UnionAll1".C30 AS C156, "UnionAll1".C31 AS C157, "UnionAll1".C32 AS C158, "UnionAll1".C33 AS C159, "UnionAll1".C34 AS C160, "UnionAll1".C35 AS C161, "UnionAll1".C36 AS C162, "UnionAll1".C37 AS C163, "UnionAll1".C38 AS C164, "UnionAll1".C39 AS C165, "UnionAll1".C40 AS C166, "UnionAll1".C41 AS C167, "UnionAll1".C42 AS C168, "UnionAll1".C43 AS C169, "UnionAll1".C44 AS C170, "UnionAll1".C45 AS C171, "UnionAll1".C46 AS C172, "UnionAll1".C47 AS C173, "UnionAll1".C48 AS C174, "UnionAll1".C49 AS C175, "UnionAll1".C50 AS C176, "UnionAll1".C51 AS C177, "UnionAll1".C52 AS C178, "UnionAll1".C53 AS C179, "UnionAll1".C54 AS C180, "UnionAll1".C55 AS C181, "UnionAll1".C56 AS C182, "UnionAll1".C57 AS C183, "UnionAll1".C58 AS C184, "UnionAll1".C59 AS C185, "UnionAll1".C60 AS C186, "UnionAll1".C61 AS C187, "UnionAll1".C62 AS C188, "UnionAll1".C63 AS C189, "UnionAll1".C64 AS C190, "UnionAll1".C65 AS C191, "UnionAll1".C66 AS C192, "UnionAll1".C67 AS C193, "UnionAll1".C68 AS C194, "UnionAll1".C69 AS C195, "UnionAll1".C70 AS C196, "UnionAll1".C71 AS C197, "UnionAll1".C72 AS C198, "UnionAll1".C73 AS C199, "UnionAll1".C74 AS C200, "UnionAll1".C75 AS C201, "UnionAll1".C76 AS C202, "UnionAll1".C77 AS C203 FROM (SELECT "Project2".C1 AS C1, "Project1".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Project1".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID, "Project1".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID1, "Project1".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID2, "Extent5".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, "Extent5".DATE_ENTERED AS DATE_ENTERED, "Extent5".DATE_OF_DEAL AS DATE_OF_DEAL, "Extent5".FLOW_DATE_START AS FLOW_DATE_START, "Extent5".FLOW_DATE_END AS FLOW_DATE_END, "Extent5".DEAL_NUMBER AS DEAL_NUMBER, "Extent5".BUS_ASSOCIATE_ID AS BUS_ASSOCIATE_ID, "Extent5".SUPPLY_CONTACT_ID AS SUPPLY_CONTACT_ID, "Extent5".CONTACT_OTHER AS CONTACT_OTHER, "Extent5".MO_CONTACT_ID AS MO_CONTACT_ID, "Extent5".PIPELINE_ID AS PIPELINE_ID, "Extent5".ZONE_ID AS ZONE_ID, "Extent5".LOCATION_ID AS LOCATION_ID, "Extent5".TRANSFER_POINT_ID AS TRANSFER_POINT_ID, "Extent5".DEAL_TYPE_ID AS DEAL_TYPE_ID, "Extent5".SUPPLY_CONTRACT_ID AS SUPPLY_CONTRACT_ID, "Extent5".GMS_BROKER_ACCT_NBR AS GMS_BROKER_ACCT_NBR, "Extent5".RATE_PLAN_ID AS RATE_PLAN_ID, "Extent5".PROMOTED AS PROMOTED, "Extent5".DELIVERED AS DELIVERED, "Extent5".CANCELED AS CANCELED, "Extent5".DEAL_TERMS_CHANGED AS DEAL_TERMS_CHANGED, "Extent5".DEAL_COMMENT AS DEAL_COMMENT, "Extent5".LOCKED_BY AS LOCKED_BY, "Extent5".ENTERED_BY AS ENTERED_BY, "Extent5".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent5".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent5".WHAT_CHANGED AS WHAT_CHANGED, "Extent5".NEED_ACCOUNT AS NEED_ACCOUNT, "Extent5".RESUBMITTED AS RESUBMITTED, "Extent5".STORAGE_TRANSFER AS STORAGE_TRANSFER, "Extent5".MO_CONTACT_OTHER AS MO_CONTACT_OTHER, "Extent5".SPECIAL_CONDITIONS AS SPECIAL_CONDITIONS, "Extent5".ATTACHED_SHEET AS ATTACHED_SHEET, "Extent5".HAS_ATTACHED_SHEET AS HAS_ATTACHED_SHEET, "Extent5".HISTORY_TMS AS HISTORY_TMS, "Extent5".HISTORY_USER AS HISTORY_USER, "Extent5".ROW_VERSION AS ROW_VERSION, "Extent5".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID1, "Extent5".BUS_ASSOCIATE_CD AS BUS_ASSOCIATE_CD, "Extent5".SUPPLY_CONTACT_NAME AS SUPPLY_CONTACT_NAME, "Extent5".MO_CONTACT_NAME AS MO_CONTACT_NAME, "Extent5".PIPELINE_CD AS PIPELINE_CD, "Extent5".ZONE_CD AS ZONE_CD, "Extent5".LOCATION_CD AS LOCATION_CD, "Extent5".TRANSFER_POINT_CD AS TRANSFER_POINT_CD, "Extent5".DEAL_TYPE AS DEAL_TYPE, "Extent5".SUPPLY_CONTRACT_NBR AS SUPPLY_CONTRACT_NBR, "Extent5".DEAL552CODE_ID AS DEAL552CODE_ID, "Extent6".DEAL_HISTORY_ID AS DEAL_HISTORY_ID1, "Extent7".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID3, "Extent8".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID4, "Extent8".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID5, "Extent10".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID6, "Extent10".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID7, "Extent12".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID8, "Extent12".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID9, "Extent14".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID10, "Extent14".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID11, "Extent16".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID12, "Extent16".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID13, "Extent17".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID14, 1 AS C2, 1 AS C3, "Extent6".DEAL_ID AS DEAL_ID, 1 AS C4, "Extent7".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID15, "Extent7".ACTION_DATE AS ACTION_DATE, "Extent7".CONF_SENT_DATE AS CONF_SENT_DATE, "Extent7".CONF_SEND_METHOD_OTHER_DESC AS CONF_SEND_METHOD_OTHER_DESC, "Extent7".CONF_RECEIVE_METHOD_OTHER_DESC AS CONF_RECEIVE_METHOD_OTHER_DESC, "Extent7".CONF_RECEIVE_DATE AS CONF_RECEIVE_DATE, "Extent7".CONF_COMMENT AS CONF_COMMENT, "Extent7".ENTERED_BY AS ENTERED_BY1, "Extent7".LAST_UPDATE_USER AS LAST_UPDATE_USER1, "Extent7".LAST_UPDATE_TMS AS LAST_UPDATE_TMS1, "Extent7".CONFIRMATION_PDF AS CONFIRMATION_PDF, "Extent7".ROW_VERSION AS ROW_VERSION1, "Extent9".DEAL_CONF_STATUS_ID AS DEAL_CONF_STATUS_ID, "Extent9".DEAL_CONF_STATUS AS DEAL_CONF_STATUS, "Extent9".LAST_UPDATE_USER AS LAST_UPDATE_USER2, "Extent9".LAST_UPDATE_TMS AS LAST_UPDATE_TMS2, "Extent11".DEAL_CONF_SEND_STATUS_ID AS DEAL_CONF_SEND_STATUS_ID, "Extent11".DEAL_CONF_SEND_STATUS AS DEAL_CONF_SEND_STATUS, "Extent11".LAST_UPDATE_USER AS LAST_UPDATE_USER3, "Extent11".LAST_UPDATE_TMS AS LAST_UPDATE_TMS3, "Extent13".DEAL_CONF_RECEIVE_METHOD_ID AS DEAL_CONF_RECEIVE_METHOD_ID, "Extent13".DEAL_CONF_RECEIVE_METHOD AS DEAL_CONF_RECEIVE_METHOD, "Extent13".LAST_UPDATE_USER AS LAST_UPDATE_USER4, "Extent13".LAST_UPDATE_TMS AS LAST_UPDATE_TMS4, "Extent15".CONTACT_METHOD_ID AS CONTACT_METHOD_ID, "Extent15".METHOD AS METHOD, "Extent15".LAST_UPDATE_USER AS LAST_UPDATE_USER5, "Extent15".LAST_UPDATE_TMS AS LAST_UPDATE_TMS5, "Extent16".DEAL_ID AS DEAL_ID1, "Extent17".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID16, "Project1".DEAL_ID AS DEAL_ID2, "Project2".C1 AS C5, "Project2".DEAL_PRICING_HISTORY_ID AS DEAL_PRICING_HISTORY_ID, "Project2".DEAL_PRICING_ID AS DEAL_PRICING_ID, "Project2".DEAL_ID AS DEAL_ID3, "Project2".VOLUME_MIN AS VOLUME_MIN, "Project2".VOLUME_MAX AS VOLUME_MAX, "Project2".VOLUME_TYPE_ID AS VOLUME_TYPE_ID, "Project2".PRICE AS PRICE, "Project2".DEAL_PRICE_TYPE_ID AS DEAL_PRICE_TYPE_ID, "Project2".PRICE_DESCRIPTION AS PRICE_DESCRIPTION, "Project2".PRICE_INDEX_PIPELINE_ID AS PRICE_INDEX_PIPELINE_ID, "Project2".PRICE_INDEX_LOCATION_ID AS PRICE_INDEX_LOCATION_ID, "Project2".PRICE_INDEX_PRICE_TYPE_ID AS PRICE_INDEX_PRICE_TYPE_ID, "Project2".PRICE_INDEX_HML_FLAG AS PRICE_INDEX_HML_FLAG, "Project2".PRICE_INDEX_ADDER AS PRICE_INDEX_ADDER, "Project2".ENTERED_BY AS ENTERED_BY2, "Project2".LAST_UPDATE_USER AS LAST_UPDATE_USER6, "Project2".LAST_UPDATE_TMS AS LAST_UPDATE_TMS6, "Project2".WHAT_CHANGED AS WHAT_CHANGED1, "Project2".HISTORY_TMS AS HISTORY_TMS1, "Project2".HISTORY_USER AS HISTORY_USER1, "Project2".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID2, "Project2".DEAL_PRICE_TYPE AS DEAL_PRICE_TYPE, "Project2".PIPELINE_CD AS PIPELINE_CD1, "Project2".LOCATION_CD AS LOCATION_CD1, "Project2".INDEX_PRICE_TYPE AS INDEX_PRICE_TYPE, "Project2".VOLUME_TYPE AS VOLUME_TYPE, "Project2".ROW_VERSION AS ROW_VERSION2, "Project2".DEAL_HISTORY_ID AS DEAL_HISTORY_ID2, FLOOR(CAST(NULL AS NUMBER)) AS C6, FLOOR(CAST(NULL AS NUMBER)) AS C7, CAST(NULL AS TIMESTAMP(9)) AS C8, TO_CHAR(NULL) AS C9, TO_CHAR(NULL) AS C10, CAST(NULL AS TIMESTAMP(9)) AS C11, TO_CHAR(NULL) AS C12, TO_CHAR(NULL) AS C13, TO_CHAR(NULL) AS C14, TO_CHAR(NULL) AS C15, CAST(NULL AS TIMESTAMP(9)) AS C16, TO_CHAR(NULL) AS C17, TO_CHAR(NULL) AS C18, TO_CHAR(NULL) AS C19, CAST(NULL AS TIMESTAMP(9)) AS C20, TO_CHAR(NULL) AS C21, FLOOR(CAST(NULL AS NUMBER)) AS C22, FLOOR(CAST(NULL AS NUMBER)) AS C23, FLOOR(CAST(NULL AS NUMBER)) AS C24, FLOOR(CAST(NULL AS NUMBER)) AS C25, TO_CHAR(NULL) AS C26, CAST(NULL AS TIMESTAMP(9)) AS C27, FLOOR(CAST(NULL AS NUMBER)) AS C28, TO_CHAR(NULL) AS C29, TO_CHAR(NULL) AS C30, CAST(NULL AS TIMESTAMP(9)) AS C31, FLOOR(CAST(NULL AS NUMBER)) AS C32, FLOOR(CAST(NULL AS NUMBER)) AS C33, FLOOR(CAST(NULL AS NUMBER)) AS C34, FLOOR(CAST(NULL AS NUMBER)) AS C35, FLOOR(CAST(NULL AS NUMBER)) AS C36, FLOOR(CAST(NULL AS NUMBER)) AS C37, FLOOR(CAST(NULL AS NUMBER)) AS C38, TO_CHAR(NULL) AS C39, FLOOR(CAST(NULL AS NUMBER)) AS C40, TO_CHAR(NULL) AS C41, TO_CHAR(NULL) AS C42, TO_CHAR(NULL) AS C43, TO_CHAR(NULL) AS C44, TO_CHAR(NULL) AS C45, TO_CHAR(NULL) AS C46, TO_CHAR(NULL) AS C47, TO_CHAR(NULL) AS C48, TO_CHAR(NULL) AS C49, TO_CHAR(NULL) AS C50, TO_CHAR(NULL) AS C51, TO_CHAR(NULL) AS C52, TO_CHAR(NULL) AS C53, TO_CHAR(NULL) AS C54, FLOOR(CAST(NULL AS NUMBER)) AS C55, FLOOR(CAST(NULL AS NUMBER)) AS C56, FLOOR(CAST(NULL AS NUMBER)) AS C57, TO_CHAR(NULL) AS C58, TO_CHAR(NULL) AS C59, CAST(NULL AS TIMESTAMP(9)) AS C60, FLOOR(CAST(NULL AS NUMBER)) AS C61, FLOOR(CAST(NULL AS NUMBER)) AS C62, FLOOR(CAST(NULL AS NUMBER)) AS C63, FLOOR(CAST(NULL AS NUMBER)) AS C64, FLOOR(CAST(NULL AS NUMBER)) AS C65, FLOOR(CAST(NULL AS NUMBER)) AS C66, FLOOR(CAST(NULL AS NUMBER)) AS C67, FLOOR(CAST(NULL AS NUMBER)) AS C68, FLOOR(CAST(NULL AS NUMBER)) AS C69, TO_CHAR(NULL) AS C70, CAST(NULL AS TIMESTAMP(9)) AS C71, FLOOR(CAST(NULL AS NUMBER)) AS C72, TO_CHAR(NULL) AS C73, TO_CHAR(NULL) AS C74, CAST(NULL AS TIMESTAMP(9)) AS C75, FLOOR(CAST(NULL AS NUMBER)) AS C76, FLOOR(CAST(NULL AS NUMBER)) AS C77 FROM (SELECT "Extent1".DEAL_ID AS DEAL_ID, "Extent1".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent1".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, "Extent2".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID, "Extent3".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID1, (SELECT Count(1) AS A1 FROM ( SELECT "Extent4".DEAL_PRICING_HISTORY_ID AS DEAL_PRICING_HISTORY_ID, "Extent4".DEAL_PRICING_ID AS DEAL_PRICING_ID, "Extent4".DEAL_ID AS DEAL_ID, "Extent4".VOLUME_MIN AS VOLUME_MIN, "Extent4".VOLUME_MAX AS VOLUME_MAX, "Extent4".VOLUME_TYPE_ID AS VOLUME_TYPE_ID, "Extent4".PRICE AS PRICE, "Extent4".DEAL_PRICE_TYPE_ID AS DEAL_PRICE_TYPE_ID, "Extent4".PRICE_DESCRIPTION AS PRICE_DESCRIPTION, "Extent4".PRICE_INDEX_PIPELINE_ID AS PRICE_INDEX_PIPELINE_ID, "Extent4".PRICE_INDEX_LOCATION_ID AS PRICE_INDEX_LOCATION_ID, "Extent4".PRICE_INDEX_PRICE_TYPE_ID AS PRICE_INDEX_PRICE_TYPE_ID, "Extent4".PRICE_INDEX_HML_FLAG AS PRICE_INDEX_HML_FLAG, "Extent4".PRICE_INDEX_ADDER AS PRICE_INDEX_ADDER, "Extent4".ENTERED_BY AS ENTERED_BY, "Extent4".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent4".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent4".WHAT_CHANGED AS WHAT_CHANGED, "Extent4".HISTORY_TMS AS HISTORY_TMS, "Extent4".HISTORY_USER AS HISTORY_USER, "Extent4".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent4".DEAL_PRICE_TYPE AS DEAL_PRICE_TYPE, "Extent4".PIPELINE_CD AS PIPELINE_CD, "Extent4".LOCATION_CD AS LOCATION_CD, "Extent4".INDEX_PRICE_TYPE AS INDEX_PRICE_TYPE, "Extent4".VOLUME_TYPE AS VOLUME_TYPE, "Extent4".ROW_VERSION AS ROW_VERSION, "Extent4".DEAL_HISTORY_ID AS DEAL_HISTORY_ID FROM GCSD9.DEAL_PRICING_HISTORY "Extent4" WHERE "Extent4".DEAL_HISTORY_ID IS NOT NULL ) "Filter2" WHERE "Extent1".DEAL_HISTORY_ID = "Filter2".DEAL_HISTORY_ID) AS C1 FROM GCSD9.DEAL_DEAL_CONF "Extent1" LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent2" ON "Extent1".DEAL_CONFIRMATION_ID = "Extent2".DEAL_CONFIRM_ID INNER JOIN GCSD9.DEAL_CONFIRMATION "Extent3" ON "Extent2".DEAL_CONFIRM_ID = "Extent3".DEAL_CONFIRM_ID WHERE "Extent3".DEAL_CONFIRM_ID IS NOT NULL ) "Project1" LEFT OUTER JOIN GCSD9.DEAL_HISTORY "Extent5" ON "Project1".DEAL_HISTORY_ID = "Extent5".DEAL_HISTORY_ID LEFT OUTER JOIN GCSD9.DEAL_HISTORY "Extent6" ON "Project1".DEAL_HISTORY_ID = "Extent6".DEAL_HISTORY_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent7" ON "Project1".DEAL_CONFIRM_ID = "Extent7".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent8" ON "Project1".DEAL_CONFIRM_ID = "Extent8".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_STATUS "Extent9" ON "Extent8".CONF_STATUS_ID = "Extent9".DEAL_CONF_STATUS_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent10" ON "Project1".DEAL_CONFIRM_ID = "Extent10".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_SEND_STATUS "Extent11" ON "Extent10".CONF_SEND_STATUS_ID = "Extent11".DEAL_CONF_SEND_STATUS_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent12" ON "Project1".DEAL_CONFIRM_ID = "Extent12".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_RECEIVE_METHOD "Extent13" ON "Extent12".CONF_RECEIVE_METHOD_ID = "Extent13".DEAL_CONF_RECEIVE_METHOD_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent14" ON "Project1".DEAL_CONFIRM_ID = "Extent14".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.CONTACT_METHOD "Extent15" ON "Extent14".CONF_SEND_METHOD_ID = "Extent15".CONTACT_METHOD_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent16" ON "Project1".DEAL_CONFIRM_ID = "Extent16".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent17" ON "Project1".DEAL_CONFIRM_ID = "Extent17".DEAL_CONFIRM_ID LEFT OUTER JOIN (SELECT "Extent18".DEAL_PRICING_HISTORY_ID AS DEAL_PRICING_HISTORY_ID, "Extent18".DEAL_PRICING_ID AS DEAL_PRICING_ID, "Extent18".DEAL_ID AS DEAL_ID, "Extent18".VOLUME_MIN AS VOLUME_MIN, "Extent18".VOLUME_MAX AS VOLUME_MAX, "Extent18".VOLUME_TYPE_ID AS VOLUME_TYPE_ID, "Extent18".PRICE AS PRICE, "Extent18".DEAL_PRICE_TYPE_ID AS DEAL_PRICE_TYPE_ID, "Extent18".PRICE_DESCRIPTION AS PRICE_DESCRIPTION, "Extent18".PRICE_INDEX_PIPELINE_ID AS PRICE_INDEX_PIPELINE_ID, "Extent18".PRICE_INDEX_LOCATION_ID AS PRICE_INDEX_LOCATION_ID, "Extent18".PRICE_INDEX_PRICE_TYPE_ID AS PRICE_INDEX_PRICE_TYPE_ID, "Extent18".PRICE_INDEX_HML_FLAG AS PRICE_INDEX_HML_FLAG, "Extent18".PRICE_INDEX_ADDER AS PRICE_INDEX_ADDER, "Extent18".ENTERED_BY AS ENTERED_BY, "Extent18".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent18".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent18".WHAT_CHANGED AS WHAT_CHANGED, "Extent18".HISTORY_TMS AS HISTORY_TMS, "Extent18".HISTORY_USER AS HISTORY_USER, "Extent18".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent18".DEAL_PRICE_TYPE AS DEAL_PRICE_TYPE, "Extent18".PIPELINE_CD AS PIPELINE_CD, "Extent18".LOCATION_CD AS LOCATION_CD, "Extent18".INDEX_PRICE_TYPE AS INDEX_PRICE_TYPE, "Extent18".VOLUME_TYPE AS VOLUME_TYPE, "Extent18".ROW_VERSION AS ROW_VERSION, "Extent18".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, 1 AS C1 FROM GCSD9.DEAL_PRICING_HISTORY "Extent18" WHERE "Extent18".DEAL_HISTORY_ID IS NOT NULL ) "Project2" ON "Project1".DEAL_HISTORY_ID = "Project2".DEAL_HISTORY_ID WHERE "Project1".C1 > 0 UNION ALL SELECT 2 AS C1, "Project4".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Project4".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID, "Project4".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID1, "Project4".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID2, "Extent23".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, "Extent23".DATE_ENTERED AS DATE_ENTERED, "Extent23".DATE_OF_DEAL AS DATE_OF_DEAL, "Extent23".FLOW_DATE_START AS FLOW_DATE_START, "Extent23".FLOW_DATE_END AS FLOW_DATE_END, "Extent23".DEAL_NUMBER AS DEAL_NUMBER, "Extent23".BUS_ASSOCIATE_ID AS BUS_ASSOCIATE_ID, "Extent23".SUPPLY_CONTACT_ID AS SUPPLY_CONTACT_ID, "Extent23".CONTACT_OTHER AS CONTACT_OTHER, "Extent23".MO_CONTACT_ID AS MO_CONTACT_ID, "Extent23".PIPELINE_ID AS PIPELINE_ID, "Extent23".ZONE_ID AS ZONE_ID, "Extent23".LOCATION_ID AS LOCATION_ID, "Extent23".TRANSFER_POINT_ID AS TRANSFER_POINT_ID, "Extent23".DEAL_TYPE_ID AS DEAL_TYPE_ID, "Extent23".SUPPLY_CONTRACT_ID AS SUPPLY_CONTRACT_ID, "Extent23".GMS_BROKER_ACCT_NBR AS GMS_BROKER_ACCT_NBR, "Extent23".RATE_PLAN_ID AS RATE_PLAN_ID, "Extent23".PROMOTED AS PROMOTED, "Extent23".DELIVERED AS DELIVERED, "Extent23".CANCELED AS CANCELED, "Extent23".DEAL_TERMS_CHANGED AS DEAL_TERMS_CHANGED, "Extent23".DEAL_COMMENT AS DEAL_COMMENT, "Extent23".LOCKED_BY AS LOCKED_BY, "Extent23".ENTERED_BY AS ENTERED_BY, "Extent23".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent23".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent23".WHAT_CHANGED AS WHAT_CHANGED, "Extent23".NEED_ACCOUNT AS NEED_ACCOUNT, "Extent23".RESUBMITTED AS RESUBMITTED, "Extent23".STORAGE_TRANSFER AS STORAGE_TRANSFER, "Extent23".MO_CONTACT_OTHER AS MO_CONTACT_OTHER, "Extent23".SPECIAL_CONDITIONS AS SPECIAL_CONDITIONS, "Extent23".ATTACHED_SHEET AS ATTACHED_SHEET, "Extent23".HAS_ATTACHED_SHEET AS HAS_ATTACHED_SHEET, "Extent23".HISTORY_TMS AS HISTORY_TMS, "Extent23".HISTORY_USER AS HISTORY_USER, "Extent23".ROW_VERSION AS ROW_VERSION, "Extent23".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID1, "Extent23".BUS_ASSOCIATE_CD AS BUS_ASSOCIATE_CD, "Extent23".SUPPLY_CONTACT_NAME AS SUPPLY_CONTACT_NAME, "Extent23".MO_CONTACT_NAME AS MO_CONTACT_NAME, "Extent23".PIPELINE_CD AS PIPELINE_CD, "Extent23".ZONE_CD AS ZONE_CD, "Extent23".LOCATION_CD AS LOCATION_CD, "Extent23".TRANSFER_POINT_CD AS TRANSFER_POINT_CD, "Extent23".DEAL_TYPE AS DEAL_TYPE, "Extent23".SUPPLY_CONTRACT_NBR AS SUPPLY_CONTRACT_NBR, "Extent23".DEAL552CODE_ID AS DEAL552CODE_ID, "Extent24".DEAL_HISTORY_ID AS DEAL_HISTORY_ID1, "Extent25".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID3, "Extent26".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID4, "Extent26".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID5, "Extent28".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID6, "Extent28".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID7, "Extent30".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID8, "Extent30".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID9, "Extent32".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID10, "Extent32".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID11, "Extent34".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID12, "Extent34".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID13, "Extent35".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID14, 1 AS C2, 1 AS C3, "Extent24".DEAL_ID AS DEAL_ID, 1 AS C4, "Extent25".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID15, "Extent25".ACTION_DATE AS ACTION_DATE, "Extent25".CONF_SENT_DATE AS CONF_SENT_DATE, "Extent25".CONF_SEND_METHOD_OTHER_DESC AS CONF_SEND_METHOD_OTHER_DESC, "Extent25".CONF_RECEIVE_METHOD_OTHER_DESC AS CONF_RECEIVE_METHOD_OTHER_DESC, "Extent25".CONF_RECEIVE_DATE AS CONF_RECEIVE_DATE, "Extent25".CONF_COMMENT AS CONF_COMMENT, "Extent25".ENTERED_BY AS ENTERED_BY1, "Extent25".LAST_UPDATE_USER AS LAST_UPDATE_USER1, "Extent25".LAST_UPDATE_TMS AS LAST_UPDATE_TMS1, "Extent25".CONFIRMATION_PDF AS CONFIRMATION_PDF, "Extent25".ROW_VERSION AS ROW_VERSION1, "Extent27".DEAL_CONF_STATUS_ID AS DEAL_CONF_STATUS_ID, "Extent27".DEAL_CONF_STATUS AS DEAL_CONF_STATUS, "Extent27".LAST_UPDATE_USER AS LAST_UPDATE_USER2, "Extent27".LAST_UPDATE_TMS AS LAST_UPDATE_TMS2, "Extent29".DEAL_CONF_SEND_STATUS_ID AS DEAL_CONF_SEND_STATUS_ID, "Extent29".DEAL_CONF_SEND_STATUS AS DEAL_CONF_SEND_STATUS, "Extent29".LAST_UPDATE_USER AS LAST_UPDATE_USER3, "Extent29".LAST_UPDATE_TMS AS LAST_UPDATE_TMS3, "Extent31".DEAL_CONF_RECEIVE_METHOD_ID AS DEAL_CONF_RECEIVE_METHOD_ID, "Extent31".DEAL_CONF_RECEIVE_METHOD AS DEAL_CONF_RECEIVE_METHOD, "Extent31".LAST_UPDATE_USER AS LAST_UPDATE_USER4, "Extent31".LAST_UPDATE_TMS AS LAST_UPDATE_TMS4, "Extent33".CONTACT_METHOD_ID AS CONTACT_METHOD_ID, "Extent33".METHOD AS METHOD, "Extent33".LAST_UPDATE_USER AS LAST_UPDATE_USER5, "Extent33".LAST_UPDATE_TMS AS LAST_UPDATE_TMS5, "Extent34".DEAL_ID AS DEAL_ID1, "Extent35".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID16, "Project4".DEAL_ID AS DEAL_ID2, FLOOR(CAST(NULL AS NUMBER)) AS C5, FLOOR(CAST(NULL AS NUMBER)) AS C6, FLOOR(CAST(NULL AS NUMBER)) AS C7, FLOOR(CAST(NULL AS NUMBER)) AS C8, FLOOR(CAST(NULL AS NUMBER)) AS C9, FLOOR(CAST(NULL AS NUMBER)) AS C10, FLOOR(CAST(NULL AS NUMBER)) AS C11, CAST(NULL AS NUMBER) AS C12, FLOOR(CAST(NULL AS NUMBER)) AS C13, TO_CHAR(NULL) AS C14, TO_CHAR(NULL) AS C15, TO_CHAR(NULL) AS C16, FLOOR(CAST(NULL AS NUMBER)) AS C17, TO_CHAR(NULL) AS C18, CAST(NULL AS NUMBER) AS C19, TO_CHAR(NULL) AS C20, TO_CHAR(NULL) AS C21, CAST(NULL AS TIMESTAMP(9)) AS C22, TO_CHAR(NULL) AS C23, CAST(NULL AS TIMESTAMP(9)) AS C24, TO_CHAR(NULL) AS C25, FLOOR(CAST(NULL AS NUMBER)) AS C26, TO_CHAR(NULL) AS C27, TO_CHAR(NULL) AS C28, TO_CHAR(NULL) AS C29, TO_CHAR(NULL) AS C30, TO_CHAR(NULL) AS C31, FLOOR(CAST(NULL AS NUMBER)) AS C32, FLOOR(CAST(NULL AS NUMBER)) AS C33, 1 AS C34, "Extent36".CONF_HISTORY_ID AS CONF_HISTORY_ID, "Extent36".ACTION_DATE AS ACTION_DATE1, "Extent36".CONF_STATUS AS CONF_STATUS, "Extent36".CONF_SEND_STATUS AS CONF_SEND_STATUS, "Extent36".CONF_SENT_DATE AS CONF_SENT_DATE1, "Extent36".CONF_SEND_METHOD AS CONF_SEND_METHOD, "Extent36".CONF_SEND_METHOD_OTHER_DESC AS CONF_SEND_METHOD_OTHER_DESC1, "Extent36".CONF_RECEIVE_METHOD AS CONF_RECEIVE_METHOD, "Extent36".CONF_RECEIVE_METHOD_OTHER_DESC AS CONF_RECEIVE_METHOD_OTHER_DESC1, "Extent36".CONF_RECEIVE_DATE AS CONF_RECEIVE_DATE1, "Extent36".CONF_COMMENT AS CONF_COMMENT1, "Extent36".ENTERED_BY AS ENTERED_BY2, "Extent36".LAST_UPDATE_USER AS LAST_UPDATE_USER6, "Extent36".LAST_UPDATE_TMS AS LAST_UPDATE_TMS6, "Extent36".HISTORY_AUDIT_DESC AS HISTORY_AUDIT_DESC, "Extent36".ROW_VERSION AS ROW_VERSION2, "Extent36".DEAL_CONF_ID AS DEAL_CONF_ID, FLOOR(CAST(NULL AS NUMBER)) AS C35, FLOOR(CAST(NULL AS NUMBER)) AS C36, TO_CHAR(NULL) AS C37, CAST(NULL AS TIMESTAMP(9)) AS C38, FLOOR(CAST(NULL AS NUMBER)) AS C39, TO_CHAR(NULL) AS C40, TO_CHAR(NULL) AS C41, CAST(NULL AS TIMESTAMP(9)) AS C42, FLOOR(CAST(NULL AS NUMBER)) AS C43, FLOOR(CAST(NULL AS NUMBER)) AS C44, FLOOR(CAST(NULL AS NUMBER)) AS C45, FLOOR(CAST(NULL AS NUMBER)) AS C46, FLOOR(CAST(NULL AS NUMBER)) AS C47, FLOOR(CAST(NULL AS NUMBER)) AS C48, FLOOR(CAST(NULL AS NUMBER)) AS C49, TO_CHAR(NULL) AS C50, FLOOR(CAST(NULL AS NUMBER)) AS C51, TO_CHAR(NULL) AS C52, TO_CHAR(NULL) AS C53, TO_CHAR(NULL) AS C54, TO_CHAR(NULL) AS C55, TO_CHAR(NULL) AS C56, TO_CHAR(NULL) AS C57, TO_CHAR(NULL) AS C58, TO_CHAR(NULL) AS C59, TO_CHAR(NULL) AS C60, TO_CHAR(NULL) AS C61, TO_CHAR(NULL) AS C62, TO_CHAR(NULL) AS C63, TO_CHAR(NULL) AS C64, TO_CHAR(NULL) AS C65, FLOOR(CAST(NULL AS NUMBER)) AS C66, FLOOR(CAST(NULL AS NUMBER)) AS C67, FLOOR(CAST(NULL AS NUMBER)) AS C68, TO_CHAR(NULL) AS C69, TO_CHAR(NULL) AS C70, CAST(NULL AS TIMESTAMP(9)) AS C71, FLOOR(CAST(NULL AS NUMBER)) AS C72, FLOOR(CAST(NULL AS NUMBER)) AS C73, FLOOR(CAST(NULL AS NUMBER)) AS C74, FLOOR(CAST(NULL AS NUMBER)) AS C75, FLOOR(CAST(NULL AS NUMBER)) AS C76, FLOOR(CAST(NULL AS NUMBER)) AS C77, FLOOR(CAST(NULL AS NUMBER)) AS C78, FLOOR(CAST(NULL AS NUMBER)) AS C79, FLOOR(CAST(NULL AS NUMBER)) AS C80, TO_CHAR(NULL) AS C81, CAST(NULL AS TIMESTAMP(9)) AS C82, FLOOR(CAST(NULL AS NUMBER)) AS C83, TO_CHAR(NULL) AS C84, TO_CHAR(NULL) AS C85, CAST(NULL AS TIMESTAMP(9)) AS C86, FLOOR(CAST(NULL AS NUMBER)) AS C87, FLOOR(CAST(NULL AS NUMBER)) AS C88 FROM (SELECT "Extent19".DEAL_ID AS DEAL_ID, "Extent19".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent19".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, "Extent20".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID, "Extent21".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID1, (SELECT Count(1) AS A1 FROM ( SELECT "Extent22".DEAL_PRICING_HISTORY_ID AS DEAL_PRICING_HISTORY_ID, "Extent22".DEAL_PRICING_ID AS DEAL_PRICING_ID, "Extent22".DEAL_ID AS DEAL_ID, "Extent22".VOLUME_MIN AS VOLUME_MIN, "Extent22".VOLUME_MAX AS VOLUME_MAX, "Extent22".VOLUME_TYPE_ID AS VOLUME_TYPE_ID, "Extent22".PRICE AS PRICE, "Extent22".DEAL_PRICE_TYPE_ID AS DEAL_PRICE_TYPE_ID, "Extent22".PRICE_DESCRIPTION AS PRICE_DESCRIPTION, "Extent22".PRICE_INDEX_PIPELINE_ID AS PRICE_INDEX_PIPELINE_ID, "Extent22".PRICE_INDEX_LOCATION_ID AS PRICE_INDEX_LOCATION_ID, "Extent22".PRICE_INDEX_PRICE_TYPE_ID AS PRICE_INDEX_PRICE_TYPE_ID, "Extent22".PRICE_INDEX_HML_FLAG AS PRICE_INDEX_HML_FLAG, "Extent22".PRICE_INDEX_ADDER AS PRICE_INDEX_ADDER, "Extent22".ENTERED_BY AS ENTERED_BY, "Extent22".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent22".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent22".WHAT_CHANGED AS WHAT_CHANGED, "Extent22".HISTORY_TMS AS HISTORY_TMS, "Extent22".HISTORY_USER AS HISTORY_USER, "Extent22".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent22".DEAL_PRICE_TYPE AS DEAL_PRICE_TYPE, "Extent22".PIPELINE_CD AS PIPELINE_CD, "Extent22".LOCATION_CD AS LOCATION_CD, "Extent22".INDEX_PRICE_TYPE AS INDEX_PRICE_TYPE, "Extent22".VOLUME_TYPE AS VOLUME_TYPE, "Extent22".ROW_VERSION AS ROW_VERSION, "Extent22".DEAL_HISTORY_ID AS DEAL_HISTORY_ID FROM GCSD9.DEAL_PRICING_HISTORY "Extent22" WHERE "Extent22".DEAL_HISTORY_ID IS NOT NULL ) "Filter7" WHERE "Extent19".DEAL_HISTORY_ID = "Filter7".DEAL_HISTORY_ID) AS C1 FROM GCSD9.DEAL_DEAL_CONF "Extent19" LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent20" ON "Extent19".DEAL_CONFIRMATION_ID = "Extent20".DEAL_CONFIRM_ID INNER JOIN GCSD9.DEAL_CONFIRMATION "Extent21" ON "Extent20".DEAL_CONFIRM_ID = "Extent21".DEAL_CONFIRM_ID WHERE "Extent21".DEAL_CONFIRM_ID IS NOT NULL ) "Project4" LEFT OUTER JOIN GCSD9.DEAL_HISTORY "Extent23" ON "Project4".DEAL_HISTORY_ID = "Extent23".DEAL_HISTORY_ID LEFT OUTER JOIN GCSD9.DEAL_HISTORY "Extent24" ON "Project4".DEAL_HISTORY_ID = "Extent24".DEAL_HISTORY_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent25" ON "Project4".DEAL_CONFIRM_ID = "Extent25".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent26" ON "Project4".DEAL_CONFIRM_ID = "Extent26".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_STATUS "Extent27" ON "Extent26".CONF_STATUS_ID = "Extent27".DEAL_CONF_STATUS_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent28" ON "Project4".DEAL_CONFIRM_ID = "Extent28".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_SEND_STATUS "Extent29" ON "Extent28".CONF_SEND_STATUS_ID = "Extent29".DEAL_CONF_SEND_STATUS_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent30" ON "Project4".DEAL_CONFIRM_ID = "Extent30".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_RECEIVE_METHOD "Extent31" ON "Extent30".CONF_RECEIVE_METHOD_ID = "Extent31".DEAL_CONF_RECEIVE_METHOD_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent32" ON "Project4".DEAL_CONFIRM_ID = "Extent32".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.CONTACT_METHOD "Extent33" ON "Extent32".CONF_SEND_METHOD_ID = "Extent33".CONTACT_METHOD_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent34" ON "Project4".DEAL_CONFIRM_ID = "Extent34".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent35" ON "Project4".DEAL_CONFIRM_ID = "Extent35".DEAL_CONFIRM_ID INNER JOIN GCSD9.DEAL_CONF_HISTORY "Extent36" ON "Project4".DEAL_CONFIRM_ID = "Extent36".DEAL_CONF_ID WHERE "Project4".C1 > 0) "UnionAll1" UNION ALL SELECT 3 AS C1, "Project7".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Project7".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID, "Project7".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID1, "Project7".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID2, "Extent41".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, "Extent41".DATE_ENTERED AS DATE_ENTERED, "Extent41".DATE_OF_DEAL AS DATE_OF_DEAL, "Extent41".FLOW_DATE_START AS FLOW_DATE_START, "Extent41".FLOW_DATE_END AS FLOW_DATE_END, "Extent41".DEAL_NUMBER AS DEAL_NUMBER, "Extent41".BUS_ASSOCIATE_ID AS BUS_ASSOCIATE_ID, "Extent41".SUPPLY_CONTACT_ID AS SUPPLY_CONTACT_ID, "Extent41".CONTACT_OTHER AS CONTACT_OTHER, "Extent41".MO_CONTACT_ID AS MO_CONTACT_ID, "Extent41".PIPELINE_ID AS PIPELINE_ID, "Extent41".ZONE_ID AS ZONE_ID, "Extent41".LOCATION_ID AS LOCATION_ID, "Extent41".TRANSFER_POINT_ID AS TRANSFER_POINT_ID, "Extent41".DEAL_TYPE_ID AS DEAL_TYPE_ID, "Extent41".SUPPLY_CONTRACT_ID AS SUPPLY_CONTRACT_ID, "Extent41".GMS_BROKER_ACCT_NBR AS GMS_BROKER_ACCT_NBR, "Extent41".RATE_PLAN_ID AS RATE_PLAN_ID, "Extent41".PROMOTED AS PROMOTED, "Extent41".DELIVERED AS DELIVERED, "Extent41".CANCELED AS CANCELED, "Extent41".DEAL_TERMS_CHANGED AS DEAL_TERMS_CHANGED, "Extent41".DEAL_COMMENT AS DEAL_COMMENT, "Extent41".LOCKED_BY AS LOCKED_BY, "Extent41".ENTERED_BY AS ENTERED_BY, "Extent41".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent41".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent41".WHAT_CHANGED AS WHAT_CHANGED, "Extent41".NEED_ACCOUNT AS NEED_ACCOUNT, "Extent41".RESUBMITTED AS RESUBMITTED, "Extent41".STORAGE_TRANSFER AS STORAGE_TRANSFER, "Extent41".MO_CONTACT_OTHER AS MO_CONTACT_OTHER, "Extent41".SPECIAL_CONDITIONS AS SPECIAL_CONDITIONS, "Extent41".ATTACHED_SHEET AS ATTACHED_SHEET, "Extent41".HAS_ATTACHED_SHEET AS HAS_ATTACHED_SHEET, "Extent41".HISTORY_TMS AS HISTORY_TMS, "Extent41".HISTORY_USER AS HISTORY_USER, "Extent41".ROW_VERSION AS ROW_VERSION, "Extent41".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID1, "Extent41".BUS_ASSOCIATE_CD AS BUS_ASSOCIATE_CD, "Extent41".SUPPLY_CONTACT_NAME AS SUPPLY_CONTACT_NAME, "Extent41".MO_CONTACT_NAME AS MO_CONTACT_NAME, "Extent41".PIPELINE_CD AS PIPELINE_CD, "Extent41".ZONE_CD AS ZONE_CD, "Extent41".LOCATION_CD AS LOCATION_CD, "Extent41".TRANSFER_POINT_CD AS TRANSFER_POINT_CD, "Extent41".DEAL_TYPE AS DEAL_TYPE, "Extent41".SUPPLY_CONTRACT_NBR AS SUPPLY_CONTRACT_NBR, "Extent41".DEAL552CODE_ID AS DEAL552CODE_ID, "Extent42".DEAL_HISTORY_ID AS DEAL_HISTORY_ID1, "Extent43".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID3, "Extent44".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID4, "Extent44".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID5, "Extent46".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID6, "Extent46".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID7, "Extent48".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID8, "Extent48".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID9, "Extent50".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID10, "Extent50".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID11, "Extent52".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID12, "Extent52".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID13, "Extent53".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID14, 1 AS C2, 1 AS C3, "Extent42".DEAL_ID AS DEAL_ID, 1 AS C4, "Extent43".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID15, "Extent43".ACTION_DATE AS ACTION_DATE, "Extent43".CONF_SENT_DATE AS CONF_SENT_DATE, "Extent43".CONF_SEND_METHOD_OTHER_DESC AS CONF_SEND_METHOD_OTHER_DESC, "Extent43".CONF_RECEIVE_METHOD_OTHER_DESC AS CONF_RECEIVE_METHOD_OTHER_DESC, "Extent43".CONF_RECEIVE_DATE AS CONF_RECEIVE_DATE, "Extent43".CONF_COMMENT AS CONF_COMMENT, "Extent43".ENTERED_BY AS ENTERED_BY1, "Extent43".LAST_UPDATE_USER AS LAST_UPDATE_USER1, "Extent43".LAST_UPDATE_TMS AS LAST_UPDATE_TMS1, "Extent43".CONFIRMATION_PDF AS CONFIRMATION_PDF, "Extent43".ROW_VERSION AS ROW_VERSION1, "Extent45".DEAL_CONF_STATUS_ID AS DEAL_CONF_STATUS_ID, "Extent45".DEAL_CONF_STATUS AS DEAL_CONF_STATUS, "Extent45".LAST_UPDATE_USER AS LAST_UPDATE_USER2, "Extent45".LAST_UPDATE_TMS AS LAST_UPDATE_TMS2, "Extent47".DEAL_CONF_SEND_STATUS_ID AS DEAL_CONF_SEND_STATUS_ID, "Extent47".DEAL_CONF_SEND_STATUS AS DEAL_CONF_SEND_STATUS, "Extent47".LAST_UPDATE_USER AS LAST_UPDATE_USER3, "Extent47".LAST_UPDATE_TMS AS LAST_UPDATE_TMS3, "Extent49".DEAL_CONF_RECEIVE_METHOD_ID AS DEAL_CONF_RECEIVE_METHOD_ID, "Extent49".DEAL_CONF_RECEIVE_METHOD AS DEAL_CONF_RECEIVE_METHOD, "Extent49".LAST_UPDATE_USER AS LAST_UPDATE_USER4, "Extent49".LAST_UPDATE_TMS AS LAST_UPDATE_TMS4, "Extent51".CONTACT_METHOD_ID AS CONTACT_METHOD_ID, "Extent51".METHOD AS METHOD, "Extent51".LAST_UPDATE_USER AS LAST_UPDATE_USER5, "Extent51".LAST_UPDATE_TMS AS LAST_UPDATE_TMS5, "Extent52".DEAL_ID AS DEAL_ID1, "Extent53".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID16, "Project7".DEAL_ID AS DEAL_ID2, FLOOR(CAST(NULL AS NUMBER)) AS C5, FLOOR(CAST(NULL AS NUMBER)) AS C6, FLOOR(CAST(NULL AS NUMBER)) AS C7, FLOOR(CAST(NULL AS NUMBER)) AS C8, FLOOR(CAST(NULL AS NUMBER)) AS C9, FLOOR(CAST(NULL AS NUMBER)) AS C10, FLOOR(CAST(NULL AS NUMBER)) AS C11, CAST(NULL AS NUMBER) AS C12, FLOOR(CAST(NULL AS NUMBER)) AS C13, TO_CHAR(NULL) AS C14, TO_CHAR(NULL) AS C15, TO_CHAR(NULL) AS C16, FLOOR(CAST(NULL AS NUMBER)) AS C17, TO_CHAR(NULL) AS C18, CAST(NULL AS NUMBER) AS C19, TO_CHAR(NULL) AS C20, TO_CHAR(NULL) AS C21, CAST(NULL AS TIMESTAMP(9)) AS C22, TO_CHAR(NULL) AS C23, CAST(NULL AS TIMESTAMP(9)) AS C24, TO_CHAR(NULL) AS C25, FLOOR(CAST(NULL AS NUMBER)) AS C26, TO_CHAR(NULL) AS C27, TO_CHAR(NULL) AS C28, TO_CHAR(NULL) AS C29, TO_CHAR(NULL) AS C30, TO_CHAR(NULL) AS C31, FLOOR(CAST(NULL AS NUMBER)) AS C32, FLOOR(CAST(NULL AS NUMBER)) AS C33, FLOOR(CAST(NULL AS NUMBER)) AS C34, FLOOR(CAST(NULL AS NUMBER)) AS C35, CAST(NULL AS TIMESTAMP(9)) AS C36, TO_CHAR(NULL) AS C37, TO_CHAR(NULL) AS C38, CAST(NULL AS TIMESTAMP(9)) AS C39, TO_CHAR(NULL) AS C40, TO_CHAR(NULL) AS C41, TO_CHAR(NULL) AS C42, TO_CHAR(NULL) AS C43, CAST(NULL AS TIMESTAMP(9)) AS C44, TO_CHAR(NULL) AS C45, TO_CHAR(NULL) AS C46, TO_CHAR(NULL) AS C47, CAST(NULL AS TIMESTAMP(9)) AS C48, TO_CHAR(NULL) AS C49, FLOOR(CAST(NULL AS NUMBER)) AS C50, FLOOR(CAST(NULL AS NUMBER)) AS C51, "Join49".DEAL_CONF_CONTACTLIST_ID1 AS DEAL_CONF_CONTACTLIST_ID, "Join49".REMOVED1 AS REMOVED, "Join49".CONF_SEND_METHOD_DESC1 AS CONF_SEND_METHOD_DESC, "Join49".CONF_SENT_DATE1 AS CONF_SENT_DATE1, "Join49".INCLUDE_BATCH1 AS INCLUDE_BATCH, "Join49".ENTERED_BY1 AS ENTERED_BY2, "Join49".LAST_UPDATE_USER1 AS LAST_UPDATE_USER6, "Join49".LAST_UPDATE_TMS1 AS LAST_UPDATE_TMS6, "Join49".ROW_VERSION1 AS ROW_VERSION2, "Join49".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID17, "Join49".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID18, "Join49".CONTACT_ID1 AS CONTACT_ID, 1 AS C52, 1 AS C53, "Join49".CONTACT_ID1 AS CONTACT_ID1, "Join49".LN_ID AS LN_ID, "Join49".STATUS AS STATUS, "Join49".FIRST_NAME AS FIRST_NAME, "Join49".LAST_NAME AS LAST_NAME, "Join49".NAME AS NAME, "Join49".TITLE AS TITLE, "Join49".COMPANY AS COMPANY, "Join49".WORK_PHONE AS WORK_PHONE, "Join49".CELL_PHONE AS CELL_PHONE, "Join49".FAX AS FAX, "Join49".EMAIL AS EMAIL, "Join49".IM AS IM, "Join49".STREET AS STREET, "Join49".CITY AS CITY, "Join49".STATE AS STATE, "Join49".ZIP AS ZIP, "Join49".SEND_CONFIRM AS SEND_CONFIRM, "Join49".AUTO_SEND AS AUTO_SEND, "Join49".LN_FLAG AS LN_FLAG, "Join49"."COMMENT" AS "COMMENT", "Join49".LAST_UPDATE_USER2 AS LAST_UPDATE_USER7, "Join49".LAST_UPDATE_TMS2 AS LAST_UPDATE_TMS7, "Join49".ROW_VERSION2 AS ROW_VERSION3, "Join49".CONTACT_DEPT_ID AS CONTACT_DEPT_ID, "Join49".DEAL_CONFIRM_ID1 AS DEAL_CONFIRM_ID19, CASE WHEN ("Join49".DEAL_CONF_CONTACT_HISTORY_ID IS NULL) THEN FLOOR(CAST(NULL AS NUMBER)) ELSE 1 END AS C54, CASE WHEN ("Join49".DEAL_CONF_CONTACT_HISTORY_ID IS NULL) THEN FLOOR(CAST(NULL AS NUMBER)) ELSE 1 END AS C55, "Join49".DEAL_CONF_CONTACT_HISTORY_ID AS DEAL_CONF_CONTACT_HISTORY_ID, "Join49".DEAL_CONFIRM_ID2 AS DEAL_CONFIRM_ID20, "Join49".CONTACT_ID2 AS CONTACT_ID2, "Join49".REMOVED2 AS REMOVED1, "Join49".CONF_SEND_METHOD_DESC2 AS CONF_SEND_METHOD_DESC1, "Join49".CONF_SENT_DATE2 AS CONF_SENT_DATE2, "Join49".INCLUDE_BATCH2 AS INCLUDE_BATCH1, "Join49".ENTERED_BY2 AS ENTERED_BY3, "Join49".LAST_UPDATE_USER3 AS LAST_UPDATE_USER8, "Join49".LAST_UPDATE_TMS3 AS LAST_UPDATE_TMS8, "Join49".ROW_VERSION3 AS ROW_VERSION4, "Join49".DEAL_CONF_CONTACTLIST_ID2 AS DEAL_CONF_CONTACTLIST_ID1 FROM (SELECT "Extent37".DEAL_ID AS DEAL_ID, "Extent37".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent37".DEAL_HISTORY_ID AS DEAL_HISTORY_ID, "Extent38".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID, "Extent39".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID1, (SELECT Count(1) AS A1 FROM ( SELECT "Extent40".DEAL_PRICING_HISTORY_ID AS DEAL_PRICING_HISTORY_ID, "Extent40".DEAL_PRICING_ID AS DEAL_PRICING_ID, "Extent40".DEAL_ID AS DEAL_ID, "Extent40".VOLUME_MIN AS VOLUME_MIN, "Extent40".VOLUME_MAX AS VOLUME_MAX, "Extent40".VOLUME_TYPE_ID AS VOLUME_TYPE_ID, "Extent40".PRICE AS PRICE, "Extent40".DEAL_PRICE_TYPE_ID AS DEAL_PRICE_TYPE_ID, "Extent40".PRICE_DESCRIPTION AS PRICE_DESCRIPTION, "Extent40".PRICE_INDEX_PIPELINE_ID AS PRICE_INDEX_PIPELINE_ID, "Extent40".PRICE_INDEX_LOCATION_ID AS PRICE_INDEX_LOCATION_ID, "Extent40".PRICE_INDEX_PRICE_TYPE_ID AS PRICE_INDEX_PRICE_TYPE_ID, "Extent40".PRICE_INDEX_HML_FLAG AS PRICE_INDEX_HML_FLAG, "Extent40".PRICE_INDEX_ADDER AS PRICE_INDEX_ADDER, "Extent40".ENTERED_BY AS ENTERED_BY, "Extent40".LAST_UPDATE_USER AS LAST_UPDATE_USER, "Extent40".LAST_UPDATE_TMS AS LAST_UPDATE_TMS, "Extent40".WHAT_CHANGED AS WHAT_CHANGED, "Extent40".HISTORY_TMS AS HISTORY_TMS, "Extent40".HISTORY_USER AS HISTORY_USER, "Extent40".DEAL_CONFIRMATION_ID AS DEAL_CONFIRMATION_ID, "Extent40".DEAL_PRICE_TYPE AS DEAL_PRICE_TYPE, "Extent40".PIPELINE_CD AS PIPELINE_CD, "Extent40".LOCATION_CD AS LOCATION_CD, "Extent40".INDEX_PRICE_TYPE AS INDEX_PRICE_TYPE, "Extent40".VOLUME_TYPE AS VOLUME_TYPE, "Extent40".ROW_VERSION AS ROW_VERSION, "Extent40".DEAL_HISTORY_ID AS DEAL_HISTORY_ID FROM GCSD9.DEAL_PRICING_HISTORY "Extent40" WHERE "Extent40".DEAL_HISTORY_ID IS NOT NULL ) "Filter11" WHERE "Extent37".DEAL_HISTORY_ID = "Filter11".DEAL_HISTORY_ID) AS C1 FROM GCSD9.DEAL_DEAL_CONF "Extent37" LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent38" ON "Extent37".DEAL_CONFIRMATION_ID = "Extent38".DEAL_CONFIRM_ID INNER JOIN GCSD9.DEAL_CONFIRMATION "Extent39" ON "Extent38".DEAL_CONFIRM_ID = "Extent39".DEAL_CONFIRM_ID WHERE "Extent39".DEAL_CONFIRM_ID IS NOT NULL ) "Project7" LEFT OUTER JOIN GCSD9.DEAL_HISTORY "Extent41" ON "Project7".DEAL_HISTORY_ID = "Extent41".DEAL_HISTORY_ID LEFT OUTER JOIN GCSD9.DEAL_HISTORY "Extent42" ON "Project7".DEAL_HISTORY_ID = "Extent42".DEAL_HISTORY_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent43" ON "Project7".DEAL_CONFIRM_ID = "Extent43".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent44" ON "Project7".DEAL_CONFIRM_ID = "Extent44".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_STATUS "Extent45" ON "Extent44".CONF_STATUS_ID = "Extent45".DEAL_CONF_STATUS_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent46" ON "Project7".DEAL_CONFIRM_ID = "Extent46".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_SEND_STATUS "Extent47" ON "Extent46".CONF_SEND_STATUS_ID = "Extent47".DEAL_CONF_SEND_STATUS_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent48" ON "Project7".DEAL_CONFIRM_ID = "Extent48".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_RECEIVE_METHOD "Extent49" ON "Extent48".CONF_RECEIVE_METHOD_ID = "Extent49".DEAL_CONF_RECEIVE_METHOD_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent50" ON "Project7".DEAL_CONFIRM_ID = "Extent50".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.CONTACT_METHOD "Extent51" ON "Extent50".CONF_SEND_METHOD_ID = "Extent51".CONTACT_METHOD_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent52" ON "Project7".DEAL_CONFIRM_ID = "Extent52".DEAL_CONFIRM_ID LEFT OUTER JOIN GCSD9.DEAL_CONFIRMATION "Extent53" ON "Project7".DEAL_CONFIRM_ID = "Extent53".DEAL_CONFIRM_ID INNER JOIN (SELECT "Extent54".DEAL_CONF_CONTACTLIST_ID AS DEAL_CONF_CONTACTLIST_ID1, "Extent54".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID1, "Extent54".CONTACT_ID AS CONTACT_ID3, "Extent54".REMOVED AS REMOVED1, "Extent54".CONF_SEND_METHOD_DESC AS CONF_SEND_METHOD_DESC1, "Extent54".CONF_SENT_DATE AS CONF_SENT_DATE1, "Extent54".INCLUDE_BATCH AS INCLUDE_BATCH1, "Extent54".ENTERED_BY AS ENTERED_BY1, "Extent54".LAST_UPDATE_USER AS LAST_UPDATE_USER1, "Extent54".LAST_UPDATE_TMS AS LAST_UPDATE_TMS1, "Extent54".ROW_VERSION AS ROW_VERSION1, "Extent55".CONTACT_ID AS CONTACT_ID1, "Extent55".CONTACT_DEPT_ID AS CONTACT_DEPT_ID, "Extent55".LN_ID AS LN_ID, "Extent55".STATUS AS STATUS, "Extent55".FIRST_NAME AS FIRST_NAME, "Extent55".LAST_NAME AS LAST_NAME, "Extent55".NAME AS NAME, "Extent55".TITLE AS TITLE, "Extent55".COMPANY AS COMPANY, "Extent55".WORK_PHONE AS WORK_PHONE, "Extent55".CELL_PHONE AS CELL_PHONE, "Extent55".FAX AS FAX, "Extent55".EMAIL AS EMAIL, "Extent55".IM AS IM, "Extent55".STREET AS STREET, "Extent55".CITY AS CITY, "Extent55".STATE AS STATE, "Extent55".ZIP AS ZIP, "Extent55".SEND_CONFIRM AS SEND_CONFIRM, "Extent55".AUTO_SEND AS AUTO_SEND, "Extent55".LN_FLAG AS LN_FLAG, "Extent55"."COMMENT" AS "COMMENT", "Extent55".LAST_UPDATE_USER AS LAST_UPDATE_USER2, "Extent55".LAST_UPDATE_TMS AS LAST_UPDATE_TMS2, "Extent55".ROW_VERSION AS ROW_VERSION2, "Extent56".DEAL_CONF_CONTACT_HISTORY_ID AS DEAL_CONF_CONTACT_HISTORY_ID, "Extent56".DEAL_CONF_CONTACTLIST_ID AS DEAL_CONF_CONTACTLIST_ID2, "Extent56".DEAL_CONFIRM_ID AS DEAL_CONFIRM_ID2, "Extent56".CONTACT_ID AS CONTACT_ID2, "Extent56".REMOVED AS REMOVED2, "Extent56".CONF_SEND_METHOD_DESC AS CONF_SEND_METHOD_DESC2, "Extent56".CONF_SENT_DATE AS CONF_SENT_DATE2, "Extent56".INCLUDE_BATCH AS INCLUDE_BATCH2, "Extent56".ENTERED_BY AS ENTERED_BY2, "Extent56".LAST_UPDATE_USER AS LAST_UPDATE_USER3, "Extent56".LAST_UPDATE_TMS AS LAST_UPDATE_TMS3, "Extent56".ROW_VERSION AS ROW_VERSION3 FROM GCSD9.DEAL_CONF_CONTACTLIST "Extent54" LEFT OUTER JOIN GCSD9.CONTACT "Extent55" ON "Extent54".CONTACT_ID = "Extent55".CONTACT_ID LEFT OUTER JOIN GCSD9.DEAL_CONF_CONTACT_HISTORY "Extent56" ON "Extent54".DEAL_CONF_CONTACTLIST_ID = "Extent56".DEAL_CONF_CONTACTLIST_ID ) "Join49" ON "Project7".DEAL_CONFIRM_ID = "Join49".DEAL_CONFIRM_ID1 WHERE "Project7".C1 > 0) "UnionAll2" ORDER BY "UnionAll2".C2 ASC, "UnionAll2".C3 ASC, "UnionAll2".C4 ASC, "UnionAll2".C5 ASC, "UnionAll2".C6 ASC, "UnionAll2".C55 ASC, "UnionAll2".C56 ASC, "UnionAll2".C57 ASC, "UnionAll2".C58 ASC, "UnionAll2".C59 ASC, "UnionAll2".C60 ASC, "UnionAll2".C61 ASC, "UnionAll2".C62 ASC, "UnionAll2".C63 ASC, "UnionAll2".C64 ASC, "UnionAll2".C65 ASC, "UnionAll2".C66 ASC, "UnionAll2".C67 ASC, "UnionAll2".C72 ASC, "UnionAll2".C84 ASC, "UnionAll2".C88 ASC, "UnionAll2".C92 ASC, "UnionAll2".C96 ASC, "UnionAll2".C101 ASC, "UnionAll2".C1 ASC, "UnionAll2".C150 ASC, "UnionAll2".C159 ASC, "UnionAll2".C160 ASC, "UnionAll2".C161 ASC, "UnionAll2".C164 ASC, "UnionAll2".C190 ASC ERROR MESSAGE: <entry id="32" timestamp="2009-08-04T11:01:41" username="DefaultPrincipal - 0" source="IdeaBlade.EntityModel.Server.EntityServerErrorHandler:CreateMessageFault">Caught exception: System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Devart.Data.Oracle.OracleException: ORA-00972: identifier is too long at Devart.Data.Oracle.ap.b(Int32 A_0) at Devart.Data.Oracle.ar.e(Int32 A_0) at Devart.Data.Oracle.ar.a(Int32 A_0, a5 A_1) at Devart.Data.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Devart.Data.Oracle.Entity.d.a.a(CommandBehavior A_0) at Devart.Data.Oracle.Entity.d.a.b(CommandBehavior A_0) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) --- End of inner exception stack trace --- at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at lambda_method(ExecutionScope , Object[] ) at IdeaBlade.Core.MemberInfoFns.FastStaticMethodInvoke(MethodInfo method, Object[] args) at IdeaBlade.EntityModel.Edm.ObjectQueryProcessor.ProcessQuery(IQueryable objectQuery, EdmType edmType, NavigationSet navSet, Boolean shouldLogSql) at IdeaBlade.EntityModel.Edm.EdmQueryExecutor.ProcessQuery(EntityQuery query, IQueryable edmSource, Expression edmExpr) at IdeaBlade.EntityModel.Edm.EdmQueryExecutor.TranslateAndExecuteQuery(EntityQuery query) at IdeaBlade.EntityModel.Edm.EdmQueryExecutor.ExecuteCore(IEntityQuery query, IDataSourceKey edmKey) at IdeaBlade.EntityModel.Edm.EdmQueryExecutor.Execute(IEntityQuery query, IDataSourceKey edmKey) at IdeaBlade.EntityModel.Server.EntityServer.Fetch(IEntityQuery query, IDataSourceKey dsKey) at IdeaBlade.EntityModel.Server.EntityServer.Fetch(SessionBundle sessionBundle, IEntityQuerySurrogate surrogate) at SyncInvokeFetch(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</entry> </log> |
|
 |
|
|
kimj
IdeaBlade 
Joined: 09-May-2007 Posts: 1391 |
Post Options
Quote Reply
Posted: 04-Aug-2009 at 10:57am |
|
That's one awful looking query!
The SQL is actually generated by the data provider (in this case DevArt) based on the query expression. I'd guess this query, if the SQL were correct, would not perform well. Try turning query inversion off here - this should remove a number of the left outer joins and the "extent" names. The easiest way to turn inversion off is to use a DataSourceOnly query strategy, but check out the QueryStrategy type for other options.
|
|
|
|
|
DanW
Newbie
Joined: 04-Aug-2009 Posts: 6 |
Post Options
Quote Reply
Posted: 04-Aug-2009 at 11:36am |
|
Kim, Thank you for the speedy response, I tried what you suggested, with turning off Query Inversion and playing with the Query Strategy settings, but as far as I can tell, none of that affected the way that the SQL was generated at all. I have posted this to the DevArt forum now as well, apparantly this has been a known issue for about a year now. Hopefully they will address it soon!
Thank you!
-DanW
P.S. Here's the link to the DevArt post:
|
|
|
|
|
DanW
Newbie
Joined: 04-Aug-2009 Posts: 6 |
Post Options
Quote Reply
Posted: 10-Aug-2009 at 5:05am |
|
Just FYI, This has apparantly been an outstanding issue with the dotConnect drivers for over a year now, but they have acknowledged it and stated that they intend to address it in their next release
|
|
|
|
|
Post Reply
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |