July 7, 2022 at 5:23 pm
In the below select which populates a table my boss wants to add case or if to the select for field delay. I was doing it in three steps. Now i have to update based on if a field in one table is greater than 0 it is yes or if field in another table is great than 0 than yes else No
I made the code red I'm not sure how to fix it as it won't run currently.
select
date(sa.Datesent),
sa.InvNum,
sa.fee,
sa.total_ar,
sa.ap_total,
if(ar.ardelay > 0 then Yes
elseif(aj.apdelay > 0 then Yes
else No ,
sa.Customer_site_name,
sa.Shipment_id,
sa.loadcount,
sum(sa.ar) as TransxAR,
sum(sa.AP) as TransxAP,
sum(sa.Fee) as TransxMargin,
sr.total_ap as LoadAP,
sr.total_ar as LoadAR,
sr.total_margin as LoadMargin,
if(arhx.opsAR is null, 0,arhx.opsAR) as OpsAR,
if(apjx.OpsAP is null, 0, apjx.opsAP) as OpsAP,
if(arhx.opsAR is null, 0,arhx.opsAR)-if(apjx.OpsAP is null, 0, apjx.opsAP) as OpsMargin,
if(arhx.acctar is null,0, arhx.acctar) as AcctAR,
if(apjx.acctap is null, 0, apjx.acctap) as AcctAP,
if(arhx.acctar is null,0, arhx.acctar)-if(apjx.acctap is null, 0, apjx.acctap) as AcctMargin,
sdex.SalesRptAR as SalesRptAR,
sdex.SalesRptAP as SalesRptAP,
sdex.SalesRptMargin as SalesRptMargin,
If((SalesRptAR=sr.total_AR and SalesRptAP=sr.total_ap),"Complete","InProcess"),
sa.customer_id,
sa.salescode,
u.full_name,
sa.office_code,
sr.shipment_type_desc_short,
date(sr.pick_depart_loaded),
date(sr.delv_depart_empty),
concat_ws(" to ",sr.shipper_city,sr.consignee_site_city),
sr.imdl_container,
sr.shipment_status_desc,
if(sa.split_percent=0,1,sa.split_percent)
From cs.acct_sales_daily_extract2 sa
left join cs.users u on sa.salesperson_user=u.salesperson_id
left join (select arh.shipment_id,
sum(if(arh.acc_status_id=1,arh.invoice_total,0)) as OpsAR,
sum(if(arh.acc_status_id in (2,3,4),arh.invoice_total,0)) as AcctAR
From cs.shipment_ar_header arh group by arh.shipment_id) arhx on sa.shipment_id=arhx.shipment_id
left join (select shipmentid,
sum(if(ready_for_gp is null or ready_for_gp=0, theamount,0)) as OpsAP,
sum(if(ready_for_gp=1,theamount,0)) as AcctAP
from cs.acct_apaccrual_journal apj where apj.datesent is null group by shipmentid) apjx on sa.shipment_id=apjx.shipmentid
left join (select sde.shipment_id,
sum(sde.ar) as SalesRptAR,
sum(sde.ap) as SalesRptAP,
sum(sde.fee) as SalesRptMargin
From cs.acct_sales_daily_extract2 sde
group by sde.shipment_id) sdex on sa.shipment_id=sdex.shipment_id
left join cs.shipment_reporting sr on sa.shipment_id=sr.shipment_id
LEFT JOIN custom_cs.arh ar ON sn.shipment = ar.shipment_id
LEFT JOIN custom_cs.apj aj ON sn.shipment = aj.shipmentid
where date(sa.Datesent) > '2019-12-31'
group by sa.shipment_id, sa.datesent, sa.customer_id, sa.office_code,sa.Movetype
Order by sa.Customer_site_name
July 7, 2022 at 5:57 pm
First, this website is specifically dedicated to Transact SQL. You might be better off posting to a website specifically for mySQL or to a general SQL website.
Second, stating only that it "won't run currently" is too vague to be helpful. If there is an error message, what is that message?
Also, the title indicates that the problem is with the IF/ELSIF/ELSE, but the text says that you've highlighted the problem section in red, and that section only has the JOIN conditions.
Finally, if this is a data issue, rather than a syntax issue, we would need sample data and expected results in order to help resolve this issue.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 7, 2022 at 6:02 pm
I am not familiar with MySQL, and I don't know what your error message is, but you could try a case statement. You would probably need to include the case statement in the group by too.
case
when ar.ardelay > 0 then 'Yes'
when j.apdelay > 0 then 'Yes'
else 'No'
end,
July 7, 2022 at 6:04 pm
IF elseif is in green don't know why it isn't red as was the joins. The joins in red are what is referenced to in the green if else if.
The error is below.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then Yes
elseif(aj.apdelay > 0 then Yes
else No ' at line 7
here is the if elseif
if(ar.ardelay) > 0 then Yes
elseif(aj.apdelay) > 0 then Yes
else No ,
My apologies for those errors. If I need to go to a different website I can do so but I appreciate you taking the time to assist so I do not make a mistake moving forward.
July 7, 2022 at 6:07 pm
Alternatively you could nest the ifs
if(ar.ardelay > 0, Yes,if(aj.apdelay > 0 Yes, No)) ,
I believe your error message refers to the use of 'then' when you need a comma. I don't know if elseif is valid MySQL syntax so if you replace the 'then' with comma, you may see another error message. Nesting ifs works in t-sql, and maybe in MySQL.
July 7, 2022 at 6:22 pm
The case statement is running now. I appreciate it alot.
July 7, 2022 at 6:27 pm
Thank you!
July 7, 2022 at 7:25 pm
I added to the group by and it is really taking to long it is at 3000 seconds and this is just a select statement. ARH table has 4000 approximate records but APJ has 60000 records. Any thoughts on how to speed this up?
July 7, 2022 at 7:59 pm
Do you need left joins for all the joins?
Can you avoid a group by, by replacing the two left joins with a single left join to a union of the two tables where the delay column > 0. I assume union returns a distinct record set in MySQL. The " is null " syntax might not be right, but I don't have a way to test it. I would test the union separately first to see if it returns fast enough. I put the larger table first in case that helps the distinct.
if(shd.shipment_id is null, 'NO', 'YES') delay
left join ( select shipment_id from custom_cs.apj where ajdelay > 0
union select shipment_id from custom_cs.arh where ardelay > 0
) shd on sa.shipment_id = shd.shipment_id
July 7, 2022 at 8:14 pm
I will give it a try and let you know.
July 8, 2022 at 12:31 pm
I added a distinct but it is still taking so long. Did i put the distinct in the wrong places?
left join ( select distinct(shipment_id) from custom_cs.apj where apdelay > 0
union select distinct(shipment_id) from custom_cs.arh where ardelay > 0
) shd on sa.shipment_id = shd.shipment_id
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply