How to build WHERE condition based on a calculated field?

Question

I have this query that pulls data from the inventory bin location and a user defined field that shows the max number of pallets per warehouse.

My data:

   Bin Code     Max Pallets     Total Pallets    Available
   000-AA-001   34              30               4
   000-BB-001   24              18               6
   000-CC-001   12              14               -2

My query:

SELECT T0."BinCode", T0."U_TRX_MaxPallets" AS "Max Pallets",
SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Total Pallets", T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Available"
FROM OBIN T0  INNER JOIN OIBQ T1 ON T0."AbsEntry" = T1."BinAbs"  
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE T1."OnHandQty"  <> 0 AND  T1."WhsCode" ='000' AND SUBSTRING(T0."BinCode",5,1) <> 'R' AND SUBSTRING(T0."BinCode",5,1) <> 'P' AND SUBSTRING(T0."BinCode",1,11) <> '000-OFFICE' AND SUBSTRING(T0."BinCode",1,8) <> '000-OTBD' AND "Available" > 0
GROUP BY T0."BinCode", T0."U_TRX_MaxPallets"
ORDER BY T0."BinCode"

The end result is I need all bin codes where the available > 0.

I added a column called Total, and removed all references to Bin.

It gives me an error message when I remove the T0."U_TRX_MaxPallets" from the group by clause.

SELECT 'Total' AS "Total", T0."U_TRX_MaxPallets" AS "Max Pallets",
SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Total Pallets", T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Available"
FROM OBIN T0  INNER JOIN OIBQ T1 ON T0."AbsEntry" = T1."BinAbs"  
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE T1."OnHandQty"  <> 0 AND  T1."WhsCode" ='000' AND SUBSTRING(T0."BinCode",5,1) <> 'R' AND SUBSTRING(T0."BinCode",5,1) <> 'P' AND SUBSTRING(T0."BinCode",1,11) <> '000-OFFICE' AND SUBSTRING(T0."BinCode",1,8) <> '000-OTBD'
GROUP BY 'Total', T0."U_TRX_MaxPallets"

So people take the time to edit what I said since I said in wrong, but can't provide an answer? Thanks anyway for the help or just the criticism of my message... — Brian Branco
– Brian Branco, Commented May 15, 2024 at 12:32
People are taking the time to improve your question, so that chances increase to actually get it answered by people who know. This is not criticism. This is part of a constructive and healthy culture. — Mathias Kemeter
– Mathias Kemeter, Commented May 16, 2024 at 10:55

Mathias Kemeter · Accepted Answer · 2024-05-16 10:51:44Z

1

If you simply would like to filter for available > 0, you can do so by using a nested select statement or the HAVING clause.

Option 1:

SELECT *
FROM
( 
    <INSERT YOUR QUERY HERE>
)
WHERE "Available" > 0

Option 2:

<INSERT YOUR QUERY HERE>
HAVING T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) > 0

answered May 16, 2024 at 10:51

Mathias Kemeter

1,1915 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to build WHERE condition based on a calculated field?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related