I'm trying to create a function to return a table.
I want to take the following query provide the where clause with a parameter, essentially creating a parameterized view.
WITH SUB (
SORT_STR,
ITEM_LEVEL,
BU_CD,
PART_NO_SUB,
PART_NO_ORG,
BRAND_CD,
TO_UNIFY_PART_NO
) AS (
SELECT
CAST(SUBSTITUTE.BU_CD || ':' || SUB_PART_REG.PART_NO_ORG || ':' || TO_CHAR(SUBSTITUTE.SUBSTITUTE_REGISTER_DT, 'YYYY-MM-DD') AS VARCHAR(600)),
CAST(0 AS DECIMAL(2,0)),
SUBSTITUTE.BU_CD,
SUB_PART_REG.PART_NO_SUB,
SUB_PART_REG.PART_NO_ORG,
SUB_PART_REG.BRAND_CD_ORG,
SUB_PART_REG.PART_NO_SUB
FROM
F6RD_SUB_PART_REG SUB_PART_REG
INNER JOIN
F6RD_SUBSTITUTE SUBSTITUTE
ON SUB_PART_REG.SUBSTITUTE_NO = SUBSTITUTE.SUBSTITUTE_NO
AND SUBSTITUTE.SUBSTITUTE_CL = 1
INNER JOIN
F6RD_PART PART_SUB
ON SUB_PART_REG.PART_NO_SUB = PART_SUB.PART_NO
AND SUB_PART_REG.BRAND_CD_SUB = PART_SUB.BRAND_CD
INNER JOIN
F6RD_PART PART_ORG
ON SUB_PART_REG.PART_NO_ORG = PART_ORG.PART_NO
AND SUB_PART_REG.BRAND_CD_ORG = PART_ORG.BRAND_CD
WHERE
SUB_PART_REG.PART_NO_ORG LIKE '16097-1072%' Parameterize THIS
AND SUB_PART_REG.BRAND_CD_ORG LIKE '%'
AND PART_SUB.SUPPLY_STOP_DT IS NULL
AND PART_ORG.SUPPLY_STOP_DT IS NOT NULL
UNION ALL
SELECT
CAST(SUB.SORT_STR || ':' || TO_CHAR(SUBSTITUTE.SUBSTITUTE_REGISTER_DT, 'YYYY-MM-DD') || ',' || SUBSTITUTE.BU_CD || ':' || SUB_PART_REG_ORG.PART_NO_ORG || ':' || TO_CHAR(SUBSTITUTE.SUBSTITUTE_REGISTER_DT, 'YYYY-MM-DD') AS VARCHAR(600)),
CAST(SUB.ITEM_LEVEL + 1 AS DECIMAL(2,0)),
SUBSTITUTE.BU_CD,
SUB_PART_REG_ORG.PART_NO_SUB,
SUB_PART_REG_ORG.PART_NO_ORG,
SUB_PART_REG_ORG.BRAND_CD_ORG,
SUB.TO_UNIFY_PART_NO
FROM
SUB SUB
INNER JOIN
F6RD_SUB_PART_REG SUB_PART_REG_ORG
ON SUB.PART_NO_ORG = SUB_PART_REG_ORG.PART_NO_SUB
INNER JOIN
F6RD_SUBSTITUTE SUBSTITUTE
ON SUB_PART_REG_ORG.SUBSTITUTE_NO = SUBSTITUTE.SUBSTITUTE_NO
AND SUB.BU_CD = SUBSTITUTE.BU_CD
AND SUBSTITUTE.SUBSTITUTE_CL = 1
INNER JOIN
F6RD_PART PART
ON SUB_PART_REG_ORG.PART_NO_ORG = PART.PART_NO
AND SUB_PART_REG_ORG.BRAND_CD_ORG = PART.BRAND_CD
WHERE
PART.SUPPLY_STOP_DT IS NOT NULL
AND SUB.ITEM_LEVEL < 50 )
SELECT
SUB.SORT_STR
, SUB.ITEM_LEVEL
, SUB.BU_CD
, SUB.TO_UNIFY_PART_NO
, SUB.BRAND_CD
FROM
(
SELECT
TO_UNIFY_PART_NO,
BU_CD,
BRAND_CD,
MAX(ITEM_LEVEL) AS ITEM_LEVEL
FROM
SUB
GROUP BY
TO_UNIFY_PART_NO,
BU_CD,
BRAND_CD ) GROUP_SUB
INNER JOIN
SUB SUB
ON GROUP_SUB.TO_UNIFY_PART_NO = SUB.TO_UNIFY_PART_NO
AND GROUP_SUB.ITEM_LEVEL = SUB.ITEM_LEVEL
AND GROUP_SUB.BU_CD = SUB.BU_CD
AND GROUP_SUB.BRAND_CD = SUB.BRAND_CD
I think this is likely a very simple problem, I just cannot seem to pin down the correct syntax. I have created types for both the rows from the select and a table to hold the rows, but have had no success. Can someone show me the correct way to do this? Thanks