|
Участник
Регистрация: 12.10.2004
Адрес: Москва
|
Кстати, при работе с ГАК может проявиться баг ядра при работе с виртуальными компаниями.
Предположим, табличка адресов address является виртуальной, тогда при выполнении запроса
X++: select crosscompany:dataAreaidList addressRelationshipMapping
join partyAddressRelationship
where partyAddressRelationship.PartyId == _partyId && partyAddressRelationship.IsPrimary &&
(partyAddressRelationship.ValidFromDateTime <= _transDateTime) &&
(partyAddressRelationship.ValidToDateTime > _transDateTime) &&
partyAddressRelationship.RecId == addressRelationshipMapping.PartyAddressRelationshipRecId
join address
where address.RecId == addressRelationshipMapping.AddressRecId &&
address.DataAreaId == addressRelationshipMapping.RefCompanyId;тут
\Classes\DirPartyAddress\findPrimaryAddress
мы в результате ничего не получим.
Причина в том, что ядро при использовании директивы crosscompany: изменяет запрос к БД таким образом, что вместо одной записи с кодом виртуальной компании в address.DataAreaId, подзапрс вернет несколько записей, в каждой из которых address.DataAreaId стоит код невиртуаьной компании, входящей в виртуальную.
А к полю addressRelationshipMapping.RefCompanyId по которому идет джоин, такого преобразования не выполняется. в результате запрос работает неправильно.
X++: SELECT A.ADDRESSRECID,A.REFCOMPANYID,A.PARTYADDRESSRELATIONSHIPRECID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.CREATEDDATETIME,A.CREATEDBY,A.CREATEDTRANSACTIONID,A.DATAAREAID,A.RECVERSION,A.RECID,B.PARTYID,B.ISPRIMARY,B.STATUS,B.VALIDTODATETIME,B.VALIDTODATETIMETZID,B.VALIDFROMDATETIME,B.VALIDFROMDATETIMETZID,B.SHARED,B.MODIFIEDDATETIME,B.MODIFIEDBY,B.MODIFIEDTRANSACTIONID,B.CREATEDDATETIME,B.CREATEDBY,B.CREATEDTRANSACTIONID,B.DATAAREAID,B.RECVERSION,B.RECID,C.ADDRTABLEID,C.ADDRRECID,C.LINENUM,C.TYPE,C.NAME,C.ADDRESS,C.PHONE,C.TELEFAX,C.COUNTRYREGIONID,C.ZIPCODE,C.STATE,C.COUNTY,C.TELEX,C.URL,C.PHONELOCAL,C.CELLULARPHONE,C.EMAIL,C.TAXGROUP,C.CITY,C.STREET,C.PAGER,C.SMS,C.LATITUDE,C.LONGITUDE,C.SALESCALENDARID,C.TIMEZONE,C.DLVTERM,C.DLVMODE,C.SHIPCARRIERID,C.SHIPCARRIERBLINDSHIPMENT,C.SHIPCARRIERACCOUNT,C.SHIPCARRIERACCOUNTCODE,C.SHIPCARRIERRESIDENTIAL,C.TOWNID_RU,C.SETTLEMENTID_RU,C.STREETID_RU,C.FLAT_RU,C.BUILDING_RU,C.ESTATE_RU,C.GRD_ACCOUNTNUMOPTIMUMEX,C.GRD_TRANSPORTZONEID,C.GRD_RADIUS,C.GRD_COORDY,C.GRD_COORDX,C.GRD_ADDRESSREGNUM,C.GRD_ISBLOCKED,C.GRD_PARENTADDRESS,C.GRD_SOTYPEID,C.GRD_ADDRDESCRIPTION,C.GRD_ADDRID,C.GRD_KPP,C.GRD_CMCZONEID,C.GRD_SHOPTOTALAREA,C.GRD_DELIVERYTIME,C.GRD_DELIVERYTIMEDESCRIPTION,C.GRD_ABC,C.GRD_SELFSERVICE,C.GRD_FORWARDINGCOST,C.GRD_PICKINGQUEUE,C.GRD_FOR1C,C.GRD_SHOPNUM,C.GRD_SALESOUTLETSIGN,C.GRD_MARKETTYPEID,C.DEL_GRD_MONITORING,C.GRD_CUSTITEMGROUPID,C.GRD_TTCLOSEREASONID,C.GRD_STAMP,C.GRD_MARKETID,C.GRD_MAINADDRESSREGNUM,C.GRD_FAKTADDRESS,C.GRD_GLN,C.GRD_EXTID,C.GRD_DELIVERYDATE4DUEDATE,C.GRD_OPENDATE,C.GRD_COSMZONE,C.GRD_DELIVERYPERIOD,C.GRD_DELIVERYTIMEFROM,C.GRD_DELIVERYTIMETO,C.MODIFIEDDATETIME,C.DEL_MODIFIEDTIME,C.MODIFIEDBY,C.CREATEDDATETIME,C.DEL_CREATEDTIME,C.CREATEDBY,C.DATAAREAID,C.RECVERSION,C.RECID FROM (SELECT VIRT.ID AS DATAAREAID ,A.ADDRESSRECID,A.REFCOMPANYID,A.PARTYADDRESSRELATIONSHIPRECID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.CREATEDDATETIME,A.CREATEDBY,A.CREATEDTRANSACTIONID,A.RECVERSION,A.RECID FROM DIRPARTYADDRESSRELATIONSHI1066 A INNER JOIN VIRTUALDATAAREALIST VIRT ON SUBSTR(NLS_LOWER(A.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(VIRT.VIRTUALDATAAREA),1,4) WHERE (SUBSTR(NLS_LOWER(A.DATAAREAID),1,4) IN (NLS_LOWER(:in1),NLS_LOWER(:in2),NLS_LOWER(:in3),NLS_LOWER(:in4),NLS_LOWER(:in5),NLS_LOWER(:in6),NLS_LOWER(:in7),NLS_LOWER(:in8),NLS_LOWER(:in9),NLS_LOWER(:in10),NLS_LOWER(:in11),NLS_LOWER(:in12),NLS_LOWER(:in13),NLS_LOWER(:in14),NLS_LOWER(:in15),NLS_LOWER(:in16),NLS_LOWER(:in17),NLS_LOWER(:in18),NLS_LOWER(:in19),NLS_LOWER(:in20),NLS_LOWER(:in21),NLS_LOWER(:in22),NLS_LOWER(:in23),NLS_LOWER(:in24),NLS_LOWER(:in25),NLS_LOWER(:in26),NLS_LOWER(:in27),NLS_LOWER(:in28),NLS_LOWER(:in29),NLS_LOWER(:in30),NLS_LOWER(:in31),NLS_LOWER(:in32),NLS_LOWER(:in33),NLS_LOWER(:in34),NLS_LOWER(:in35),NLS_LOWER(:in36),NLS_LOWER(:in37),NLS_LOWER(:in38),NLS_LOWER(:in39),NLS_LOWER(:in40),NLS_LOWER(:in41),NLS_LOWER(:in42),NLS_LOWER(:in43),NLS_LOWER(:in44),NLS_LOWER(:in45),NLS_LOWER(:in46),NLS_LOWER(:in47),NLS_LOWER(:in48),NLS_LOWER(:in49),NLS_LOWER(:in50),NLS_LOWER(:in51),NLS_LOWER(:in52),NLS_LOWER(:in53),NLS_LOWER(:in54),NLS_LOWER(:in55),NLS_LOWER(:in56),NLS_LOWER(:in57),NLS_LOWER(:in58),NLS_LOWER(:in59),NLS_LOWER(:in60),NLS_LOWER(:in61),NLS_LOWER(:in62),NLS_LOWER(:in63),NLS_LOWER(:in64),NLS_LOWER(:in65),NLS_LOWER(:in66),NLS_LOWER(:in67),NLS_LOWER(:in68),NLS_LOWER(:in69),NLS_LOWER(:in70),NLS_LOWER(:in71),NLS_LOWER(:in72),NLS_LOWER(:in73),NLS_LOWER(:in74),NLS_LOWER(:in75),NLS_LOWER(:in76),NLS_LOWER(:in77),NLS_LOWER(:in78),NLS_LOWER(:in79),NLS_LOWER(:in80),NLS_LOWER(:in81),NLS_LOWER(:in82),NLS_LOWER(:in83),NLS_LOWER(:in84),NLS_LOWER(:in85),NLS_LOWER(:in86),NLS_LOWER(:in87),NLS_LOWER(:in88),NLS_LOWER(:in89),NLS_LOWER(:in90),NLS_LOWER(:in91),NLS_LOWER(:in92),NLS_LOWER(:in93),NLS_LOWER(:in94),NLS_LOWER(:in95),NLS_LOWER(:in96),NLS_LOWER(:in97),NLS_LOWER(:in98),NLS_LOWER(:in99),NLS_LOWER(:in100),NLS_LOWER(:in101),NLS_LOWER(:in102),NLS_LOWER(:in103),NLS_LOWER(:in104),NLS_LOWER(:in105),NLS_LOWER(:in106),NLS_LOWER(:in107),NLS_LOWER(:in108),NLS_LOWER(:in109),NLS_LOWER(:in110),NLS_LOWER(:in111),NLS_LOWER(:in112),NLS_LOWER(:in113),NLS_LOWER(:in114),NLS_LOWER(:in115),NLS_LOWER(:in116),NLS_LOWER(:in117),NLS_LOWER(:in118),NLS_LOWER(:in119),NLS_LOWER(:in120),NLS_LOWER(:in121),NLS_LOWER(:in122)) ) UNION ALL SELECT A.DATAAREAID ,A.ADDRESSRECID,A.REFCOMPANYID,A.PARTYADDRESSRELATIONSHIPRECID,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.CREATEDDATETIME,A.CREATEDBY,A.CREATEDTRANSACTIONID,A.RECVERSION,A.RECID FROM DIRPARTYADDRESSRELATIONSHI1066 A INNER JOIN DATAAREA DAT ON (SUBSTR(NLS_LOWER(A.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(DAT.ID),1,4) AND DAT.ISVIRTUAL = 0) WHERE (SUBSTR(NLS_LOWER(A.DATAAREAID),1,4) IN (NLS_LOWER(:in123),NLS_LOWER(:in124),NLS_LOWER(:in125),NLS_LOWER(:in126),NLS_LOWER(:in127),NLS_LOWER(:in128),NLS_LOWER(:in129),NLS_LOWER(:in130),NLS_LOWER(:in131),NLS_LOWER(:in132),NLS_LOWER(:in133),NLS_LOWER(:in134),NLS_LOWER(:in135),NLS_LOWER(:in136),NLS_LOWER(:in137),NLS_LOWER(:in138),NLS_LOWER(:in139),NLS_LOWER(:in140),NLS_LOWER(:in141),NLS_LOWER(:in142),NLS_LOWER(:in143),NLS_LOWER(:in144),NLS_LOWER(:in145),NLS_LOWER(:in146),NLS_LOWER(:in147),NLS_LOWER(:in148),NLS_LOWER(:in149),NLS_LOWER(:in150),NLS_LOWER(:in151),NLS_LOWER(:in152),NLS_LOWER(:in153),NLS_LOWER(:in154),NLS_LOWER(:in155),NLS_LOWER(:in156),NLS_LOWER(:in157),NLS_LOWER(:in158),NLS_LOWER(:in159),NLS_LOWER(:in160),NLS_LOWER(:in161),NLS_LOWER(:in162),NLS_LOWER(:in163),NLS_LOWER(:in164),NLS_LOWER(:in165),NLS_LOWER(:in166),NLS_LOWER(:in167),NLS_LOWER(:in168),NLS_LOWER(:in169),NLS_LOWER(:in170),NLS_LOWER(:in171),NLS_LOWER(:in172),NLS_LOWER(:in173),NLS_LOWER(:in174),NLS_LOWER(:in175),NLS_LOWER(:in176),NLS_LOWER(:in177),NLS_LOWER(:in178),NLS_LOWER(:in179),NLS_LOWER(:in180),NLS_LOWER(:in181),NLS_LOWER(:in182),NLS_LOWER(:in183),NLS_LOWER(:in184),NLS_LOWER(:in185),NLS_LOWER(:in186),NLS_LOWER(:in187),NLS_LOWER(:in188),NLS_LOWER(:in189),NLS_LOWER(:in190),NLS_LOWER(:in191),NLS_LOWER(:in192),NLS_LOWER(:in193),NLS_LOWER(:in194),NLS_LOWER(:in195),NLS_LOWER(:in196),NLS_LOWER(:in197),NLS_LOWER(:in198),NLS_LOWER(:in199),NLS_LOWER(:in200),NLS_LOWER(:in201),NLS_LOWER(:in202),NLS_LOWER(:in203),NLS_LOWER(:in204),NLS_LOWER(:in205),NLS_LOWER(:in206),NLS_LOWER(:in207),NLS_LOWER(:in208),NLS_LOWER(:in209),NLS_LOWER(:in210),NLS_LOWER(:in211),NLS_LOWER(:in212),NLS_LOWER(:in213),NLS_LOWER(:in214),NLS_LOWER(:in215),NLS_LOWER(:in216),NLS_LOWER(:in217),NLS_LOWER(:in218),NLS_LOWER(:in219),NLS_LOWER(:in220),NLS_LOWER(:in221),NLS_LOWER(:in222),NLS_LOWER(:in223),NLS_LOWER(:in224),NLS_LOWER(:in225),NLS_LOWER(:in226),NLS_LOWER(:in227),NLS_LOWER(:in228),NLS_LOWER(:in229),NLS_LOWER(:in230),NLS_LOWER(:in231),NLS_LOWER(:in232),NLS_LOWER(:in233),NLS_LOWER(:in234),NLS_LOWER(:in235),NLS_LOWER(:in236),NLS_LOWER(:in237),NLS_LOWER(:in238),NLS_LOWER(:in239),NLS_LOWER(:in240),NLS_LOWER(:in241),NLS_LOWER(:in242),NLS_LOWER(:in243),NLS_LOWER(:in244)) )) A,(SELECT VIRT.ID AS DATAAREAID ,B.PARTYID,B.ISPRIMARY,B.STATUS,B.VALIDTODATETIME,B.VALIDFROMDATETIME,B.SHARED,B.MODIFIEDDATETIME,B.MODIFIEDBY,B.MODIFIEDTRANSACTIONID,B.CREATEDDATETIME,B.CREATEDBY,B.CREATEDTRANSACTIONID,B.RECVERSION,B.RECID,B.VALIDTODATETIMETZID,B.VALIDFROMDATETIMETZID FROM DIRPARTYADDRESSRELATIONSHIP B INNER JOIN VIRTUALDATAAREALIST VIRT ON SUBSTR(NLS_LOWER(B.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(VIRT.VIRTUALDATAAREA),1,4) WHERE (SUBSTR(NLS_LOWER(B.DATAAREAID),1,4) IN (NLS_LOWER(:in245),NLS_LOWER(:in246),NLS_LOWER(:in247),NLS_LOWER(:in248),NLS_LOWER(:in249),NLS_LOWER(:in250),NLS_LOWER(:in251),NLS_LOWER(:in252),NLS_LOWER(:in253),NLS_LOWER(:in254),NLS_LOWER(:in255),NLS_LOWER(:in256),NLS_LOWER(:in257),NLS_LOWER(:in258),NLS_LOWER(:in259),NLS_LOWER(:in260),NLS_LOWER(:in261),NLS_LOWER(:in262),NLS_LOWER(:in263),NLS_LOWER(:in264),NLS_LOWER(:in265),NLS_LOWER(:in266),NLS_LOWER(:in267),NLS_LOWER(:in268),NLS_LOWER(:in269),NLS_LOWER(:in270),NLS_LOWER(:in271),NLS_LOWER(:in272),NLS_LOWER(:in273),NLS_LOWER(:in274),NLS_LOWER(:in275),NLS_LOWER(:in276),NLS_LOWER(:in277),NLS_LOWER(:in278),NLS_LOWER(:in279),NLS_LOWER(:in280),NLS_LOWER(:in281),NLS_LOWER(:in282),NLS_LOWER(:in283),NLS_LOWER(:in284),NLS_LOWER(:in285),NLS_LOWER(:in286),NLS_LOWER(:in287),NLS_LOWER(:in288),NLS_LOWER(:in289),NLS_LOWER(:in290),NLS_LOWER(:in291),NLS_LOWER(:in292),NLS_LOWER(:in293),NLS_LOWER(:in294),NLS_LOWER(:in295),NLS_LOWER(:in296),NLS_LOWER(:in297),NLS_LOWER(:in298),NLS_LOWER(:in299),NLS_LOWER(:in300),NLS_LOWER(:in301),NLS_LOWER(:in302),NLS_LOWER(:in303),NLS_LOWER(:in304),NLS_LOWER(:in305),NLS_LOWER(:in306),NLS_LOWER(:in307),NLS_LOWER(:in308),NLS_LOWER(:in309),NLS_LOWER(:in310),NLS_LOWER(:in311),NLS_LOWER(:in312),NLS_LOWER(:in313),NLS_LOWER(:in314),NLS_LOWER(:in315),NLS_LOWER(:in316),NLS_LOWER(:in317),NLS_LOWER(:in318),NLS_LOWER(:in319),NLS_LOWER(:in320),NLS_LOWER(:in321),NLS_LOWER(:in322),NLS_LOWER(:in323),NLS_LOWER(:in324),NLS_LOWER(:in325),NLS_LOWER(:in326),NLS_LOWER(:in327),NLS_LOWER(:in328),NLS_LOWER(:in329),NLS_LOWER(:in330),NLS_LOWER(:in331),NLS_LOWER(:in332),NLS_LOWER(:in333),NLS_LOWER(:in334),NLS_LOWER(:in335),NLS_LOWER(:in336),NLS_LOWER(:in337),NLS_LOWER(:in338),NLS_LOWER(:in339),NLS_LOWER(:in340),NLS_LOWER(:in341),NLS_LOWER(:in342),NLS_LOWER(:in343),NLS_LOWER(:in344),NLS_LOWER(:in345),NLS_LOWER(:in346),NLS_LOWER(:in347),NLS_LOWER(:in348),NLS_LOWER(:in349),NLS_LOWER(:in350),NLS_LOWER(:in351),NLS_LOWER(:in352),NLS_LOWER(:in353),NLS_LOWER(:in354),NLS_LOWER(:in355),NLS_LOWER(:in356),NLS_LOWER(:in357),NLS_LOWER(:in358),NLS_LOWER(:in359),NLS_LOWER(:in360),NLS_LOWER(:in361),NLS_LOWER(:in362),NLS_LOWER(:in363),NLS_LOWER(:in364),NLS_LOWER(:in365),NLS_LOWER(:in366)) ) UNION ALL SELECT B.DATAAREAID ,B.PARTYID,B.ISPRIMARY,B.STATUS,B.VALIDTODATETIME,B.VALIDFROMDATETIME,B.SHARED,B.MODIFIEDDATETIME,B.MODIFIEDBY,B.MODIFIEDTRANSACTIONID,B.CREATEDDATETIME,B.CREATEDBY,B.CREATEDTRANSACTIONID,B.RECVERSION,B.RECID,B.VALIDTODATETIMETZID,B.VALIDFROMDATETIMETZID FROM DIRPARTYADDRESSRELATIONSHIP B INNER JOIN DATAAREA DAT ON (SUBSTR(NLS_LOWER(B.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(DAT.ID),1,4) AND DAT.ISVIRTUAL = 0) WHERE (SUBSTR(NLS_LOWER(B.DATAAREAID),1,4) IN (NLS_LOWER(:in367),NLS_LOWER(:in368),NLS_LOWER(:in369),NLS_LOWER(:in370),NLS_LOWER(:in371),NLS_LOWER(:in372),NLS_LOWER(:in373),NLS_LOWER(:in374),NLS_LOWER(:in375),NLS_LOWER(:in376),NLS_LOWER(:in377),NLS_LOWER(:in378),NLS_LOWER(:in379),NLS_LOWER(:in380),NLS_LOWER(:in381),NLS_LOWER(:in382),NLS_LOWER(:in383),NLS_LOWER(:in384),NLS_LOWER(:in385),NLS_LOWER(:in386),NLS_LOWER(:in387),NLS_LOWER(:in388),NLS_LOWER(:in389),NLS_LOWER(:in390),NLS_LOWER(:in391),NLS_LOWER(:in392),NLS_LOWER(:in393),NLS_LOWER(:in394),NLS_LOWER(:in395),NLS_LOWER(:in396),NLS_LOWER(:in397),NLS_LOWER(:in398),NLS_LOWER(:in399),NLS_LOWER(:in400),NLS_LOWER(:in401),NLS_LOWER(:in402),NLS_LOWER(:in403),NLS_LOWER(:in404),NLS_LOWER(:in405),NLS_LOWER(:in406),NLS_LOWER(:in407),NLS_LOWER(:in408),NLS_LOWER(:in409),NLS_LOWER(:in410),NLS_LOWER(:in411),NLS_LOWER(:in412),NLS_LOWER(:in413),NLS_LOWER(:in414),NLS_LOWER(:in415),NLS_LOWER(:in416),NLS_LOWER(:in417),NLS_LOWER(:in418),NLS_LOWER(:in419),NLS_LOWER(:in420),NLS_LOWER(:in421),NLS_LOWER(:in422),NLS_LOWER(:in423),NLS_LOWER(:in424),NLS_LOWER(:in425),NLS_LOWER(:in426),NLS_LOWER(:in427),NLS_LOWER(:in428),NLS_LOWER(:in429),NLS_LOWER(:in430),NLS_LOWER(:in431),NLS_LOWER(:in432),NLS_LOWER(:in433),NLS_LOWER(:in434),NLS_LOWER(:in435),NLS_LOWER(:in436),NLS_LOWER(:in437),NLS_LOWER(:in438),NLS_LOWER(:in439),NLS_LOWER(:in440),NLS_LOWER(:in441),NLS_LOWER(:in442),NLS_LOWER(:in443),NLS_LOWER(:in444),NLS_LOWER(:in445),NLS_LOWER(:in446),NLS_LOWER(:in447),NLS_LOWER(:in448),NLS_LOWER(:in449),NLS_LOWER(:in450),NLS_LOWER(:in451),NLS_LOWER(:in452),NLS_LOWER(:in453),NLS_LOWER(:in454),NLS_LOWER(:in455),NLS_LOWER(:in456),NLS_LOWER(:in457),NLS_LOWER(:in458),NLS_LOWER(:in459),NLS_LOWER(:in460),NLS_LOWER(:in461),NLS_LOWER(:in462),NLS_LOWER(:in463),NLS_LOWER(:in464),NLS_LOWER(:in465),NLS_LOWER(:in466),NLS_LOWER(:in467),NLS_LOWER(:in468),NLS_LOWER(:in469),NLS_LOWER(:in470),NLS_LOWER(:in471),NLS_LOWER(:in472),NLS_LOWER(:in473),NLS_LOWER(:in474),NLS_LOWER(:in475),NLS_LOWER(:in476),NLS_LOWER(:in477),NLS_LOWER(:in478),NLS_LOWER(:in479),NLS_LOWER(:in480),NLS_LOWER(:in481),NLS_LOWER(:in482),NLS_LOWER(:in483),NLS_LOWER(:in484),NLS_LOWER(:in485),NLS_LOWER(:in486),NLS_LOWER(:in487),NLS_LOWER(:in488)) )) B,(SELECT VIRT.ID AS DATAAREAID ,C.ADDRTABLEID,C.ADDRRECID,C.LINENUM,C.TYPE,C.NAME,C.ADDRESS,C.PHONE,C.TELEFAX,C.COUNTRYREGIONID,C.ZIPCODE,C.STATE,C.COUNTY,C.TELEX,C.URL,C.PHONELOCAL,C.CELLULARPHONE,C.EMAIL,C.TAXGROUP,C.CITY,C.STREET,C.PAGER,C.SMS,C.LATITUDE,C.LONGITUDE,C.SALESCALENDARID,C.TIMEZONE,C.DLVTERM,C.DLVMODE,C.SHIPCARRIERID,C.SHIPCARRIERBLINDSHIPMENT,C.SHIPCARRIERACCOUNT,C.SHIPCARRIERACCOUNTCODE,C.SHIPCARRIERRESIDENTIAL,C.TOWNID_RU,C.SETTLEMENTID_RU,C.STREETID_RU,C.FLAT_RU,C.BUILDING_RU,C.ESTATE_RU,C.GRD_ACCOUNTNUMOPTIMUMEX,C.GRD_TRANSPORTZONEID,C.GRD_RADIUS,C.GRD_COORDY,C.GRD_COORDX,C.GRD_ADDRESSREGNUM,C.GRD_ISBLOCKED,C.GRD_PARENTADDRESS,C.GRD_SOTYPEID,C.GRD_ADDRDESCRIPTION,C.GRD_ADDRID,C.GRD_KPP,C.GRD_CMCZONEID,C.GRD_SHOPTOTALAREA,C.GRD_DELIVERYTIME,C.GRD_DELIVERYTIMEDESCRIPTION,C.GRD_ABC,C.GRD_SELFSERVICE,C.GRD_FORWARDINGCOST,C.GRD_PICKINGQUEUE,C.GRD_FOR1C,C.GRD_SHOPNUM,C.GRD_SALESOUTLETSIGN,C.GRD_MARKETTYPEID,C.DEL_GRD_MONITORING,C.GRD_CUSTITEMGROUPID,C.GRD_TTCLOSEREASONID,C.GRD_STAMP,C.GRD_MARKETID,C.GRD_MAINADDRESSREGNUM,C.GRD_FAKTADDRESS,C.GRD_GLN,C.GRD_EXTID,C.GRD_DELIVERYDATE4DUEDATE,C.GRD_OPENDATE,C.GRD_COSMZONE,C.GRD_DELIVERYPERIOD,C.GRD_DELIVERYTIMEFROM,C.GRD_DELIVERYTIMETO,C.MODIFIEDDATETIME,C.DEL_MODIFIEDTIME,C.MODIFIEDBY,C.CREATEDDATETIME,C.DEL_CREATEDTIME,C.CREATEDBY,C.RECVERSION,C.RECID FROM ADDRESS C INNER JOIN VIRTUALDATAAREALIST VIRT ON SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(VIRT.VIRTUALDATAAREA),1,4) WHERE (SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) IN (NLS_LOWER(:in489),NLS_LOWER(:in490),NLS_LOWER(:in491),NLS_LOWER(:in492),NLS_LOWER(:in493),NLS_LOWER(:in494),NLS_LOWER(:in495),NLS_LOWER(:in496),NLS_LOWER(:in497),NLS_LOWER(:in498),NLS_LOWER(:in499),NLS_LOWER(:in500),NLS_LOWER(:in501),NLS_LOWER(:in502),NLS_LOWER(:in503),NLS_LOWER(:in504),NLS_LOWER(:in505),NLS_LOWER(:in506),NLS_LOWER(:in507),NLS_LOWER(:in508),NLS_LOWER(:in509),NLS_LOWER(:in510),NLS_LOWER(:in511),NLS_LOWER(:in512),NLS_LOWER(:in513),NLS_LOWER(:in514),NLS_LOWER(:in515),NLS_LOWER(:in516),NLS_LOWER(:in517),NLS_LOWER(:in518),NLS_LOWER(:in519),NLS_LOWER(:in520),NLS_LOWER(:in521),NLS_LOWER(:in522),NLS_LOWER(:in523),NLS_LOWER(:in524),NLS_LOWER(:in525),NLS_LOWER(:in526),NLS_LOWER(:in527),NLS_LOWER(:in528),NLS_LOWER(:in529),NLS_LOWER(:in530),NLS_LOWER(:in531),NLS_LOWER(:in532),NLS_LOWER(:in533),NLS_LOWER(:in534),NLS_LOWER(:in535),NLS_LOWER(:in536),NLS_LOWER(:in537),NLS_LOWER(:in538),NLS_LOWER(:in539),NLS_LOWER(:in540),NLS_LOWER(:in541),NLS_LOWER(:in542),NLS_LOWER(:in543),NLS_LOWER(:in544),NLS_LOWER(:in545),NLS_LOWER(:in546),NLS_LOWER(:in547),NLS_LOWER(:in548),NLS_LOWER(:in549),NLS_LOWER(:in550),NLS_LOWER(:in551),NLS_LOWER(:in552),NLS_LOWER(:in553),NLS_LOWER(:in554),NLS_LOWER(:in555),NLS_LOWER(:in556),NLS_LOWER(:in557),NLS_LOWER(:in558),NLS_LOWER(:in559),NLS_LOWER(:in560),NLS_LOWER(:in561),NLS_LOWER(:in562),NLS_LOWER(:in563),NLS_LOWER(:in564),NLS_LOWER(:in565),NLS_LOWER(:in566),NLS_LOWER(:in567),NLS_LOWER(:in568),NLS_LOWER(:in569),NLS_LOWER(:in570),NLS_LOWER(:in571),NLS_LOWER(:in572),NLS_LOWER(:in573),NLS_LOWER(:in574),NLS_LOWER(:in575),NLS_LOWER(:in576),NLS_LOWER(:in577),NLS_LOWER(:in578),NLS_LOWER(:in579),NLS_LOWER(:in580),NLS_LOWER(:in581),NLS_LOWER(:in582),NLS_LOWER(:in583),NLS_LOWER(:in584),NLS_LOWER(:in585),NLS_LOWER(:in586),NLS_LOWER(:in587),NLS_LOWER(:in588),NLS_LOWER(:in589),NLS_LOWER(:in590),NLS_LOWER(:in591),NLS_LOWER(:in592),NLS_LOWER(:in593),NLS_LOWER(:in594),NLS_LOWER(:in595),NLS_LOWER(:in596),NLS_LOWER(:in597),NLS_LOWER(:in598),NLS_LOWER(:in599),NLS_LOWER(:in600),NLS_LOWER(:in601),NLS_LOWER(:in602),NLS_LOWER(:in603),NLS_LOWER(:in604),NLS_LOWER(:in605),NLS_LOWER(:in606),NLS_LOWER(:in607),NLS_LOWER(:in608),NLS_LOWER(:in609),NLS_LOWER(:in610)) ) UNION ALL SELECT C.DATAAREAID ,C.ADDRTABLEID,C.ADDRRECID,C.LINENUM,C.TYPE,C.NAME,C.ADDRESS,C.PHONE,C.TELEFAX,C.COUNTRYREGIONID,C.ZIPCODE,C.STATE,C.COUNTY,C.TELEX,C.URL,C.PHONELOCAL,C.CELLULARPHONE,C.EMAIL,C.TAXGROUP,C.CITY,C.STREET,C.PAGER,C.SMS,C.LATITUDE,C.LONGITUDE,C.SALESCALENDARID,C.TIMEZONE,C.DLVTERM,C.DLVMODE,C.SHIPCARRIERID,C.SHIPCARRIERBLINDSHIPMENT,C.SHIPCARRIERACCOUNT,C.SHIPCARRIERACCOUNTCODE,C.SHIPCARRIERRESIDENTIAL,C.TOWNID_RU,C.SETTLEMENTID_RU,C.STREETID_RU,C.FLAT_RU,C.BUILDING_RU,C.ESTATE_RU,C.GRD_ACCOUNTNUMOPTIMUMEX,C.GRD_TRANSPORTZONEID,C.GRD_RADIUS,C.GRD_COORDY,C.GRD_COORDX,C.GRD_ADDRESSREGNUM,C.GRD_ISBLOCKED,C.GRD_PARENTADDRESS,C.GRD_SOTYPEID,C.GRD_ADDRDESCRIPTION,C.GRD_ADDRID,C.GRD_KPP,C.GRD_CMCZONEID,C.GRD_SHOPTOTALAREA,C.GRD_DELIVERYTIME,C.GRD_DELIVERYTIMEDESCRIPTION,C.GRD_ABC,C.GRD_SELFSERVICE,C.GRD_FORWARDINGCOST,C.GRD_PICKINGQUEUE,C.GRD_FOR1C,C.GRD_SHOPNUM,C.GRD_SALESOUTLETSIGN,C.GRD_MARKETTYPEID,C.DEL_GRD_MONITORING,C.GRD_CUSTITEMGROUPID,C.GRD_TTCLOSEREASONID,C.GRD_STAMP,C.GRD_MARKETID,C.GRD_MAINADDRESSREGNUM,C.GRD_FAKTADDRESS,C.GRD_GLN,C.GRD_EXTID,C.GRD_DELIVERYDATE4DUEDATE,C.GRD_OPENDATE,C.GRD_COSMZONE,C.GRD_DELIVERYPERIOD,C.GRD_DELIVERYTIMEFROM,C.GRD_DELIVERYTIMETO,C.MODIFIEDDATETIME,C.DEL_MODIFIEDTIME,C.MODIFIEDBY,C.CREATEDDATETIME,C.DEL_CREATEDTIME,C.CREATEDBY,C.RECVERSION,C.RECID FROM ADDRESS C INNER JOIN DATAAREA DAT ON (SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(DAT.ID),1,4) AND DAT.ISVIRTUAL = 0) WHERE (SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) IN (NLS_LOWER(:in611),NLS_LOWER(:in612),NLS_LOWER(:in613),NLS_LOWER(:in614),NLS_LOWER(:in615),NLS_LOWER(:in616),NLS_LOWER(:in617),NLS_LOWER(:in618),NLS_LOWER(:in619),NLS_LOWER(:in620),NLS_LOWER(:in621),NLS_LOWER(:in622),NLS_LOWER(:in623),NLS_LOWER(:in624),NLS_LOWER(:in625),NLS_LOWER(:in626),NLS_LOWER(:in627),NLS_LOWER(:in628),NLS_LOWER(:in629),NLS_LOWER(:in630),NLS_LOWER(:in631),NLS_LOWER(:in632),NLS_LOWER(:in633),NLS_LOWER(:in634),NLS_LOWER(:in635),NLS_LOWER(:in636),NLS_LOWER(:in637),NLS_LOWER(:in638),NLS_LOWER(:in639),NLS_LOWER(:in640),NLS_LOWER(:in641),NLS_LOWER(:in642),NLS_LOWER(:in643),NLS_LOWER(:in644),NLS_LOWER(:in645),NLS_LOWER(:in646),NLS_LOWER(:in647),NLS_LOWER(:in648),NLS_LOWER(:in649),NLS_LOWER(:in650),NLS_LOWER(:in651),NLS_LOWER(:in652),NLS_LOWER(:in653),NLS_LOWER(:in654),NLS_LOWER(:in655),NLS_LOWER(:in656),NLS_LOWER(:in657),NLS_LOWER(:in658),NLS_LOWER(:in659),NLS_LOWER(:in660),NLS_LOWER(:in661),NLS_LOWER(:in662),NLS_LOWER(:in663),NLS_LOWER(:in664),NLS_LOWER(:in665),NLS_LOWER(:in666),NLS_LOWER(:in667),NLS_LOWER(:in668),NLS_LOWER(:in669),NLS_LOWER(:in670),NLS_LOWER(:in671),NLS_LOWER(:in672),NLS_LOWER(:in673),NLS_LOWER(:in674),NLS_LOWER(:in675),NLS_LOWER(:in676),NLS_LOWER(:in677),NLS_LOWER(:in678),NLS_LOWER(:in679),NLS_LOWER(:in680),NLS_LOWER(:in681),NLS_LOWER(:in682),NLS_LOWER(:in683),NLS_LOWER(:in684),NLS_LOWER(:in685),NLS_LOWER(:in686),NLS_LOWER(:in687),NLS_LOWER(:in688),NLS_LOWER(:in689),NLS_LOWER(:in690),NLS_LOWER(:in691),NLS_LOWER(:in692),NLS_LOWER(:in693),NLS_LOWER(:in694),NLS_LOWER(:in695),NLS_LOWER(:in696),NLS_LOWER(:in697),NLS_LOWER(:in698),NLS_LOWER(:in699),NLS_LOWER(:in700),NLS_LOWER(:in701),NLS_LOWER(:in702),NLS_LOWER(:in703),NLS_LOWER(:in704),NLS_LOWER(:in705),NLS_LOWER(:in706),NLS_LOWER(:in707),NLS_LOWER(:in708),NLS_LOWER(:in709),NLS_LOWER(:in710),NLS_LOWER(:in711),NLS_LOWER(:in712),NLS_LOWER(:in713),NLS_LOWER(:in714),NLS_LOWER(:in715),NLS_LOWER(:in716),NLS_LOWER(:in717),NLS_LOWER(:in718),NLS_LOWER(:in719),NLS_LOWER(:in720),NLS_LOWER(:in721),NLS_LOWER(:in722),NLS_LOWER(:in723),NLS_LOWER(:in724),NLS_LOWER(:in725),NLS_LOWER(:in726),NLS_LOWER(:in727),NLS_LOWER(:in728),NLS_LOWER(:in729),NLS_LOWER(:in730),NLS_LOWER(:in731),NLS_LOWER(:in732)) )) C WHERE (SUBSTR(NLS_LOWER(A.DATAAREAID),1,4) IN (NLS_LOWER(:in733),NLS_LOWER(:in734),NLS_LOWER(:in735),NLS_LOWER(:in736),NLS_LOWER(:in737),NLS_LOWER(:in738),NLS_LOWER(:in739),NLS_LOWER(:in740),NLS_LOWER(:in741),NLS_LOWER(:in742),NLS_LOWER(:in743),NLS_LOWER(:in744),NLS_LOWER(:in745),NLS_LOWER(:in746),NLS_LOWER(:in747),NLS_LOWER(:in748),NLS_LOWER(:in749),NLS_LOWER(:in750),NLS_LOWER(:in751),NLS_LOWER(:in752),NLS_LOWER(:in753),NLS_LOWER(:in754),NLS_LOWER(:in755),NLS_LOWER(:in756),NLS_LOWER(:in757),NLS_LOWER(:in758),NLS_LOWER(:in759),NLS_LOWER(:in760),NLS_LOWER(:in761),NLS_LOWER(:in762),NLS_LOWER(:in763),NLS_LOWER(:in764),NLS_LOWER(:in765),NLS_LOWER(:in766),NLS_LOWER(:in767),NLS_LOWER(:in768),NLS_LOWER(:in769),NLS_LOWER(:in770),NLS_LOWER(:in771),NLS_LOWER(:in772),NLS_LOWER(:in773),NLS_LOWER(:in774),NLS_LOWER(:in775),NLS_LOWER(:in776),NLS_LOWER(:in777),NLS_LOWER(:in778),NLS_LOWER(:in779),NLS_LOWER(:in780),NLS_LOWER(:in781),NLS_LOWER(:in782),NLS_LOWER(:in783),NLS_LOWER(:in784),NLS_LOWER(:in785),NLS_LOWER(:in786),NLS_LOWER(:in787),NLS_LOWER(:in788),NLS_LOWER(:in789),NLS_LOWER(:in790),NLS_LOWER(:in791),NLS_LOWER(:in792),NLS_LOWER(:in793),NLS_LOWER(:in794),NLS_LOWER(:in795),NLS_LOWER(:in796),NLS_LOWER(:in797),NLS_LOWER(:in798),NLS_LOWER(:in799),NLS_LOWER(:in800),NLS_LOWER(:in801),NLS_LOWER(:in802),NLS_LOWER(:in803),NLS_LOWER(:in804),NLS_LOWER(:in805),NLS_LOWER(:in806),NLS_LOWER(:in807),NLS_LOWER(:in808),NLS_LOWER(:in809),NLS_LOWER(:in810),NLS_LOWER(:in811),NLS_LOWER(:in812),NLS_LOWER(:in813),NLS_LOWER(:in814),NLS_LOWER(:in815),NLS_LOWER(:in816),NLS_LOWER(:in817),NLS_LOWER(:in818),NLS_LOWER(:in819),NLS_LOWER(:in820),NLS_LOWER(:in821),NLS_LOWER(:in822),NLS_LOWER(:in823),NLS_LOWER(:in824),NLS_LOWER(:in825),NLS_LOWER(:in826),NLS_LOWER(:in827),NLS_LOWER(:in828),NLS_LOWER(:in829),NLS_LOWER(:in830),NLS_LOWER(:in831),NLS_LOWER(:in832),NLS_LOWER(:in833),NLS_LOWER(:in834),NLS_LOWER(:in835),NLS_LOWER(:in836),NLS_LOWER(:in837),NLS_LOWER(:in838),NLS_LOWER(:in839),NLS_LOWER(:in840),NLS_LOWER(:in841),NLS_LOWER(:in842),NLS_LOWER(:in843),NLS_LOWER(:in844),NLS_LOWER(:in845),NLS_LOWER(:in846),NLS_LOWER(:in847),NLS_LOWER(:in848),NLS_LOWER(:in849),NLS_LOWER(:in850),NLS_LOWER(:in851),NLS_LOWER(:in852),NLS_LOWER(:in853),NLS_LOWER(:in854)) ) AND (((((SUBSTR(NLS_LOWER(B.PARTYID),1,20)=NLS_LOWER(:in855)) AND (B.ISPRIMARY=:in856)) AND (B.VALIDFROMDATETIME<=:in857)) AND (B.VALIDTODATETIME>:in858)) AND (B.RECID=A.PARTYADDRESSRELATIONSHIPRECID AND (SUBSTR(NLS_LOWER(B.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(A.DATAAREAID),1,4)))) AND ((SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) IN (NLS_LOWER(:in859),NLS_LOWER(:in860),NLS_LOWER(:in861),NLS_LOWER(:in862),NLS_LOWER(:in863),NLS_LOWER(:in864),NLS_LOWER(:in865),NLS_LOWER(:in866),NLS_LOWER(:in867),NLS_LOWER(:in868),NLS_LOWER(:in869),NLS_LOWER(:in870),NLS_LOWER(:in871),NLS_LOWER(:in872),NLS_LOWER(:in873),NLS_LOWER(:in874),NLS_LOWER(:in875),NLS_LOWER(:in876),NLS_LOWER(:in877),NLS_LOWER(:in878),NLS_LOWER(:in879),NLS_LOWER(:in880),NLS_LOWER(:in881),NLS_LOWER(:in882),NLS_LOWER(:in883),NLS_LOWER(:in884),NLS_LOWER(:in885),NLS_LOWER(:in886),NLS_LOWER(:in887),NLS_LOWER(:in888),NLS_LOWER(:in889),NLS_LOWER(:in890),NLS_LOWER(:in891),NLS_LOWER(:in892),NLS_LOWER(:in893),NLS_LOWER(:in894),NLS_LOWER(:in895),NLS_LOWER(:in896),NLS_LOWER(:in897),NLS_LOWER(:in898),NLS_LOWER(:in899),NLS_LOWER(:in900),NLS_LOWER(:in901),NLS_LOWER(:in902),NLS_LOWER(:in903),NLS_LOWER(:in904),NLS_LOWER(:in905),NLS_LOWER(:in906),NLS_LOWER(:in907),NLS_LOWER(:in908),NLS_LOWER(:in909),NLS_LOWER(:in910),NLS_LOWER(:in911),NLS_LOWER(:in912),NLS_LOWER(:in913),NLS_LOWER(:in914),NLS_LOWER(:in915),NLS_LOWER(:in916),NLS_LOWER(:in917),NLS_LOWER(:in918),NLS_LOWER(:in919),NLS_LOWER(:in920),NLS_LOWER(:in921),NLS_LOWER(:in922),NLS_LOWER(:in923),NLS_LOWER(:in924),NLS_LOWER(:in925),NLS_LOWER(:in926),NLS_LOWER(:in927),NLS_LOWER(:in928),NLS_LOWER(:in929),NLS_LOWER(:in930),NLS_LOWER(:in931),NLS_LOWER(:in932),NLS_LOWER(:in933),NLS_LOWER(:in934),NLS_LOWER(:in935),NLS_LOWER(:in936),NLS_LOWER(:in937),NLS_LOWER(:in938),NLS_LOWER(:in939),NLS_LOWER(:in940),NLS_LOWER(:in941),NLS_LOWER(:in942),NLS_LOWER(:in943),NLS_LOWER(:in944),NLS_LOWER(:in945),NLS_LOWER(:in946),NLS_LOWER(:in947),NLS_LOWER(:in948),NLS_LOWER(:in949),NLS_LOWER(:in950),NLS_LOWER(:in951),NLS_LOWER(:in952),NLS_LOWER(:in953),NLS_LOWER(:in954),NLS_LOWER(:in955),NLS_LOWER(:in956),NLS_LOWER(:in957),NLS_LOWER(:in958),NLS_LOWER(:in959),NLS_LOWER(:in960),NLS_LOWER(:in961),NLS_LOWER(:in962),NLS_LOWER(:in963),NLS_LOWER(:in964),NLS_LOWER(:in965),NLS_LOWER(:in966),NLS_LOWER(:in967),NLS_LOWER(:in968),NLS_LOWER(:in969),NLS_LOWER(:in970),NLS_LOWER(:in971),NLS_LOWER(:in972),NLS_LOWER(:in973),NLS_LOWER(:in974),NLS_LOWER(:in975),NLS_LOWER(:in976),NLS_LOWER(:in977),NLS_LOWER(:in978),NLS_LOWER(:in979),NLS_LOWER(:in980)) ) AND ((C.RECID=A.ADDRESSRECID AND (SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(A.DATAAREAID),1,4))) AND (SUBSTR(NLS_LOWER(C.DATAAREAID),1,4)=SUBSTR(NLS_LOWER(A.REFCOMPANYID),1,4) AND (SUBSTR(NLS_LOWER(C.DATAAREAID),1,4) = SUBSTR(NLS_LOWER(A.DATAAREAID),1,4))))) Пока не нашел ничего лучше кроме как разорвать запрос на 2.
отдельно выполнить
X++: select crosscompany:dataAreaidList addressRelationshipMapping
where addressRelationshipMapping.RefCompanyId == GRD_address.dataAreaId
join tableId
from partyAddressRelationship
where partyAddressRelationship.RecId == addressRelationshipMapping.PartyAddressRelationshipRecId
&& partyAddressRelationship.PartyId == _partyId
&& partyAddressRelationship.IsPrimary
&& partyAddressRelationship.ValidFromDateTime <= _transDateTime
&& partyAddressRelationship.ValidToDateTime > _transDateTimeА затем отдельно в нужной компании начитать address
|