q- parameter use to pass the parameter condition at next heirarchical level / Filtering at OIC Mapper level

 

OrganizationCode =28192 and StructureName='Primary' and ItemNumber='F6-TD-' and Component.EndDateTime =''

Note :In oracle Notes found that the q parameters filtering will not works at components level

So need to use filtering logic at the OIC level .

I have used a IF condition at OIC mapping level 

((string-length (nsmpr10:EndDateTime ) = 0) or (nsmpr10:EndDateTime > fn:current-dateTime()))

There is one more way we can filter the records at mapper that is using  [ ] .

Below is example where we can filter the supplier depending on status by passing status field in [] square brackets.

SQL to Get dates missing in a table for given date range

 WITH all_dates AS (

    SELECT DATE '2025-01-01' + LEVEL - 1 AS dt

    FROM dual

    CONNECT BY LEVEL <= (DATE '2026-01-01' - DATE '2025-01-01') + 1

),

existing_dates AS (

    SELECT TRUNC(creation_date) AS dt

    FROM  XXCUST.CUSTOM_TL -- your table name

    WHERE creation_date BETWEEN DATE '2025-01-01' AND DATE '2026-01-01'

)

SELECT a.dt AS missing_date

FROM all_dates a

LEFT JOIN existing_dates e

       ON a.dt = e.dt

WHERE e.dt IS NULL

ORDER BY a.dt;