Provides a list of all macros in the database
including their components, accessories and alternatives
| You can execute this query using Excel Query Builder in RentalPoint..... |  |
WITH CTE AS (
SELECT
ISNULL(B.parent_code,' ') AS ParentCode,
ISNULL(I.descriptionv6,' ') AS ParentDescription,
-- FIXED: Proper CASE + alias
ISNULL(
CASE I.product_config
WHEN 0 THEN 'Product'
WHEN 1 THEN 'PACKAGE'
WHEN 2 THEN 'MACRO'
END, ' '
) AS ParentConfig,
ISNULL(B.product_code,' ') AS ChildCode,
ISNULL(I2.descriptionv6,' ') AS ChildDescription,
CASE B.variable_part
WHEN 0 THEN '0-Component'
WHEN 1 THEN '1-Accessory'
WHEN 2 THEN '2-Alternative'
END AS ChildDefinedAs,
B.SelectComp AS ChildSelectComp,
-- FIXED: numeric → varchar before ISNULL
ISNULL(CAST(B.qty_v5 AS varchar(20)),' ') AS ChildQty,
ISNULL(
CAST(I2.product_config AS varchar(10)),
' '
) AS ChildConfig,
ROW_NUMBER() OVER (
PARTITION BY B.parent_code
ORDER BY B.sub_seq_no
) AS rn
FROM tblbill B
LEFT JOIN tblinvmas I ON I.product_code = B.parent_code
LEFT JOIN tblinvmas I2 ON I2.product_code = B.product_code
WHERE I.product_type_v41 = 0
AND I.product_config IN (2)
)
SELECT
CASE WHEN rn = 1 THEN ParentCode ELSE ' ' END AS [Macro],
CASE WHEN rn = 1 THEN ParentDescription ELSE ' ' END AS [Macro Description],
ChildCode AS [Product],
ChildDefinedAs AS [Type],
ChildSelectComp AS [Optional],
ChildDescription AS [Product Description],
ChildQty AS [Product Qty]
FROM CTE
ORDER BY ParentCode,ChildDefinedAs;