help with view

RUnning IDS 9.40.FC6 on HPUX 11.11 I create a view with the following select: CREATE VIEW v_cust (div, cust_num, ship_num, cust_name, address1, address2, attn, city, province, postal_code, phone, terr_code, sort_name, cust_type, cust_group, inv_date_last, hold_code, soft_a_i, term_code) AS SELECT csxlocnr.bal_ledger[1,2], customer.cust_num, "" , customer.cust_name, customer.address1, customer.address2, fcccustr.bus_name2, customer.city, customer.province, customer.postal_code, customer.phone, customer.terr_code, customer.sort_name, customer_2.cust_type, customer.cust_group, customer.inv_date_last, customer.hold_code, customer_2.soft_a_i, customer.term_code FROM csxlocnr, customer, customer_2, fcccustr WHERE customer.cust_num=customer_2.cust_num AND csxlocnr.cost_ctr = customer.terr_code AND customer.cust_num = fcccustr.cust_code UNION SELECT csxlocnr.bal_ledger[1,2], cust_shp.cust_num, cust_shp.ship_num, cust_shp.ship_name, cust_shp.address2, cust_shp.address3, cust_shp.address1, cust_shp.city, cust_shp.province, cust_shp.postal_code, cust_shp.phone, cust_shp.terr_code, cust_shp.sort_name, cust_shp_2.cust_type, cust_shp_2.cust_group, customer.inv_date_last, customer.hold_code, cust_shp_2.soft_a_i, customer.term_code FROM csxlocnr, cust_shp, cust_shp_2, customer WHERE cust_shp.cust_num=cust_shp_2.cust_num AND csxlocnr.cost_ctr = cust_shp.terr_code AND cust_shp.ship_num = cust_shp_2.ship_num AND cust_shp.cust_num = customer.cust_num The problem I am having is that I need to have the column in the first half of the union that corresponds to ship_num be null. It will alwas be null for that query. When I query the view for ship_num IS NULL, I don't get any rows returned. How do I get a null value into the view for the first select?

Hello! I think that problem is in the query "AND cust_shp.ship_num = cust_shp_2.ship_num", this is not valid for values NULLs. This sentence only geting values different to null. Saludos! Norberto. ----- Original Message ----- From: "ANTHONY JUDISH" <ajudish@lextron-inc.com> To: <ids@iiug.org> Sent: Wednesday, October 18, 2006 5:11 PM Subject: help with view [7643] > > RUnning IDS 9.40.FC6 on HPUX 11.11 > > I create a view with the following select: > > CREATE VIEW v_cust (div, cust_num, ship_num, cust_name, address1, > address2,> > attn, city, province, postal_code, phone, terr_code, > > sort_name, cust_type, cust_group, inv_date_last, > > hold_code, soft_a_i, term_code) > AS > SELECT csxlocnr.bal_ledger[1,2], customer.cust_num, "" , > customer.cust_name, > > customer.address1, customer.address2, fcccustr.bus_name2, > > customer.city, customer.province, customer.postal_code, > > customer.phone, customer.terr_code, customer.sort_name, > > customer_2.cust_type, customer.cust_group, customer.inv_date_last, > > customer.hold_code, customer_2.soft_a_i, customer.term_code > FROM csxlocnr, customer, customer_2, fcccustr > WHERE customer.cust_num=customer_2.cust_num > > AND csxlocnr.cost_ctr = customer.terr_code > > AND customer.cust_num = fcccustr.cust_code > > UNION > > SELECT csxlocnr.bal_ledger[1,2], cust_shp.cust_num, cust_shp.ship_num, > > cust_shp.ship_name, cust_shp.address2, > > cust_shp.address3, cust_shp.address1, cust_shp.city, > > cust_shp.province, cust_shp.postal_code, cust_shp.phone, > > cust_shp.terr_code, cust_shp.sort_name, cust_shp_2.cust_type, > > cust_shp_2.cust_group, customer.inv_date_last, customer.hold_code, > > cust_shp_2.soft_a_i, customer.term_code > FROM csxlocnr, cust_shp, cust_shp_2, customer > WHERE cust_shp.cust_num=cust_shp_2.cust_num > > AND csxlocnr.cost_ctr = cust_shp.terr_code > > AND cust_shp.ship_num = cust_shp_2.ship_num > > AND cust_shp.cust_num = customer.cust_num > > The problem I am having is that I need to have the column in the first > half of > the union that corresponds to ship_num be null. It will alwas be null > for that > query. When I query the view for ship_num IS NULL, I don't get any rows > returned. How do I get a null value into the view for the first select? > > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum.

Try adding the clause 'OUTER' in the second SELECT: ...cust_shp_2.soft_a_i, customer.term_code FROM csxlocnr, cust_shp, OUTER cust_shp_2, customer WHERE cust_shp.cust_num=cust_shp_2.cust_num ... -----Mensaje original----- De: Norberto Valverde LLanos [mailto:nvalverde@comsa.com.pe] Enviado el: Miércoles, 18 de Octubre de 2006 05:47 p.m. Para: ids@iiug.org Asunto: Re: help with view [7644] Hello! I think that problem is in the query "AND cust_shp.ship_num = cust_shp_2.ship_num", this is not valid for values NULLs. This sentence only geting values different to null. Saludos! Norberto. ----- Original Message ----- From: "ANTHONY JUDISH" <ajudish@lextron-inc.com> To: <ids@iiug.org> Sent: Wednesday, October 18, 2006 5:11 PM Subject: help with view [7643] > > RUnning IDS 9.40.FC6 on HPUX 11.11 > > I create a view with the following select: > > CREATE VIEW v_cust (div, cust_num, ship_num, cust_name, address1, > address2,> > attn, city, province, postal_code, phone, terr_code, > > sort_name, cust_type, cust_group, inv_date_last, > > hold_code, soft_a_i, term_code) > AS > SELECT csxlocnr.bal_ledger[1,2], customer.cust_num, "" , > customer.cust_name, > > customer.address1, customer.address2, fcccustr.bus_name2, > > customer.city, customer.province, customer.postal_code, > > customer.phone, customer.terr_code, customer.sort_name, > > customer_2.cust_type, customer.cust_group, customer.inv_date_last, > > customer.hold_code, customer_2.soft_a_i, customer.term_code > FROM csxlocnr, customer, customer_2, fcccustr > WHERE customer.cust_num=customer_2.cust_num > > AND csxlocnr.cost_ctr = customer.terr_code > > AND customer.cust_num = fcccustr.cust_code > > UNION > > SELECT csxlocnr.bal_ledger[1,2], cust_shp.cust_num, cust_shp.ship_num, > > cust_shp.ship_name, cust_shp.address2, > > cust_shp.address3, cust_shp.address1, cust_shp.city, > > cust_shp.province, cust_shp.postal_code, cust_shp.phone, > > cust_shp.terr_code, cust_shp.sort_name, cust_shp_2.cust_type, > > cust_shp_2.cust_group, customer.inv_date_last, customer.hold_code, > > cust_shp_2.soft_a_i, customer.term_code > FROM csxlocnr, cust_shp, cust_shp_2, customer > WHERE cust_shp.cust_num=cust_shp_2.cust_num > > AND csxlocnr.cost_ctr = cust_shp.terr_code > > AND cust_shp.ship_num = cust_shp_2.ship_num > > AND cust_shp.cust_num = customer.cust_num > > The problem I am having is that I need to have the column in the first > half of > the union that corresponds to ship_num be null. It will alwas be null > for that > query. When I query the view for ship_num IS NULL, I don't get any rows > returned. How do I get a null value into the view for the first select? > > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

The empty string you are returning as ship_num in the first half of the union is not the same as NULL... If you were to: select * from v_cust where length(ship_num) = 0; as opposed to IS NULL you would return values... Create a procedure: p_null() ...; create procedure p_null() returning char(1); define v_return char(1); let v_return = null; return v_return; end procedure; Change the view to use p_null() in place of the empty string. ... AS > SELECT csxlocnr.bal_ledger[1,2], customer.cust_num, p_null() , > customer.cust_name, > ... Selecting where ship_num is null will now work as you expected. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Norberto Valverde LLanos Sent: Thursday, 19 October 2006 08:47 To: ids@iiug.org Subject: Re: help with view [7644] Hello! I think that problem is in the query "AND cust_shp.ship_num = cust_shp_2.ship_num", this is not valid for values NULLs. This sentence only geting values different to null. Saludos! Norberto. ----- Original Message ----- From: "ANTHONY JUDISH" <ajudish@lextron-inc.com> To: <ids@iiug.org> Sent: Wednesday, October 18, 2006 5:11 PM Subject: help with view [7643] > > RUnning IDS 9.40.FC6 on HPUX 11.11 > > I create a view with the following select: > > CREATE VIEW v_cust (div, cust_num, ship_num, cust_name, address1, > address2,> > attn, city, province, postal_code, phone, terr_code, > > sort_name, cust_type, cust_group, inv_date_last, > > hold_code, soft_a_i, term_code) > AS > SELECT csxlocnr.bal_ledger[1,2], customer.cust_num, "" , > customer.cust_name, > > customer.address1, customer.address2, fcccustr.bus_name2, > > customer.city, customer.province, customer.postal_code, > > customer.phone, customer.terr_code, customer.sort_name, > > customer_2.cust_type, customer.cust_group, customer.inv_date_last, > > customer.hold_code, customer_2.soft_a_i, customer.term_code > FROM csxlocnr, customer, customer_2, fcccustr > WHERE customer.cust_num=customer_2.cust_num > > AND csxlocnr.cost_ctr = customer.terr_code > > AND customer.cust_num = fcccustr.cust_code > > UNION > > SELECT csxlocnr.bal_ledger[1,2], cust_shp.cust_num, cust_shp.ship_num, > > cust_shp.ship_name, cust_shp.address2, > > cust_shp.address3, cust_shp.address1, cust_shp.city, > > cust_shp.province, cust_shp.postal_code, cust_shp.phone, > > cust_shp.terr_code, cust_shp.sort_name, cust_shp_2.cust_type, > > cust_shp_2.cust_group, customer.inv_date_last, customer.hold_code, > > cust_shp_2.soft_a_i, customer.term_code > FROM csxlocnr, cust_shp, cust_shp_2, customer > WHERE cust_shp.cust_num=cust_shp_2.cust_num > > AND csxlocnr.cost_ctr = cust_shp.terr_code > > AND cust_shp.ship_num = cust_shp_2.ship_num > > AND cust_shp.cust_num = customer.cust_num > > The problem I am having is that I need to have the column in the first > half of > the union that corresponds to ship_num be null. It will alwas be null > for that > query. When I query the view for ship_num IS NULL, I don't get any rows > returned. How do I get a null value into the view for the first select? > > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************* Everything about this email and its attachments, including potentially confidential components, is only for the eyes and ears of the persons to whom it has been addressed (i.e. the persons whose names appear in the "To" section of the email; however, those listed in sections "CC" and "BCC" may also consider themselves included in the group whose eyes and ears this email is for.). In the event that no physical, emotional or spiritual resemblance can be made between you and the intended recipients, you have been mistakenly or deliberately omitted from the email, someone has given it to you, or you have nicked it. If you're not supposed to have access to this email, please delete it, destroy it and deprive others from it. Oh, and let us know when you are done. *******************************************************************