X++ SQL to SQL Server Conversion: Selecting Tax Information

This example shows how to convert X++ SQL code to standard SQL Server SQL code. The original X++ code selects 'TaxCode' and 'TaxName' from multiple tables based on specific conditions. The converted SQL Server SQL code maintains the same logic and structure:

Original X++ Code:

select firstonly TaxCode, TaxName from taxTable2
                    join TaxOutgoingLedgerDimension from taxLedgerAccountGroup2
                    where taxTable2.TaxAccountGroup == taxLedgerAccountGroup2.TaxAccountGroup
                    join taxGroupData2
                    where taxTable2.TaxCode == taxGroupData2.TaxCode
                        && taxGroupData2.TaxGroup == _mtxCustOutsideSalesLine.MtxTaxGroup
                    join taxOnItem2
                    where taxTable2.TaxCode  ==  taxOnItem2.TaxCode
                        && taxOnItem2.TaxItemGroup == _mtxCustOutsideSalesLine.MtxTaxItemGroup;

Converted SQL Server SQL Code:

SELECT TaxCode, TaxName
FROM taxTable2
JOIN TaxOutgoingLedgerDimension ON taxTable2.TaxAccountGroup = TaxOutgoingLedgerDimension.TaxAccountGroup
JOIN taxGroupData2 ON taxTable2.TaxCode = taxGroupData2.TaxCode
JOIN taxOnItem2 ON taxTable2.TaxCode = taxOnItem2.TaxCode
WHERE taxGroupData2.TaxGroup = _mtxCustOutsideSalesLine.MtxTaxGroup
AND taxOnItem2.TaxItemGroup = _mtxCustOutsideSalesLine.MtxTaxItemGroup;

This SQL Server SQL code is equivalent to the original X++ code. It selects 'TaxCode' and 'TaxName' from 'taxTable2' and joins it with the other tables based on the provided conditions. The 'WHERE' clause filters the results based on the values of '_mtxCustOutsideSalesLine.MtxTaxGroup' and '_mtxCustOutsideSalesLine.MtxTaxItemGroup'.