Page 1 of 1
Form:Query UNION
Posted: Wed Mar 11, 2015 1:16 am
by 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.
Re: Form:Query UNION
Posted: Wed Mar 11, 2015 4:02 pm
by 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.
Re: Form:Query UNION
Posted: Fri Mar 13, 2015 6:11 am
by admin
Rui,
You might try turning your union query into a view.
Steven
Re: Form:Query UNION
Posted: Fri Mar 13, 2015 1:16 pm
by ruiascensao
Hi Steven,
With the VIEW it works fine.
Thank you!
Re: Form:Query UNION
Posted: Mon Mar 16, 2015 11:04 pm
by admin
.