cancel
Showing results for 
Search instead for 
Did you mean: 

Inventory query

Former Member
0 Kudos

I am trying to get a query that contains the following fields from each table.

OITM

itemcode

U_EXT_DESC

OITW

whscode

onhand

iscommited

OSRN

U_stated_cost

location

U_suggested_price

I have multiple warehouses, so I only want items that have qty 1 or more on hand value from MAIN or TECH warehouse to appear in this list

If the onhand value is 0, then I do not want it to appear on this report.

If the location and stated cost and suggested prices are duplicate lines, I want to consolidate to just be ONE row.

Can someone please help me write this query??

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi!

Hope this could help.

SELECT

T0.[ItemCode], T0.U_EXT_DESC, T0.[ItemName], T1.[WhsCode], SUM(T1.[OnHand]) 'On Hand', SUM(T1.[IsCommited]) Comitted, T2.[Location]

FROM OITM T0 

left JOIN OITW T1 ON T0.ItemCode = T1.ItemCode

left JOIN OSRN T2 ON T0.ItemCode = T2.ItemCode

WHERE

T1.[OnHand] > 0 and 

T1.[WhsCode]  IN ('MAIN','TECH')

GROUP BY T0.[ItemCode], T0.[ItemName], T2.[Location], T1.[WhsCode], T2.[U_STATED_COST], T2.[U_SUGGESTED_PRICE]

Former Member
0 Kudos

Hi Mark,

This query will not run.

Thank you,

Amber

Former Member
0 Kudos

Hi Amber!


Try this one. I just forgot to include "T0.U_EXT_DESC" in the 'group by' clause. You can still use the query above but make sure to delete T0.U_EXT_DESC on the 'select' clause


SELECT

T0.[ItemCode], T0.U_EXT_DESC, T0.[ItemName], T1.[WhsCode], SUM(T1.[OnHand]) 'On Hand', SUM(T1.[IsCommited]) Comitted, T2.[Location]

FROM OITM T0

left JOIN OITW T1 ON T0.ItemCode = T1.ItemCode

left JOIN OSRN T2 ON T0.ItemCode = T2.ItemCode

WHERE

T1.[OnHand] > 0 and

T1.[WhsCode]  IN ('MAIN','TECH')

GROUP BY T0.[ItemCode], T0.U_EXT_DESC, T0.[ItemName], T2.[Location], T1.[WhsCode], T2.[U_STATED_COST], T2.[U_SUGGESTED_PRICE]

If it is still error, please write the error message.

Thanks!

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Add your UDF in below query:

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[IsCommited], T2.[Location] FROM OITM T0  left JOIN OITW T1 ON T0.ItemCode = T1.ItemCode left JOIN OSRN T2 ON T0.ItemCode = T2.ItemCode WHERE T1.[OnHand] >0 and  T1.[WhsCode]   IN ('01','02') GROUP BY T0.[ItemCode], T0.[ItemName], T1.[OnHand], T1.[IsCommited], T2.[Location], T1.[WhsCode]

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Thank you Nagarajan,
However this query will not run.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Change ware house code 01  and  02 to your ware house code and then try.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Below is the query as I am trying to run it. I have added my UDFs from the OSRN table, which was T2. I have them in the query exactly as labeled and shown in the system information, however it does not return them for me when I execute. I have added as an attachment because I could not paste into this box for some reason.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,


Try this :

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[IsCommited], T2.[Location],T2.[U_STATED_COST], T2.[U_SUGGESTED_PRICE] FROM OITM T0  left JOIN OITW T1 ON T0.ItemCode = T1.ItemCode left JOIN OSRN T2 ON T0.ItemCode = T2.ItemCode WHERE T1.[OnHand] >0 and  T1.[WhsCode]   IN ('OPEN') GROUP BY T0.[ItemCode], T0.[ItemName], T1.[OnHand], T1.[IsCommited], T2.[Location], T1.[WhsCode],T2.[U_STATED_COST], T2.[U_SUGGESTED_PRICE]

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan,

This query will not run.

Thank you,

Amber

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Any error message? If possible post screen shot of query generator with query result.

Thanks & Regards,

Nagarajan