Product Hierarchy.
Utilize multiple joins of the Company Parameters to obtain code names in a single query.

Code

def qapi = api.queryApi()

def t1 = qapi.tables().products()
def t2 = qapi.tables().companyParameterRows("ProductGroup")
def t3 = qapi.tables().companyParameterRows("ProductLine")
def t4 = qapi.tables().companyParameterRows("SubCategory")

return qapi.source(t1, [t1.sku(), t1.ProductGroup, t1.ProductLine, t1.SubCategory])
        .leftOuterJoin(t2, { cols -> [t2.ProductGroupName] },
                { cols ->
                    qapi.exprs().and(
                            t2.ProductGroup.equal(cols.ProductGroup)
                    )
                }
        )
        .leftOuterJoin(t3, { cols -> [t3.ProductLineName] },
                { cols ->
                    qapi.exprs().and(
                            t3.ProductLine.equal(cols.ProductLine)
                    )
                }
        )
        .leftOuterJoin(t4, { cols -> [t4.SubCategoryName] },
                { cols ->
                    qapi.exprs().and(
                            t4.SubCategory.equal(cols.SubCategory)
                    )
                }
        )
        .stream { it.collect { it } }

Result

sku	ProductGroup	ProductLine	SubCategory	ProductGroupName	ProductLineName	SubCategoryName

sku	ProductGroup	ProductLine	SubCategory	ProductGroupName	ProductLineName	SubCategoryName
10000001	00L	064	794	ProductGroup 00L	ProductLine 064	SubCategory 794
IDR0001	Consumable Bundles	Offset	35x50	ProductGroup Consumable Bundles	ProductLine Offset	SubCategory 35x50
IDR0002	Consumable Bundles	Offset	70x100	ProductGroup Consumable Bundles	ProductLine Offset	SubCategory 70x100
FEED003	Printing Presses	Offset	70x100	ProductGroup Printing Presses	ProductLine Offset	SubCategory 70x100
FEED004	Printing Presses	Offset	70x100	ProductGroup Printing Presses	ProductLine Offset	SubCategory 70x100

Multiple Joins

Code

Result

See Also