Form:Query UNION

[ruiascensao]

Hi,

I have a query UNION in a form

Code: Select all

select product_id, ....
WHERE inventory_code='3' AND quantity >'1'
union
select product_id, ....
WHERE inventory_code='2'

The code works fine but when I use a lookup object that will search this form I can browse the form but when selecting a record the lookup object does not get any information.

Is there a problem using UNION on Forms?

Thank you.

[ruiascensao]

Hi,

To be more specific my query looks like:

Code: Select all

select * from product
INNER JOIN (...)
WHERE bom_prodid='#job_prodid#' AND product_category='#partscat#' AND inv_codecli_codecliid='#job_codecli#' AND inv_codecli_active='1' AND inventory_code='STORE3' AND zone_quantity >'1'
UNION
select product_id,IFNULL(product_code,''),IFNULL(inventory_name,''),IFNULL(product_category,''),IFNULL(product_design,''),IFNULL(product_manuf,''),IFNULL(zone_code,''), IFNULL(zone_id,'')  from product
INNER JOIN (...)
WHERE bom_prodid='#job_prodid#' AND product_category='#partscat#' AND inv_codecli_codecliid='#job_codecli#' AND inv_codecli_active='1' AND inventory_code='STORE2' AND 
    product_id NOT IN (SELECT product_id FROM product
    INNER JOIN (...)
    WHERE bom_prodid='#job_prodid#' AND product_category='#partscat#' AND inv_codecli_codecliid='#job_codecli#' AND inv_codecli_active='1' AND inventory_code='STORE3' AND zone_quantity >'1');

STORE2 and 3 have products. This join is to avoid duplicates giving priority to STORE3 to be used first and STORE 2 to be used if product does not exist in store 3. Even if product quantity is '0' it should display product from STORE 2.

The browse window from the lookup object works fine but when selecting the record it does not get the product_id/product_code/Product_design.
Any advise, please?

Thank you.

[admin]

Rui,

You might try turning your union query into a view.

Steven

[ruiascensao]

Hi Steven,

With the VIEW it works fine.

Thank you!

[admin]

.