Mixing temp table and real tables in query

Question

Why does one of these queries work and the other does not? I wrote the working query, then made the table temporary and it has to be rewritten to have the temp table on the outside.

This is the data in the tables:

tmpTASItemQtyInventDim:

|ItemId | InventDimId  | InventQty |
|-------|--------------|-----------|
|73016  | Dim 11542913 | 0         |

SalesLine:

|Sales Id    |Line No | ItemId | InventDimId  | SalesQty  |
|------------|--------|--------|--------------|-----------|
|SO120036796 |       1| 73016  | Dim 11542913 | 2         |

Does Not work:

select firstonly tmpTASItemQtyInventDim
    join itemId, InventDimId, sum(salesQty) from salesLine
    group by itemId, InventDimId
    where salesLine.SalesId == 'SO120036796'  &&
          tmpTASItemQtyInventDim.ItemId == salesLine.ItemId     &&
          tmpTASItemQtyInventDim.InventDimId    == salesLine.InventDimId    &&
          tmpTASItemQtyInventDim.InventQty      < salesLine.SalesQty;

if (tmpTASItemQtyInventDim.ItemId)
    info("Insufficient Qty");
else
    info("Good qty");

Works:

select firstonly itemId, InventDimId, sum(salesQty) from salesLine
    group by itemId, InventDimId
    where salesLine.SalesId     == 'SO120036796'
    exists join tmpTASItemQtyInventDim
        where tmpTASItemQtyInventDim.ItemId == salesLine.ItemId     &&
              tmpTASItemQtyInventDim.InventDimId    == salesLine.InventDimId    &&
              tmpTASItemQtyInventDim.InventQty      < salesLine.SalesQty;

if (salesLine.ItemId)
    info("Insufficient Qty");
else
    info("Good Qty");

Both should output Insufficient Qty, but the first says Good Qty.

David Lawson · Accepted Answer · 2012-11-14T09:13:49.843

1

From memory I think you need to group by on the first table as well see http://axatluegisdorf.blogspot.co.uk/2010/07/select-group-by-and-join-order-by.html

select firstonly ITEMID,InventDimId 
  FROM  tmpTASItemQtyInventDim
  GROUP BY ITEMID,InventDimId
join itemId, InventDimId, sum(salesQty) 
from salesLine
 group by itemId, InventDimId
where salesLine.SalesId == 'SO120036796'  &&
      tmpTASItemQtyInventDim.ItemId == salesLine.ItemId     &&
      tmpTASItemQtyInventDim.InventDimId    == salesLine.InventDimId    &&
      tmpTASItemQtyInventDim.InventQty      < salesLine.SalesQty;

if (tmpTASItemQtyInventDim.ItemId)
   info("Insufficient Qty");
else
   info("Good qty");

edited Nov 14 '12 at 09:13

answered Nov 14 '12 at 08:23

David Lawson

796
3
10

Brilliant! Doesn't make complete sense to why I need to do that, but it works. – Alex Kwitny Nov 14 '12 at 17:17
I think its because the query without the group by is equivalent to `code`Select SalesLine.ItemId,SalesLine.InventDimId from tmpTASItemQtyInventDim Inner join salesLine ON tmpTASItemQtyInventDim.ItemId == salesLine.ItemId AND tmpTASItemQtyInventDim.InventDimId == salesLine.InventDimId....`code` If "your does not work was" to test SalesLine.ItemId It think is would produce the correct result also – David Lawson Nov 15 '12 at 08:14

score 1 · Answer 2 · answered Nov 14 '12 at 09:57

1

Mixing temp tables and real tables is like biking a cobbled road. You do not always get what you want think you get, and the performance sometimes sucks.

In AX 2012 you could use TempDB which work more predictable.

Mixing temp table and real tables in query

2 Answers2