Skip to Content
0
Former Member
Jun 08, 2009 at 02:48 PM

Prices Query

43 Views

Hi Experts,

I have a question about a 2 queries I need to combine. First here is some background....

My client wants to see all the Price Lists Data in the Sales Order per Item Line. I created UDF's for this and linked the following query:

 SELECT T0.[Price] FROM ITM1 T0 WHERE T0.[PriceList] = 1 and t0.itemcode = $[$38.1.0] 

This works fine.

Because my client wants to update Prices in Bulk, we created another query that will populate the same UDF in the Sales Order with a Calculation (this does not update the Price List, but show them what they can change the Unit Price to. This is the query:

 select  ((grp.u_prcnt01 * whs.avgprice)+ whs.avgprice) as 'Price1'
from oitm itm left outer join oitb grp on itm.itmsgrpcod = grp.itmsgrpcod 
              left outer join oitw whs on itm.itemcode   = whs.itemcode 
where whs.whscode = '01' and itm.itemcode = $[$38.1.0] 

This works fine.

My problem comes to join these 2 queries....

When the Item in the Sales Order line is from Item Goup 1, it needs to bring back the 1st query result.

If the Item is from Item Group 2, it needs to bring back the 2nd query result.

Any help would be greatly appreciated.

Thanks.

Marli

Edited by: Marli Schutte on Jun 8, 2009 4:49 PM