The reason i need to return "no asn" in the results is because they export these records out and send out to users that need the data and they need...

Ive made the additional changes and suggestions and provided the results to the team to validate if it will work for what they are needing..

So the shipment table is...

well i made the suggested changes, but the query ran for 701.57(11 minutes) seconds before i cancelled it..

Before the changes the query would only take about 15 to 20...

Yea the names of the tables and columns have been changed before posting as this is for my work.

Ill have to check everything on monday when i get back to...

I have worked with inner join before..

the shipment table can be joined on order_no with the h.order_no field.

Ok, sorry had to run to meeting and was not able to post everything originally

Here is the entire query

SELECT DISTINCT h.ORDER_NO, dps.DEPT_NAME, im.CLASS, c.CLASS_NAME, s.SUP_NAME, d.EIDE AS EISD,

(SELECT

...