SQL Query -- IF X is shared between two strings in a primary composite key?

Question

Not sure how to explain this, as I have only basic SQL knowledge. I need help with some logic to signal a user when a unique string is referenced more than once in a composite primary key.

A table I use contains a composite primary key which ties a unique "ItemCode" to a repeating "WarehouseCode". The "QuantityOnHand" column lists how much we have of an item in each warehouse:

     _____PK_______
    |              |
ItemCode  |  WarehouseCode  |  QuantityOnHand
---------------------------------------------
   001             A                 100
   001             B                 500
   002             A                 600
   003             B                 250

etc.

How do I "signal" (create a boolean value?) when ItemCode 001 is in two separate warehouses?

The end result is a crystal report which somehow alerts the user when an item's total quantity is split between two warehouses.

Thanks

Answer 1

Utilise GROUP BY and HAVING. For example this query will return a list of ItemCodes and a count of how many warehouses they are in where that count is greater than 1.

SELECT ItemCode, COUNT(*)
FROM table
GROUP BY ItemCode
HAVING COUNT(*) > 1

Answer 2

In crystal you would probably want to create a group on ItemCode and then you can evaluate: DistinctCount({table.WarehouseCode}) but I would suggest you use SQL group by ItemCode having count(distinct WarehouseCode) > 1

Answer 3

It is much easier to do in Crystal than in pure SQL

Using a Crystal function:

If {table.ItemCode} = Previous({table.ItemCode})
    Then //we have a duplicate ItemCode, do something to flag it

The above assumes that the details have these values and that they were not grouped. Although, even if they were grouped something like this should do it

If {table.ItemCode} = Previous({table.ItemCode})
    and {table.WarehouseCode} <> Previous({table.WarehouseCode})
    Then //we have a duplicate ItemCode