Показать сообщение отдельно
Старый 19.06.2017, 21:25   #40  
mazzy is offline
mazzy
Участник
Аватар для mazzy
Лучший по профессии 2015
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
Лучший по профессии 2011
Лучший по профессии 2009
 
29,472 / 4494 (208) ++++++++++
Регистрация: 29.11.2001
Адрес: Москва
Записей в блоге: 10
Цитата:
Сообщение от mazzy Посмотреть сообщение
3. как в ИЛИ условие добавить информацию, что это разные таблицы?
в общем, интересно.
акс2012 выдает результат toString() немного в другом виде. с алиасами (именами датасорсов).

вот код и запрос в акс2012.
QueryRun нужен только для того, чтобы отправить запрос на SQL.

X++:
static void Job2(Args _args)
{
    QueryRun             qr;
    Query                q = new Query(queryStr(Query2));
    QueryBuildDataSource qbdsITT = q.dataSourceTable(tableNum(inventTransferTable));
    QueryBuildDataSource qbdsILfrom = q.dataSourceTable(tableNum(InventLocation), 1);
    QueryBuildDataSource qbdsILto = q.dataSourceTable(tableNum(InventLocation), 2);
    QueryBuildRange      qbr = SysQuery::findOrCreateRange(qbdsILfrom, fieldNum(InventLocation, DataAreaId));
    ;
    Debug::assert(qbr != null);
    Debug::assert(qbdsILfrom != null);
    Debug::assert(qbdsILto != null);
        
    qbr.value(strFmt('((%1.%3 == %4) || (%2.%3 == %4))',
        qbdsIlTo.name(),
        qbdsIlFrom.name(),
        fieldStr(InventLocation, InventLocationType),
        3));
    
    qr = new QueryRun(q);
    qr.next();

    info(q.dataSourceNo(1).toString());
}
результат в инфологе:
X++:
SELECT * FROM InventTransferTable(InventTransferTable_1) JOIN * FROM InventLocation(InventLocation_1) ON InventTransferTable.InventLocationIdFrom = InventLocation.InventLocationId AND 0 = InventLocation.InventLocationType AND ((((InventLocation_2.InventLocationType == 3) || (InventLocation_1.InventLocationType == 3)))) JOIN * FROM InventLocation(InventLocation_2) ON InventTransferTable.InventLocationIdFrom = InventLocation.InventLocationId AND 0 = InventLocation.InventLocationType
запрос в SQL (без forceLiteral для чистоты эксперимента)
X++:
declare @p1 int
set @p1=1074248408
declare @p2 int
set @p2=180888571
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 bigint,@P4 nvarchar(5),@P5 int,@P6 int,@P7 int,@P8 bigint,@P9 nvarchar(5),@P10 int',N'SELECT T1.TRANSFERID,T1.INVENTLOCATIONIDFROM,T1.INVENTLOCATIONIDTO,T1.TRANSFERSTATUS,T1.SHIPDATE,T1.RECEIVEDATE,T1.FROMADDRESSNAME,T1.TOADDRESSNAME,T1.AUTORESERVATION,T1.DLVMODEID,T1.DLVTERMID,T1.FREIGHTZONEID,T1.FREIGHTSLIPTYPE,T1.FROMCONTACTPERSON,T1.INVENTLOCATIONIDTRANSIT,T1.ATPINCLPLANNEDORDERS,T1.ATPTIMEFENCE,T1.DELIVERYDATECONTROLTYPE,T1.TOCONTACTPERSON,T1.ATPAPPLYSUPPLYTIMEFENCE,T1.ATPAPPLYDEMANDTIMEFENCE,T1.ATPBACKWARDDEMANDTIMEFENCE,T1.ATPBACKWARDSUPPLYTIMEFENCE,T1.FROMPOSTALADDRESS,T1.TOPOSTALADDRESS,T1.STATPROCID,T1.PORT,T1.TRANSPORT,T1.TRANSACTIONCODE,T1.TRANSFERTYPE_IN,T1.TRPACKINGSLIPAUTONUMBERING_LT,T1.INVENTPROFILETYPE_RU,T1.INVENTPROFILEID_RU,T1.INVENTPROFILEUSERELATED_RU,T1.DRIVERCONTACT_RU,T1.TRANSPORTINVOICETYPE_RU,T1.CARGODESCRIPTION_RU,T1.CARGOPACKING_RU,T1.RETAILREPLENISHREFTABLEID,T1.RETAILREPLENISHREFRECID,T1.RETAILRETAILSTATUSTYPE,T1.CARRIERCODE_RU,T1.CARRIERTYPE_RU,T1.CURRENCYCODE_RU,T1.DELIVERYDATE_RU,T1.DRIVER_RU,T1.DRIVERNAME_RU,T1.DRIVINGLICENSENUM_RU,T1.INTRASTATFULFILLMENTDATE_HU,T1.INTRASTATSPECMOVE_CZ,T1.INVENTPROFILEIDTO_RU,T1.LADINGPOSTALADDRESS_RU,T1.LICENSECARDNUM_RU,T1.LICENSECARDREGNUM_RU,T1.LICENSECARDSERIES_RU,T1.LICENSECARDTYPE_RU,T1.PARTYACCOUNTNUM_RU,T1.PARTYAGREEMENTHEADEREXT_RU,T1.PDSOVERRIDEFEFO,T1.PRICEGROUPID_RU,T1.TRANSFERTYPE_RU,T1.TRANSPORTATIONDOCUMENT,T1.TRANSPORTATIONPAYER_RU,T1.TRANSPORTATIONPAYERTYPE_RU,T1.TRANSPORTATIONTYPE_RU,T1.UNLADINGPOSTALADDRESS_RU,T1.VEHICLEMODEL_RU,T1.VEHICLEPLATENUM_RU,T1.WAYBILLNUM_RU,T1.SHOULDPOSTEXPENSEAMTTOINVENTORY_IN,T1.CREATEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.INVENTLOCATIONID,T2.NAME,T2.MANUAL,T2.EMPTYPALLETLOCATION,T2.MAXPICKINGROUTEVOLUME,T2.PICKINGLINETIME,T2.MAXPICKINGROUTETIME,T2.WMSLOCATIONIDDEFAULTRECEIPT,T2.WMSLOCATIONIDDEFAULTISSUE,T2.INVENTLOCATIONIDREQMAIN,T2.REQREFILL,T2.INVENTLOCATIONTYPE,T2.INVENTLOCATIONIDQUARANTINE,T2.INVENTLOCATIONLEVEL,T2.REQCALENDARID,T2.WMSAISLENAMEACTIVE,T2.WMSRACKNAMEACTIVE,T2.WMSRACKFORMAT,T2.WMSLEVELNAMEACTIVE,T2.WMSLEVELFORMAT,T2.WMSPOSITIONNAMEACTIVE,T2.WMSPOSITIONFORMAT,T2.USEWMSORDERS,T2.INVENTLOCATIONIDTRANSIT,T2.VENDACCOUNT,T2.BRANCHNUMBER,T2.INVENTSITEID,T2.INVENTCOUNTINGGROUP_BR,T2.CUSTACCOUNT_BR,T2.INVENTPROFILETYPE_RU,T2.INVENTPROFILEID_RU,T2.INVENTLOCATIONIDGOODSINROUTE_RU,T2.WMSLOCATIONIDGOODSINROUTE_RU,T2.ACTIVITYTYPE_RU,T2.NUMBERSEQUENCEGROUP_RU,T2.RETAILWEIGHTEX1,T2.FSHSTORE,T2.RBODEFAULTWMSPALLETID,T2.RBODEFAULTWMSLOCATIONID,T2.RBODEFAULTINVENTPROFILEID_RU,T2.ALLOWLABORSTANDARDS,T2.ALLOWMARKINGRESERVATIONREMOVAL,T2.CONSOLIDATESHIPATRTW,T2.CUSTACCOUNT_HU,T2.CYCLECOUNTALLOWPALLETMOVE,T2.DECREMENTLOADLINE,T2.DEFAULTKANBANFINISHEDGOODSLOCATION,T2.DEFAULTPRODUCTIONFINISHGOODSLOCATION,T2.DEFAULTSHIPMAINTENANCELOC,T2.DEFAULTSTATUSID,T2.PRINTBOLBEFORESHIPCONFIRM,T2.PRODRESERVEONLYWHSE,T2.REMOVEINVENTBLOCKINGONSTATUSCHANGE,T2.RESERVEATLOADPOST,T2.RETAILINVENTNEGFINANCIAL,T2.RETAILINVENTNEGPHYSICAL,T2.RETAILWMSLOCATIONIDDEFAULTRETURN,T2.RETAILWMSPALLETIDDEFAULTRETURN,T2.UNIQUECHECKDIGITS,T2.VENDACCOUNTCUSTOM_RU,T2.WAREHOUSEAUTORELEASERESERVATION,T2.WHSENABLED,T2.DEFAULTCONTAINERTYPECODE,T2.DEFAULTPRODUCTIONINPUTLOCATION,T2.DEFAULTRETURNCREDITONLYLOCATION,T2.WHSRAWMATERIALPOLICY,T2.MODIFIEDDATETIME,T2.DEL_MODIFIEDTIME,T2.MODIFIEDBY,T2.CREATEDDATETIME,T2.DEL_CREATEDTIME,T2.CREATEDBY,T2.RECVERSION,T2.PARTITION,T2.RECID,T3.INVENTLOCATIONID,T3.NAME,T3.MANUAL,T3.EMPTYPALLETLOCATION,T3.MAXPICKINGROUTEVOLUME,T3.PICKINGLINETIME,T3.MAXPICKINGROUTETIME,T3.WMSLOCATIONIDDEFAULTRECEIPT,T3.WMSLOCATIONIDDEFAULTISSUE,T3.INVENTLOCATIONIDREQMAIN,T3.REQREFILL,T3.INVENTLOCATIONTYPE,T3.INVENTLOCATIONIDQUARANTINE,T3.INVENTLOCATIONLEVEL,T3.REQCALENDARID,T3.WMSAISLENAMEACTIVE,T3.WMSRACKNAMEACTIVE,T3.WMSRACKFORMAT,T3.WMSLEVELNAMEACTIVE,T3.WMSLEVELFORMAT,T3.WMSPOSITIONNAMEACTIVE,T3.WMSPOSITIONFORMAT,T3.USEWMSORDERS,T3.INVENTLOCATIONIDTRANSIT,T3.VENDACCOUNT,T3.BRANCHNUMBER,T3.INVENTSITEID,T3.INVENTCOUNTINGGROUP_BR,T3.CUSTACCOUNT_BR,T3.INVENTPROFILETYPE_RU,T3.INVENTPROFILEID_RU,T3.INVENTLOCATIONIDGOODSINROUTE_RU,T3.WMSLOCATIONIDGOODSINROUTE_RU,T3.ACTIVITYTYPE_RU,T3.NUMBERSEQUENCEGROUP_RU,T3.RETAILWEIGHTEX1,T3.FSHSTORE,T3.RBODEFAULTWMSPALLETID,T3.RBODEFAULTWMSLOCATIONID,T3.RBODEFAULTINVENTPROFILEID_RU,T3.ALLOWLABORSTANDARDS,T3.ALLOWMARKINGRESERVATIONREMOVAL,T3.CONSOLIDATESHIPATRTW,T3.CUSTACCOUNT_HU,T3.CYCLECOUNTALLOWPALLETMOVE,T3.DECREMENTLOADLINE,T3.DEFAULTKANBANFINISHEDGOODSLOCATION,T3.DEFAULTPRODUCTIONFINISHGOODSLOCATION,T3.DEFAULTSHIPMAINTENANCELOC,T3.DEFAULTSTATUSID,T3.PRINTBOLBEFORESHIPCONFIRM,T3.PRODRESERVEONLYWHSE,T3.REMOVEINVENTBLOCKINGONSTATUSCHANGE,T3.RESERVEATLOADPOST,T3.RETAILINVENTNEGFINANCIAL,T3.RETAILINVENTNEGPHYSICAL,T3.RETAILWMSLOCATIONIDDEFAULTRETURN,T3.RETAILWMSPALLETIDDEFAULTRETURN,T3.UNIQUECHECKDIGITS,T3.VENDACCOUNTCUSTOM_RU,T3.WAREHOUSEAUTORELEASERESERVATION,T3.WHSENABLED,T3.DEFAULTCONTAINERTYPECODE,T3.DEFAULTPRODUCTIONINPUTLOCATION,T3.DEFAULTRETURNCREDITONLYLOCATION,T3.WHSRAWMATERIALPOLICY,T3.MODIFIEDDATETIME,T3.DEL_MODIFIEDTIME,T3.MODIFIEDBY,T3.CREATEDDATETIME,T3.DEL_CREATEDTIME,T3.CREATEDBY,T3.RECVERSION,T3.PARTITION,T3.RECID FROM INVENTTRANSFERTABLE T1 CROSS JOIN INVENTLOCATION T2 CROSS JOIN INVENTLOCATION T3 WHERE ((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) AND (((T2.PARTITION=@P3) AND (T2.DATAAREAID=@P4)) AND (((T3.INVENTLOCATIONTYPE=@P5) OR (T2.INVENTLOCATIONTYPE=@P6)) AND ((T2.INVENTLOCATIONTYPE=@P7) AND (T1.INVENTLOCATIONIDFROM=T2.INVENTLOCATIONID)))) AND (((T3.PARTITION=@P8) AND (T3.DATAAREAID=@P9)) AND ((T3.INVENTLOCATIONTYPE=@P10) AND (T1.INVENTLOCATIONIDFROM=T3.INVENTLOCATIONID))) ORDER BY T1.TRANSFERID',@p5 output,@p6 output,@p7 output,5637144576,N'ceu',5637144576,N'ceu',3,3,0,5637144576,N'ceu',0
select @p1, @p2, @p5, @p6, @p7
как видите, на SQL все уходит с алиасами, которые аксапта генерит сама.
эти алиасы нельзя узнать в x++

насколько я помню, названий датасорсов всегда было достаточно, чтобы аксапта корректно разобралась. и где-то вроде даже было обсуждение на эту тему. Но в упор не помню в какой версии это появилось.

smailik, если у вас, после применения qbds.name() результат совпадает с ожидаемым, значит и в акс4 правило наименований датасорсов тоже работало.
Вложения
Тип файла: xpo Query.xpo (2.5 Кб, 199 просмотров)
__________________
полезное на axForum, github, vk, coub.

Последний раз редактировалось mazzy; 19.06.2017 в 21:37.