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 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply