strange query

[Ruslan Zasukhin]

On 7/1/04 10:26 PM, "Tim Davis" <timdavis at amug.org> wrote:

Hi Tim,

> I have two queries below. The first one returns 4817 records the second
> return 830. The second should be correct. The only difference is a sum
> field. Why is the first returning so many "duplicate" records? Does
> Group By only work when there is a field function?

1) both your query are wrong because you have break SQL standard rule:

    if you use GROUP BY
    then in SELECT can be ONLY AND ONLY
    - fields listed in GROUP BY
    - aggregative functions on that fields
    - mix of 2 above.

You do e.g. SELECT Inventory.tCompany, but this field is not in the GROUP
BY. 

Valentina 2.0 will simply return error on such query.

2) in first case, when you use GROUP BY but not use aggregative functions,
you can use workaround:

    SELECT DISTINCT f1, f2, ...
    FROM T
    WHERE ...
        
And no GROUP BY at all.


> Thanks,
> Tim
> 
> 
> 
> Cost Report (Current Month):
> SELECT
> Inventory.tCompany,
> Inventory.tLocation,
> Inventory.tMaterial,
> Inventory.tPlacement,
> Inventory.tMfr,
> Inventory.tSubCategory,
> Inventory.tDescription,
> Inventory.tSpecials,
> Inventory.cHand,
> Inventory.cBinSort,
> Inventory.cBinSort1,
> Inventory.cBinSort2,
> Inventory.cID
> FROM
> Inventory,
> InventoryLog
> WHERE
> Inventory.cID=InventoryLog.cID
> and Inventory.tStockStatus<>'Not Stock'
> and Inventory.tLocation='LV'
> GROUP BY
> Inventory.cID
> ORDER BY
> Inventory.tCompany,
> Inventory.tLocation,
> Inventory.tMaterial,
> Inventory.cBinSort,
> Inventory.cBinSort1,
> Inventory.cBinSort2,
> Inventory.tPlacement,
> Inventory.tMfr
> 
> Cost Report (Current Month):
> SELECT
> Inventory.tCompany,
> Inventory.tLocation,
> Inventory.tMaterial,
> Inventory.tPlacement,
> Inventory.tMfr,
> Inventory.tSubCategory,
> Inventory.tDescription,
> Inventory.tSpecials,
> Inventory.cHand,
> Sum(InventoryLog.cQtyDiffStock),
> Inventory.cBinSort,
> Inventory.cBinSort1,
> Inventory.cBinSort2,
> Inventory.cID
> FROM
> Inventory,
> InventoryLog
> WHERE
> Inventory.cID=InventoryLog.cID
> and Inventory.tStockStatus<>'Not Stock'
> and Inventory.tLocation='LV'
> GROUP BY
> Inventory.cID
> ORDER BY
> Inventory.tCompany,
> Inventory.tLocation,
> Inventory.tMaterial,
> Inventory.cBinSort,
> Inventory.cBinSort1,
> Inventory.cBinSort2,
> Inventory.tPlacement,
> Inventory.tMfr
> 

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------