Hello Everyone,
The table I have highlighted in BOLD may or not have data in, but I need the query to be able to execute, even if that table is not populated....(There will always be data)
Is there like a case statement or any kind of a way to dynamically add a condition?
Like When the **cntstpool is not populated for the parameters(**cntstid, drawid)_ add this condition OR ( cp.ptnid IS NULL ) ) if the table is populated for the parameters, cntstid, drawid, then remove the condition?
or is there a way to rewrite the entire query so this isn't a issue at all.
WITH do_tickets AS (
SELECT
sub.ptnid_count,
sub.ptnid
FROM
(
SELECT
COUNT(DISTINCT dt.ptnid) AS ptnid_count,
dt.ptnid AS ptnid
FROM
ems.drawingticket dt,
ems.contest c,
ems.drawing d,
ems.cntstpool cp,
(
SELECT
cntstid,
drawid AS drawid
FROM
ems.drawing
WHERE
cntstid = 10000088
AND drawid = 10001412
) fod
WHERE
d.cntstid = c.cntstid
AND dt.cntstid = c.cntstid
AND fod.drawid = d.drawid
AND fod.cntstid = d.cntstid
AND ( cp.ptnid(+) = dt.ptnid
AND cp.iseligible = 1
AND ( ( c.winlimit = 1
AND cp.cntstid = 10000088 )
OR c.winlimit = 2
AND cp.cntstid = 10000088
AND cp.drawid = 10001412 ))
AND ( ( c.cleandraw = 0
AND d.drawid <= fod.drawid
AND dt.drawid <= fod.drawid )
OR ( c.cleandraw = 1
AND d.drawid = fod.drawid
AND dt.drawid = fod.drawid ) )
AND dt.ticketid NOT IN (
SELECT
dw.ticketid
FROM
drawingwinner dw
WHERE
dw.ptnid = dt.ptnid
)
GROUP BY
dt.ptnid,
dt.cntstid,
substr(d.prizedesc, 1, 24)
) sub
)
SELECT
*
FROM
do_tickets;
---------------------------------------------------