I have the below select statement, which I want to use an IF statement in the where clause:
SELECT
assignment_projection_report.year,
assignment_projection_report.status,
assignment_projection_report.description,
SUM(assignment_projection_report.pairs) AS pairs,
SUM(assignment_projection_report.total_fob) AS total_fob,
SUM(assignment_projection_report.total_fac) AS total_fac,
SUM(assignment_projection_report.total_first) AS total_first,
SUM(assignment_projection_report.total_comission) AS total_comission,
SUM(assignment_projection_report.STRADA_7_comission) AS STRADA_7_COMISSION,
SUM(assignment_projection_report.STRADA_5_comission) AS STRADA_5_COMISSION,
SUM(assignment_projection_report.OPP_comission) AS OPP_COMISSION
FROM
(
SELECT
DATE_FORMAT(po.lsd,'%Y') year,
po.status,
po.po,
divisions.description,
SUM(po_item.pairs-ifnull(shipment_partial.pairs,0)) as pairs,
SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0)) as total_fob,
SUM((po_item.pairs*po_item.factory_liq)-ifnull(shipment_partial.pairs*po_item.factory_liq,0)) as total_fac,
SUM((po_item.pairs*po_item.firs_sale_price)-ifnull(shipment_partial.pairs*po_item.firs_sale_price,0)) as total_first,
CASE WHEN (
(
SUM((po_item.pairs*po_item.factory_liq)-ifnull(shipment_partial.pairs*po_item.factory_liq,0))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(comission_total.percentual,0)/100))
)
>
SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))
) THEN
0
ELSE
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(comission_total.percentual,0)/100))
END as total_comission,
CASE WHEN (
(
SUM((po_item.pairs*po_item.factory_liq)-ifnull(shipment_partial.pairs*po_item.factory_liq,0))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_7.percentual,0)/100))
)
>
SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))
)
THEN
0
ELSE
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_7.percentual,0)/100))
END as STRADA_7_comission,
CASE WHEN (
(
SUM((po_item.pairs*po_item.factory_liq)-ifnull(shipment_partial.pairs*po_item.factory_liq,0))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_7.percentual,0)/100))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_5.percentual,0)/100))
)
>
SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))
)
THEN
0
ELSE
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_5.percentual,0)/100))
END as STRADA_5_comission,
CASE WHEN (
(
SUM((po_item.pairs*po_item.factory_liq)-ifnull(shipment_partial.pairs*po_item.factory_liq,0))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_7.percentual,0)/100))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(STRADA_5.percentual,0)/100))
+
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(OPP.percentual,0)/100))
)
>
SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))
)
THEN
0
ELSE
(SUM((po_item.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))))-ifnull(shipment_partial.pairs*(po_item.customer_price*(1-(po_item.special_discount/100))),0))*(ifnull(OPP.percentual,0)/100))
END as OPP_comission
FROM
po
inner join client on
po.client = client.id
inner join divisions on
client.division = divisions.id
inner join po_item on
po.po = po_item.po
inner join season ON
po.season = season.id
left join
(
SELECT
shipment_partial.po,
shipment_partial.sequencial,
SUM(shipment_partial.pairs) AS pairs
FROM
shipment_partial
group by
shipment_partial.po,
shipment_partial.sequencial
) as shipment_partial on
po_item.po = shipment_partial.po and
po_item.sequencial = shipment_partial.sequencial
left join
(
select
po_commission.po,
sum(po_commission.percentual) as percentual
from
po_commission
group by
po_commission.po
) as comission_total on
comission_total.po = po.po
left join po_commission as STRADA_7 on
STRADA_7.po = po.po and
STRADA_7.id_seller = 2
left join po_commission as STRADA_5 on
STRADA_5.po = po.po and
STRADA_5.id_seller = 5
left join po_commission as OPP on
OPP.po = po.po and
OPP.id_seller = 3
WHERE
1=1
AND po.shipment is null
AND po_item.pairs > ifnull(shipment_partial.pairs,0)
AND po.status in ('O','L','P')
AND po.lsd >= STR_TO_DATE('".$_POST['date_initial']."','%m/%d/%Y')
AND po.lsd <= STR_TO_DATE('".$_POST['date_finish']."','%m/%d/%Y')
".$company_session."
GROUP BY
po.po,
divisions.description
ORDER BY
po.cls,
divisions.description
) as assignment_projection_report
group by
assignment_projection_report.year,
assignment_projection_report.description
ORDER BY
assignment_projection_report.year,
assignment_projection_report.status,
assignment_projection_report.description
In the where clause I have (AND po.lsd...), but in my table I also have the po.rsld, how can I add an IF function, like the where clause should work using po.rsld if is not null, but if po.rlsd is null the where clause should check the po.lsd column.
WHERE
1=1
AND po.shipment is null
AND po_item.pairs > ifnull(shipment_partial.pairs,0)
AND po.status in ('O','L','P')
AND po.lsd >= STR_TO_DATE('".$_POST['date_initial']."','%m/%d/%Y')
AND po.lsd <= STR_TO_DATE('".$_POST['date_finish']."','%m/%d/%Y')
Aucun commentaire:
Enregistrer un commentaire