Skip to main content

SQL Hacks


This blog consists of daily routine SQL hacks which are beneficial for versatile requirements:

To get ENUM-Values with its corresponding integer:

select t1.NAME,t2.ENUMID,t2.ENUMVALUE,t2.NAME EnumValueName from ENUMIDTABLE t1
inner join ENUMVALUETABLE t2 on t1.ID=t2.ENUMID
where t1.NAME = 'VSD_SALESORDERWORKFLOWSTATUS'

To get ROW number for the records:

SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (ORDER BY Recid) R FROM SALESORDERHEADERCDSSTAGING) As sq
WHERE R BETWEEN 72340 and 146995

Comments

Post a Comment

Popular posts from this blog

Edit Method on Form

Edit Method D365 for a form Data Source 1. To create an edit method first create a controller class. with following properties  public static edit MainAccountNum LedgerJournalTransLedger(LedgerJournalTrans _ledgerjournal, boolean _set, MainAccountNum _id) { MainAccountNum accountId = _id; MainAccount mainAccount = MainAccount::findByMainAccountId(_id); if(_set) { if(_ledgerjournal.AccountType== LedgerJournalACType::Ledger) { mainAccount = MainAccount::findByMainAccountId(accountId); if(_ledgerjournal.LedgerDimension) { DimensionDefault defaultDim = LedgerDimensionFacade::getDefaultDimensionFromLedgerDimension(_ledgerjournal.LedgerDimension); _ledgerjournal.LedgerDimension = LedgerDimensionDefaultingEngine::getLedgerDimensionFromAccountAndDim(mainAccount.RecId, DimensionHierarchy::getAccountStructure(mainAccount.RecId), defaultDim); } else { _ledgerjournal.LedgerDimension = LedgerDimensionDefaultingEngine::getLedgerDimensionFromAccountAndDim(mainAccount.RecId, DimensionHierarchy::ge...
Post a Comment
Read more

Lookup and Mandatory fields on Form using Form event Handlers D365

Lookup and Mandatory fields on Form using Form event Handlers D365 [FormControlEventHandler(formControlStr(VendBankAccounts, VendBankAccount_SLD_BankCode), FormControlEventType::Lookup)] public static void VendBankAccount_SLD_BankCode_OnLookup(FormControl sender, FormControlEventArgs e)     {         Query query = new Query();         QueryBuildDataSource queryBuildDataSource;         FormControlCancelableSuperEventArgs event = e as FormControlCancelableSuperEventArgs;         QueryBuildRange queryBuildRange;         SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SLD_BankCodes), sender);         sysTableLookup.addSelectionField(fieldNum(SLD_BankCodes, BankCodes));         sysTableLookup.addLookupField(fieldNum(SLD_BankCodes, BankCodes));         sysTableLookup.addLookupField(fieldNum(SLD_BankCode...
Post a Comment
Read more

Field Level Security In Ax 2012

FIELD LEVEL SECURITY IN AX 2012 In this blog we will discuss field level security applied in  Ax 2012. 1. Field level security is added on form level as well in Ax 2012 its bit different then field level security in D365 2. This also requires privileges to be applied on form specific fields only. Step #1 :  Change datasource property of the field which need to be added in security. Step #2 : Now add these 2 fields in form --- > permissions --- > Read Step #3 : Now finally create privilege and add these fields explicitly in it.       HEY HEY HEY !!!! HACK OF THE DAY  Add Fields in Entry point privilege section of form  Explicitly.  
Post a Comment
Read more