Left Join in Extended Report Query

Is it possible to do a LEFT JOIN in Extended Report Query?

If not, how can I get the Cust_Address Ship_Via into VMSHPFRM.QRP?
If Ship To Addr No is NULL, then no query information is returned.

[Updated on 4/2/2009 4:26 PM]

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

unknown

Your second statement confused me a little; "If Ship To Addr No(do you mean SHIPTO_ID?) is NULL, then no query information is returned."(I would assume you only want the information to be pulled from the CUST_ADDRESS table if it ties to the Customer order?)

If the problem is you don't use the SHIPTO_ID field, but use CUST_ADDRESS.ADDR_NO then you can run a script to copy all ADDR_NO to SHIPTO_ID FIELD.

unknown

This statement should work in Oracle. I don't know if it works in SQLBase or SQL Server.

SELECT a.ship_via
FROM customer_order o , cust_address a
WHERE o.customer_id = a.customer_id (+)
AND o.ship_to_addr_no = a.addr_no (+)
AND o.id = :CO_ID

unknown

SELECT O.order_type , S.WEIGH_BILL_NUMBER , S.mi_warehouse_id , W.ADDR_1 , W.ADDR_2 , W.CITY , W.STATE , W.ZIPCODE , W.MI_PHONE , W.MI_FAX , O.mi_drop_ship_cust_po , O.CONTACT_FIRST_NAME , O.CONTACT_LAST_NAME , O.CONTACT_PHONE , O.CONTACT_FAX , O.CONTACT_EMAIL , O.SHIP_TO_ADDR_NO , O.mi_salesrep_id , R.NAME , U.MI_USER_FULLNAME , U.mi_salesrep_phone , U.mi_salesrep_email , C.SHIP_VIA , CA.SHIP_VIA
FROM CUSTOMER_ORDER O , SHIPPER S , WAREHOUSE W , SALES_REP R , MI_USER_WAREHOUSE U , CUSTOMER C , CUST_ADDRESS CA
WHERE O.ID = :CO_ID AND S.PACKLIST_ID = :CO_PACKLIST_ID AND W.ID = S.mi_warehouse_id AND O.SALESREP_ID = R.ID AND isnull(O.mi_salesrep_id, o.salesrep_id) = U.mi_salesrep_id AND C.ID = :CO_CUSTOMER_ID AND CA.CUSTOMER_ID = :CO_CUSTOMER_ID AND CA.ADDR_NO = S.SHIP_TO_ADDR_NO

This works fine when there is an ADDR_NO, but if NULL it doesn't return any results. The SHIPTO_ID is not available as in Input Item in the Packlist Form.

I am attempting to compare the Customer Ship Via or the ShipTo Ship Via (if there is a shipto) to the Order Ship Via and if the same, print the corresponding Customer User 1 or the ShipTo User 1 field. If not the same, print the Order User 1 field. The Packlist form only has the Order Ship Via available.

[Updated on 4/3/2009 10:24 AM]

unknown

Sounds like you need an outer join for the cust_address table - but you know this already. The syntax depends on your database. I am only familiar with the Oracle syntax and there you would use something like

...
AND CA.CUSTOMER_ID = :CO_CUSTOMER_ID (+)
AND CA.ADDR_NO = S.SHIP_TO_ADDR_NO (+)

ANSI SQL would be something like:

SELECT c.id, c.ship_via, a.ship_via
FROM (customer_order o JOIN customer c ON o.customer_id = c.id)
LEFT OUTER JOIN cust_address a ON (o.customer_id = a.customer_id
AND o.ship_to_addr_no = a.addr_no)
WHERE o.id = :CO_ID

unknown

Yes, I do need a LEFT JOIN, but how is that expressed in Extended Report Query? I am on SQL 2000.

com_2010feb_proactive.pdf

unknown

Try this - but I don't know if it will work. Change the last two conditions

CA.CUSTOMER_ID = :CO_CUSTOMER_ID AND CA.ADDR_NO = S.SHIP_TO_ADDR_NO

into this:

C.CUSTOMER_ID *= CA.CUSTOMER_ID AND S.SHIP_TO_ADDR_NO *= CA.ADDR_NO

unknown

Thank you very much! I changed the C.CUSTOMER_ID to C.ID and it worked like a champ. So the *= is equivalent to Left Join?

unknown

This is the info from Microsoft

Join in where clause (discontinued in SQL 2005):
msdn.microsoft.com/.../aa213235(SQL.80).aspx

Joins in general:
msdn.microsoft.com/.../aa213233(SQL.80).aspx

Copyright © 2025 Infor. All rights reserved.