Можно делать join таблицы к себе же через Query?

[raz]

Цитата:

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

Проверил, не совсем то, вернее совсем не то получается, что в задаче

А что не то?
Свиду вроде все нормально.

info вот что выдает:
SELECT * FROM Table1 GROUP BY Table1.StorageId ASC, Table1.WareId ASC, Table1.PartyId ASC WHERE ((StorageId = 000 OR StorageId = 001)) AND ((WareId = 002 OR WareId = 003)) JOIN SUM(AmountMod), SUM(CostMod) FROM Table1 WHERE Table1.StorageId = Table1.StorageId AND Table1.WareId = Table1.WareId

Планы запросов только скобками отличаются:

SELECT A.STORAGEID,A.WAREID,A.PARTYID,SUM(B.AMOUNTMOD),SUM(B.COSTMOD)
FROM TABLE1 A,TABLE1 B
WHERE ((A.DATAAREAID='DAT')
AND (((A.STORAGEID='000') OR (A.STORAGEID='001'))
AND ((A.WAREID='002') OR (A.WAREID='003'))))
AND ((B.DATAAREAID='DAT')
AND ((A.STORAGEID=B.STORAGEID)
AND (A.WAREID=B.WAREID)))
GROUP BY A.STORAGEID,A.WAREID,A.PARTYID
ORDER BY A.STORAGEID,A.WAREID,A.PARTYID OPTION(FAST 22)

SELECT A.STORAGEID,A.WAREID,A.PARTYID,SUM(B.AMOUNTMOD),SUM(B.COSTMOD)
FROM TABLE1 A,TABLE1 B
WHERE (A.DATAAREAID='DAT')
AND ((B.DATAAREAID='DAT')
AND (((((A.STORAGEID='000') OR (A.STORAGEID='001'))
AND ((A.WAREID='002') OR (A.WAREID='003')))
AND (B.STORAGEID=A.STORAGEID))
AND (B.WAREID=A.WAREID)))
GROUP BY A.STORAGEID,A.WAREID,A.PARTYID
ORDER BY A.STORAGEID,A.WAREID,A.PARTYID OPTION(FAST 22)

PHP код:

static void Job39(Args _args)
{
    Table1                          wp0;
    Table1                          wp1;
    Table1                          wp0_q;
    Table1                          wp1_q;
    StorageId                xxx = "000";
    StorageId                yyy = "001";
    WareId                zzz = "002";
    WareId                aaa = "003";

    Query                           query = new Query();
    QueryBuildDataSource            qbds1, qbds2;

    QueryRun                        QueryRun;
    ;
    select * from wp0 group by StorageId, WareId, PartyId
    join sum(AmountMod), sum(CostMod) from wp1
    where
    (wp0.StorageId == xxx || wp0.StorageId == yyy) &&
    (wp0.WareId == zzz || wp0.WareId == aaa) &&
    wp1.StorageId == wp0.StorageId &&
    wp1.WareId == wp0.WareId;

    qbds1 = query.AddDataSource(tablenum(Table1));
    qbds1.ordermode(ordermode::groupby);
    qbds1.addSortField(fieldnum(Table1, StorageId));
    qbds1.addSortField(fieldnum(Table1, WareId));
    qbds1.addSortField(fieldnum(Table1, PartyId));
    qbds1.addrange(fieldnum(Table1, StorageId)).value(xxx + "," + yyy);
    qbds1.addrange(fieldnum(Table1, WareId)).value(zzz + "," + aaa);


    qbds2 = qbds1.AddDataSource(tablenum(Table1));
    qbds2.ordermode(ordermode::groupby);
    qbds2.addSelectionField(fieldnum(Table1, AmountMod), selectionField::Sum);
    qbds2.addSelectionField(fieldnum(Table1, CostMod), selectionField::Sum);
    qbds2.addLink(fieldnum(Table1, StorageId), fieldnum(Table1, StorageId));
    qbds2.addLink(fieldnum(Table1, WareId), fieldnum(Table1, WareId));

    info(query.dataSourceNo(1).toString());

    QueryRun = new QueryRun(query);

    QueryRun.next();

    wp0_q = QueryRun.getNo(1);
    wp1_q = QueryRun.getNo(2);
} 


[korvin]

Цитата:

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

А что не то?
Свиду вроде все нормально.

info вот что выдает:
SELECT * FROM Table1 GROUP BY Table1.StorageId ASC, Table1.WareId ASC, Table1.PartyId ASC WHERE ((StorageId = 000 OR StorageId = 001)) AND ((WareId = 002 OR WareId = 003)) JOIN SUM(AmountMod), SUM(CostMod) FROM Table1 WHERE Table1.StorageId = Table1.StorageId AND Table1.WareId = Table1.WareId

Планы запросов только скобками отличаются:

SELECT A.STORAGEID,A.WAREID,A.PARTYID,SUM(B.AMOUNTMOD),SUM(B.COSTMOD)
FROM TABLE1 A,TABLE1 B
WHERE ((A.DATAAREAID='DAT')
AND (((A.STORAGEID='000') OR (A.STORAGEID='001'))
AND ((A.WAREID='002') OR (A.WAREID='003'))))
AND ((B.DATAAREAID='DAT')
AND ((A.STORAGEID=B.STORAGEID)
AND (A.WAREID=B.WAREID)))
GROUP BY A.STORAGEID,A.WAREID,A.PARTYID
ORDER BY A.STORAGEID,A.WAREID,A.PARTYID OPTION(FAST 22)

SELECT A.STORAGEID,A.WAREID,A.PARTYID,SUM(B.AMOUNTMOD),SUM(B.COSTMOD)
FROM TABLE1 A,TABLE1 B
WHERE (A.DATAAREAID='DAT')
AND ((B.DATAAREAID='DAT')
AND (((((A.STORAGEID='000') OR (A.STORAGEID='001'))
AND ((A.WAREID='002') OR (A.WAREID='003')))
AND (B.STORAGEID=A.STORAGEID))
AND (B.WAREID=A.WAREID)))
GROUP BY A.STORAGEID,A.WAREID,A.PARTYID
ORDER BY A.STORAGEID,A.WAREID,A.PARTYID OPTION(FAST 22)

PHP код:

static void Job39(Args _args)
{
    Table1                          wp0;
    Table1                          wp1;
    Table1                          wp0_q;
    Table1                          wp1_q;
    StorageId                xxx = "000";
    StorageId                yyy = "001";
    WareId                zzz = "002";
    WareId                aaa = "003";

    Query                           query = new Query();
    QueryBuildDataSource            qbds1, qbds2;

    QueryRun                        QueryRun;
    ;
    select * from wp0 group by StorageId, WareId, PartyId
    join sum(AmountMod), sum(CostMod) from wp1
    where
    (wp0.StorageId == xxx || wp0.StorageId == yyy) &&
    (wp0.WareId == zzz || wp0.WareId == aaa) &&
    wp1.StorageId == wp0.StorageId &&
    wp1.WareId == wp0.WareId;

    qbds1 = query.AddDataSource(tablenum(Table1));
    qbds1.ordermode(ordermode::groupby);
    qbds1.addSortField(fieldnum(Table1, StorageId));
    qbds1.addSortField(fieldnum(Table1, WareId));
    qbds1.addSortField(fieldnum(Table1, PartyId));
    qbds1.addrange(fieldnum(Table1, StorageId)).value(xxx + "," + yyy);
    qbds1.addrange(fieldnum(Table1, WareId)).value(zzz + "," + aaa);


    qbds2 = qbds1.AddDataSource(tablenum(Table1));
    qbds2.ordermode(ordermode::groupby);
    qbds2.addSelectionField(fieldnum(Table1, AmountMod), selectionField::Sum);
    qbds2.addSelectionField(fieldnum(Table1, CostMod), selectionField::Sum);
    qbds2.addLink(fieldnum(Table1, StorageId), fieldnum(Table1, StorageId));
    qbds2.addLink(fieldnum(Table1, WareId), fieldnum(Table1, WareId));

    info(query.dataSourceNo(1).toString());

    QueryRun = new QueryRun(query);

    QueryRun.next();

    wp0_q = QueryRun.getNo(1);
    wp1_q = QueryRun.getNo(2);
} 


Извиняюсь был неправ, ошибся у себя в условии, ваш пример отлично работает!
Беру на вооружение. Жаль только что уже сделал через XppCompiler, переработал лишнего...