AX - How get customer name in external SQL select

select a.accountnum, b.name from custTable a
join dirPartyTable b
on b.recid = a.party
where
a.accountnum like 'MINSK32680'

Output:

accountnum           name
-------------------- -------------------------
MINSK32680           MINSK WHEEL TRACTOR PLANT

(1 row(s) affected)

SQL SERVER, ORACLE - How use conditional logic in select, case statement

Prevention zero divide error:

select b.inventlocationid, itemid, a.availphysical, a.postedqty, 
a.postedvalue / case 
                  when a.postedqty = 0 then 1 
                  else a.postedqty 
                end as cost_avg_mj
from inventsum a
join inventdim b
on b.inventdimid = a.inventdimid and
b.dataareaid = a.dataareaid
where
a.itemid like 'S-%' and
a.availphysical <> 0

AX - How get enum labels to db table

When you have AX enum, often you need to read its labels externally - for example in SQL question.

In AX exists table SRSAnalysisEnums, which contain enum name, enum value and its label.

select * from SRSAnalysisEnums
where
enumname = 'NoYes' and
languageid in ( 'de', 'en-us' )

Output:

ENUMITEMVALUE ENUMITEMLABEL LANGUAGEID ENUMNAME ENUMITEMNAME RECVERSION RECID

0 Nein de NoYes No 1 5637179205
0 No en-us NoYes No 1 5637144576 1
Ja de NoYes Yes 1 5637179250 1 
Yes en-us NoYes Yes 1 5637144577

For rebuild this table (->new enum in AX) you can use class BIGenerator, method populateSRSAnalysisEnums(). Implicitly are generated enums from tables in AX BI perspectives.

static void cieb_updateSSRSEnums(Args _args)
{
    BIGenerator::populateSRSAnalysisEnums();
    info( "OK" );
}

Explicitly you can change in inner method addFrameworkEnumsToDictEnumSet(), where you can define some others special enums for SRSAnalysisEnums table:

void addFrameworkEnumsToDictEnumSet()
{
    addEnumToDictEnumSet(new DictEnum(enumNum(boolean)));
    addEnumToDictEnumSet(new DictEnum(enumNum(NoYes)));
    addEnumToDictEnumSet(new DictEnum(enumNum(AutoNoYes)));

    /* -- added - start */
    addEnumToDictEnumSet( new DictEnum( enumNum( cieb_technology_sale_type_enum ) ) );
    addEnumToDictEnumSet( new DictEnum( enumNum( cieb_technology_type_enum ) ) );
    addEnumToDictEnumSet( new DictEnum( enumNum( cieb_technology_type1_enum ) ) );
    /* -- added - end */

    addEdtEnumToDictEnumSet(new DictType(extendedTypeNum(BIIsNotApplicable)));
}

AX - Where are defined languages for BI generation

When you regenerate BI table SRSAnalysisEnums, languages for regeneration are defined in table BIUdmTranslations:

select * from BIUdmTranslations
where
generate = 1

Output:

LANGUAGEID GENERATE    RECVERSION  RECID
---------- ----------- ----------- --------------------
cs         1           1963884511  5637144578
en-us      1           2063432002  5637144591

2 row(s) affected)