Select
Select
select T1;
select * from T1;
select x from T1; (one field x)
Count
select count(RecId) from positionCount;
join
select T1
join T2
where T2.x == T1.x
join T3
where T3.y = T2.y =========> Is OK
//outer join
while select SPLPsGasNominationDaily
where SPLPsGasNominationDaily.DateTime >= dateTimeFrom
&& SPLPsGasNominationDaily.DateTime < dateTimeTo
&& SPLPsGasNominationDaily.DateTimeUntil == 0
outer join SPLPsGasControlHour
where SPLPsGasControlHour.DateTime == SPLPsGasNominationDaily.DateTime
&& SPLPsGasControlHour.DateTimeUntil == 0
exists
//exists
select T1
exists join T2
where T2.x == T1.x;
//notexists
select T1
notexists join T2
where T2.x == T1.x;
//Remark : if there are 2 notexists it doesn't work
select T1
notexists join T2
where T2.x == T1.x;
notexists join T3
where T3.x == T1.x; ===> doesn't work.
Maxof
select maxof(LineNum) from ledgerRowDefLine
where ledgerRowDefLine.RowDefinition == _rowDefinition
&& ledgerRowDefLine.ParentRecId == 0;
While
while select forupdate bomVersionUpdate
where bomVersionUpdate.bomId == this.bomId
join oldInventDim
where oldInventDim.InventDimId == bomVersionUpdate.InventDimId
&& oldInventDim.InventSiteId != this.SiteId
{
newInventDim = oldInventDim.data();
newInventDim.InventSiteId = this.SiteId;
newInventDim = InventDim::findOrCreate(newInventDim);
bomVersionUpdate.InventDimId = newInventDim.InventDimId;
if (! bomVersionUpdate.validateWrite())
{
throw error("@SYS18447");
}
bomVersionUpdate.update();
}
Sum
while select forceplaceholders sum(CostAmountAdjustment) from inventSettlement
index hint DateVoucherIdx
group by ItemId,ItemGroupId,BalanceSheetPosting,BalanceSheetAccount,OperationsPosting,OperationsAccount,Dimension
where inventSettlement.TransDate == transDate &&
inventSettlement.Voucher == voucher &&
inventSettlement.CostAmountAdjustment != 0 &&
inventSettlement.Posted == NoYes::No
Sum, group by : field in select or not
//the group by is done even if the field or the group by is not in the select : example :
static void BPL_Sh_Test2(Args _args)
{
LedgerTrans LedgerTrans;
VendTrans vendTrans;
date fromDate,toDate;
;
fromDate = mkdate(1, 01, 2013);
toDate = endmth(fromDate) + 200;
Info("---group by accountNum - while---");
while
select accountNum, sum(AmountMST) from LedgerTrans
group by accountNum
where LedgerTrans.accountNum like "11000*"
{
info(LedgerTrans.AccountNum + " " + num2str(LedgerTrans.amountMST,6,2,2,2));
}
//Message (16:45:15)
//---group by accountNum - while---
//110000 39,615,92
//110001 -16,554,00
//110002 -104,599,21
Info("---group by accountNum - select without account num ---> show the first");
select sum(AmountMST) from LedgerTrans //<-- group by accounNum even if it is not in select
group by accountNum
where LedgerTrans.accountNum like "11000*";
info(LedgerTrans.AccountNum + " " + num2str(LedgerTrans.amountMST,6,2,2,2));
//---group by accountNum - select without account num ---> show the first
//110000 39,615,92
}
Sum with join 2 DS
Good example : Serveral Group by
static void SPLPsm_ReadData_groupBy_Good(Args _args)
{
SPLPsmReadData SPLPsmReadData;
SPLPsmCalendar SPLPsmCalendar;
SPLPsmTimeFrame SPLPsmTimeFrame;
int i;
while select
StoreDev,
SUM(ValueActivePos)
from SPLPsmReadData
Group by
StoreDev
Order by
StoreDev
join SPLPsmCalendar
where
SPLPsmReadData.DateDate == SPLPsmCalendar.DATEMSV
join SPLPsmTimeFrame
group by TimeFrame
Order by
TimeFrame
where
SPLPsmTimeFrame.Season == SPLPsmCalendar.Season
&& SPLPsmTimeFrame.DayType == SPLPsmCalendar.DayType
&& SPLPsmReadData.Hour == SPLPsmTimeFrame.ToTime
{
info(SPLPsmReadData.StoreDev + " " + enum2str(SPLPsmTimeFrame.TimeFrame) + " " +
num2str(SPLPsmReadData.ValueActivePos,6,2,1,3));
i++;
}
info("line : " + int2str(i));
}
Bad example : just one Group by
//In the select we can put just columns from the first Table.
//To take also the columns from the other DS we get all the second dataSource
//Ex we get the column SPLPsmTimeFrame.TimeFrame from the second DS.
//Also : - Order is before Group by
// - Group by is before join
// - Group by fields from the second Table2 are Table2.Field, here SPLPsmTimeFrame.TimeFrame
static void SPLPsm_ReadData_groupBy(Args _args)
{
SPLPsmReadData SPLPsmReadData;
SPLPsmCalendar SPLPsmCalendar;
SPLPsmTimeFrame SPLPsmTimeFrame;
int i;
while select
StoreDevo,
SUM(ValueActivePos)
from SPLPsmReadData
order by StoreDevo
Group by
StoreDevo,
SPLPsmTimeFrame.TimeFrame //Group by doesn't work on the table 2 !
join SPLPsmCalendar
where
SPLPsmReadData.DateDate == SPLPsmCalendar.DATEMSV
join SPLPsmTimeFrame
where
SPLPsmTimeFrame.Season == SPLPsmCalendar.Season
&& SPLPsmTimeFrame.DayType == SPLPsmCalendar.DayType
&& SPLPsmReadData.Hour == SPLPsmTimeFrame.ToTime
{
info(SPLPsmReadData.StoreDevo + " " + enum2str(SPLPsmTimeFrame.TimeFrame) + " " +
num2str(SPLPsmReadData.ValueActivePos,6,2,1,3));
i++;
}
info("line : " + int2str(i));
}
GroupBy - order by
Group by + order by with field DESC on table TMP doesn't work !
while select TrackId, StationId
from SDXILDistMatrixTmp
order by TrackId ASC, StationId DESC
group by TrackId ASC ,StationId DESC
{
}
insert recordset
// exemple class LedgerTransStatementDP
insert_recordset _tmpTrans
(IsGroupedOpeningTrans,
PostingLayer,
PeriodCode,
MainFocusValue,
MainFocusDescription,
TransTxt,
TransDate)
select
yes,
currentLayer,
opening,
MainFocusValue,
MainFocusDescription,
openingText,
_fromDate
from sourceTrans
group by sourceTrans.MainFocusValue, sourceTrans.MainFocusDescription
where sourceTrans.createdTransactionId == this.parmCreatedTransactionId()
notExists join sourceTransNotExists where
sourceTransNotExists.MainFocusValue == sourceTrans.MainFocusValue &&
sourceTransNotExists.IsGroupedOpeningTrans == true &&
sourceTransNotExists.createdTransactionId == this.parmCreatedTransactionId();
insert Recordset with fix value
// exemple class LedgerTransStatementDP
LedgerTransStatementStagingTmp sourceTrans;
FiscalPeriodType endingBalancePeriodType = 4; // One more than closing transactions
DimensionsTransRecordType total = DimensionsTransRecordType::Total;
TransTxt closingText;
NoYes endingBalance = NoYes::Yes;
CurrentOperationsTax currentLayer = CurrentOperationsTax::Current;
closingText = "@SYS50986";
this.setUserConnection(sourceTrans);
sourceTrans.linkPhysicalTableInstance(_ledgerTransStatementTmp); //do a new instance of _ledgerTransStatementTmp ?
insert_recordset _ledgerTransStatementTmp
(PostingLayer,
PeriodCode,
TransDate,
AmountCredit,
AmountDebit,
MainFocusValue,
MainFocusDescription,
GeneralJournalEntryDataArea,
TransTxt,
IsEndingBalance)
select
currentLayer, //===Fix value not in the table sourceTrans = LedgerTransStatementStagingTmp===//
endingBalancePeriodType,
_endDate,
sum(AmountCredit),
sum(AmountDebit),
MainFocusValue,
MainFocusDescription,
GeneralJournalEntryDataArea,
closingText,
endingBalance
from sourceTrans
group by sourceTrans.MainFocusValue, sourceTrans.MainFocusDescription, sourceTrans.GeneralJournalEntryDataArea
where sourceTrans.RecordType != DimensionsTransRecordType::Aggregate; // Filter out the sub-total records so that amounts are not considered twice
Temp DB, linkPhysicalTableInstance/h3>
LedgerTransStatementStagingTmp sourceTrans; //TableType = TempDB <===============
this.setUserConnection(sourceTrans);
sourceTrans.linkPhysicalTableInstance(_ledgerTransStatementTmp); //Link sourceTrans to __ledgerTransStatementTmp (LedgerTransStatementStagingTmp)
insert_recordset _ledgerTransStatementTmp
(PostingLayer,
PeriodCode,
TransDate,
AmountCredit,
AmountDebit,
MainFocusValue,
MainFocusDescription,
GeneralJournalEntryDataArea,
TransTxt,
IsEndingBalance)
select
currentLayer,
endingBalancePeriodType,
_endDate,
sum(AmountCredit),
sum(AmountDebit),
MainFocusValue,
MainFocusDescription,
GeneralJournalEntryDataArea,
closingText,
endingBalance
from sourceTrans
group by sourceTrans.MainFocusValue, sourceTrans.MainFocusDescription, sourceTrans.GeneralJournalEntryDataArea
where sourceTrans.RecordType != DimensionsTransRecordType::Aggregate; // Filter out the sub-total records so that amounts are not considered twice
Update Recordeset
update_recordset ledgerJournalTrans
setting AccountNum = this.AccountID
where ledgerJournalTrans.Company == innerCompany
&& ledgerJournalTrans.AccountType == LedgerJournalACType::Bank
&& ledgerJournalTrans.AccountNum == originalBankAccountTable.AccountID;
}
Delete_from
delete_from TMP;
// Delete the source records
delete_from _tmpTrans
where _tmpTrans.PeriodCode == FiscalPeriodType::Opening &&
_tmpTrans.IsGroupedOpeningTrans == false &&
_tmpTrans.createdTransactionId == this.parmCreatedTransactionId();
Example Sum fields in while (with rupture)
Static server SPLPsFinancialGasTmp FillTableDay(SPLPsFinancialGasTmp SPLPsFinancialGasTmp)
{
SPLPsFinancialGasTmp SPLPsFinancialGasTmpDay,SPLPsFinancialGasTmpSave;
SPLPsDate DateSave;
real RequieredQty,ApprovedQty,StdQty,Cost;
int i;
;
ttsBegin;
while select SPLPsFinancialGasTmp
order by dateday, datetime
{
i++;
if (i != 1 && SPLPsFinancialGasTmpSave.dateday != SPLPsFinancialGasTmp.dateday)
{
SPLPsFinancialGasTmpDay.DateDay = SPLPsFinancialGasTmpSave.dateday;
SPLPsFinancialGasTmpDay.RequieredQty = RequieredQty;
SPLPsFinancialGasTmpDay.ApprovedQty = ApprovedQty;
SPLPsFinancialGasTmpDay.StdQty = StdQty;
SPLPsFinancialGasTmpDay.Cost = Cost;
SPLPsFinancialGasTmpDay.SFQty = SPLPsFinancialGasTmpSave.SFQty;
SPLPsFinancialGasTmpDay.Cost2 = SPLPsFinancialGasTmpSave.Cost2;
SPLPsFinancialGasTmpDay.CarryForward = SPLPsFinancialGasTmpSave.CarryForward;
SPLPsFinancialGasTmpDay.MakeUpGas = SPLPsFinancialGasTmpSave.MakeUpGas;
SPLPsFinancialGasTmpDay.insert();
RequieredQty = 0;
ApprovedQty = 0;
StdQty = 0;
Cost = 0;
}
RequieredQty += SPLPsFinancialGasTmp.RequieredQty;
ApprovedQty += SPLPsFinancialGasTmp.ApprovedQty;
StdQty += SPLPsFinancialGasTmp.StdQty;
Cost += SPLPsFinancialGasTmp.Cost;
SPLPsFinancialGasTmpSave.data(SPLPsFinancialGasTmp);
}
// The last record is write at the last
if (i)
{
SPLPsFinancialGasTmpDay.DateDay = SPLPsFinancialGasTmpSave.dateday;
SPLPsFinancialGasTmpDay.RequieredQty = RequieredQty;
SPLPsFinancialGasTmpDay.ApprovedQty = ApprovedQty;
SPLPsFinancialGasTmpDay.StdQty = StdQty;
SPLPsFinancialGasTmpDay.Cost = Cost;
SPLPsFinancialGasTmpDay.SFQty = SPLPsFinancialGasTmpSave.SFQty;
SPLPsFinancialGasTmpDay.Cost2 = SPLPsFinancialGasTmpSave.Cost2;
SPLPsFinancialGasTmpDay.CarryForward = SPLPsFinancialGasTmpSave.CarryForward;
SPLPsFinancialGasTmpDay.MakeUpGas = SPLPsFinancialGasTmpSave.MakeUpGas;
SPLPsFinancialGasTmpDay.insert();
}
ttsCommit;
return SPLPsFinancialGasTmpDay;
}
Select cross company
// Find on all the companies.
public static WMSPickingRoute SPLfindCrossCompByRecId(
recId _recId,
boolean _update = false)
{
WMSPickingRoute wmsPickingRoute;
wmsPickingRoute.selectForUpdate(_update);
select firstonly crossCompany wmsPickingRoute
index hint IdIdx
where wmsPickingRoute.recId == _recId;
return wmsPickingRoute;
}
Note
I have : T_Table and T_Trans , and the filter is on on T_Table.
Correct but we need to test if it is not too long.
-------
select sum(AmountMST) from ledgerTrans
index hint ACDate
where ledgerTrans.TransDate >= fromDate
&& ledgerTrans.TransDate <= toDate
join LedgerTable
where ledgerTable.SPLCustGroupId == CustGroup.CustGroup (the filter is on the T_Table)
&& ledgerTrans.AccountNum == ledgerTable.AccountNum;
select sum(AmountMST) from ledgerTrans
index hint ACDate
join LedgerTable
where ledgerTrans.TransDate >= fromDate
&& ledgerTrans.TransDate <= toDate
&& ledgerTable.SPLCustGroupId == CustGroup.CustGroup
&& ledgerTrans.AccountNum == ledgerTable.AccountNum;
in sql we write :
-----------------
select sum(AmountMST) from ledgerTable, ledgerTrans
where ledgerTable.SPLCustGroupId == CustGroup.CustGroup
and ledgerTrans.AccountNum == ledgerTable.AccountNum
and ledgerTrans.TransDate >= fromDate
and ledgerTrans.TransDate <= toDate
Not Correct
-----------
select sum(AmountMST) from LedgerTable (error because AmountMst doen't exist in LedgerTable)
where ledgerTable.SPLCustGroupId == CustGroup.CustGroup
&& ledgerTrans.AccountNum == ledgerTable.AccountNum;
join ledgerTrans
where ledgerTrans.TransDate >= fromDate
&& ledgerTrans.TransDate <= toDate
&& ledgerTrans.AccountNum == ledgerTable.AccountNum;