Запрос ИЛИ в query

[smailik]

Query я подправил. Сейчас без InventTable работает, а вот с ней висит на подсчете количества

query = new Query(querystr(myQuery));
    SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(InventTransferTable)), fieldnum(InventTransferTable, ReceiveDate)).value(strfmt('%1..%2', 20\05\2017, toDay()));
    SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(InventTransferTable)), fieldnum(InventTransferTable, TransferStatus)).value(QueryValue(InventTransferStatus::Received));
    SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(InventTransferTable)), fieldnum(InventTransferTable, InventLocationIdFrom)).value('242');

    if (inventLocationType != InventLocationType::Standard)
    {
        qbrDataAreaId = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(InventLocation), 2), fieldNum(InventLocation, DataAreaId));
        qbrDataAreaId.value(strFmt('((%1.%3 = %4) || (%2.%3 = %4))',
            'InventLocationTo',
            'InventLocationFrom',
            fieldStr(InventLocation, InventLocationType),
            any2int(inventLocationType)));
    }

    s = query.datasourceno(1).toString();

    info(s);

    queryRun = new QueryRun(query);

    iRowCountTmp = SysQuery::countLoops(queryRun);

Без InventTable
SELECT * FROM InventTransferTable WHERE ((ReceiveDate>={ts '2017-05-20 00:00:00.000'} AND ReceiveDate<={ts '2017-06-19 00:00:00.000'})) AND ((TransferStatus = 2)) JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdTo = InventLocation.InventLocationId JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdFrom = InventLocation.InventLocationId AND ((((InventLocationTo.InventLocationType = 3) || (InventLocationFrom.InventLocationType = 3))))

С InventTable
SELECT * FROM InventTransferTable WHERE ((ReceiveDate>={ts '2017-05-20 00:00:00.000'} AND ReceiveDate<={ts '2017-06-19 00:00:00.000'})) AND ((TransferStatus = 2)) JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdTo = InventLocation.InventLocationId AND ((((InventLocationTo.InventLocationType = 3) || (InventLocationFrom.InventLocationType = 3)))) JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdFrom = InventLocation.InventLocationId

[mazzy]

Цитата:

Сообщение от smailik

Без InventTable
SELECT * FROM InventTransferTable WHERE ((ReceiveDate>={ts '2017-05-20 00:00:00.000'} AND ReceiveDate<={ts '2017-06-19 00:00:00.000'})) AND ((TransferStatus = 2)) JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdTo = InventLocation.InventLocationId JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdFrom = InventLocation.InventLocationId AND ((((InventLocationTo.InventLocationType = 3) || (InventLocationFrom.InventLocationType = 3))))

Ага. связь есть.
Ага. интересно.

А можно попросить вас выкинуть из проекта все, не относящееся к вопросу.
в частности ReceiveDate, TransferStatus. оставьте только то, что относится к вопросу с ИЛИ.

должно остаться так:

Цитата:

Сообщение от smailik

Без InventTable
SELECT * FROM InventTransferTable WHERE JOIN * FROM InventLocation WHERE InventTransferTable.InventLocationIdTo = InventLocation.InventLocationId JOIN * FROM InventLocation WHERE ((((InventLocationTo.InventLocationType = 3) || (InventLocationFrom.InventLocationType = 3))))

насколько я понимаю, ситуация вот в чем:
1. в запросе используется одна и та же таблица два раза (СкладОткуда, СкладКуда)
2. нужно добавить сложное ИЛИ-условие со ссылками на эти две таблицы.
3. как в ИЛИ условие добавить информацию, что это разные таблицы?

Сейчас видно, что в тексте запроса отображается два одинаковых имя, хотя запрос составлен совершенно корректно.

вроде, это где-то уже обсуждалось.

==========
smailik, Logger уже спрашивал. а можете показать текст SQL-запроса, который показывает сам SQL?

[mazzy]

Цитата:

Сообщение от mazzy

3. как в ИЛИ условие добавить информацию, что это разные таблицы?

в общем, интересно.
акс2012 выдает результат toString() немного в другом виде. с алиасами (именами датасорсов).

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

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());
}

результат в инфологе:

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 для чистоты эксперимента)

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 правило наименований датасорсов тоже работало.