I'm trying to develop a search for back orders and trying to display the warehouse which has enough quantity to cover this order. When I introduce a location level DECODE to a saved search, it outputs a bunch of rows but from my commands, I would think they would all have the same result since I'm hard coding each location into the DECODE, so there's no room for it to think I'm talking about another location. But it still shows wrong data. So then I can't use the MAXIMUM grouping on the saved search because it's got incorrect data in each of the multi rows. Here's a short video explaining what I was trying to do and how it messed up Here's the formula I tried to add in the video:

CASE WHEN {item.quantityavailable} >= {quantity}-nvl({quantitycommitted},0)-nvl({quantityshiprecv},0) THEN CASE WHEN nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) THEN 'Atlanta' ELSE CASE WHEN nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) THEN 'Houston AFM' ELSE CASE WHEN nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) THEN 'Youngstown' ELSE CASE WHEN nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) THEN 'Jackson' ELSE CASE WHEN nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Atlanta', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Youngstown', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Houston AFM', {item.locationquantityavailable}),0) AND nvl(DECODE({item.inventorylocation}, 'Charlotte SG', {item.locationquantityavailable}),0) > nvl(DECODE({item.inventorylocation}, 'Jackson', {item.locationquantityavailable}),0) THEN 'Charlotte SG' ELSE 'Not enough stock' END END END END END ELSE 'Not enough stock' END

Any ideas? I know it can probably be done using SQL but I just don't know how to do that yet, I was hoping there may be something I'm overlooking.