#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    (Sub?)Query difficulty


    I've got a bit of an issue creating a query that has any elegance at all (i.e. done right by someone who actually knows this stuff) due simply to my knowledge limitations.

    In a single query I need to accomplish the following:

    Table_A contains Item_NO and UPC_NO
    Table_B contains Item_NO and Warehouse_ID and Quantity_Available and Quantity_Allocated

    Result set needs to be:

    UPC_NO, InStock_Flag, Quantity

    The problem is that the 'InStock_Flag' is a new requirement (I have a query already that simply joins the tables on Item_NO and groups by UPC_NO).

    The Flag is simply 'T' or 'F' depending upon whether the quantity value for a particular UPC_NO is positive or negative. I've accomplished that as well utilizing a CASE statement. However, what I neglected to take into account is that the UPC_NO shows up multiple times in Table_B (once for each warehouse) and as my CASE statement is looping through the records the flag is getting set to 'T' for some records and 'F' for others, thus my grouping will no longer work as those records become unique.

    The logic needs to be as follows for the query, but I'm not sure how to structure it:

    First join tables, select all records and group Quantity_Available and Quantity_Allocated by UPC_NO where Warehouse_ID <> 'x'

    Then take that result set and generate final result as UPC_NO, InStock_Flag, Quantity (Quantity_Available - Quantity_Allocated)
    Where Quantity_Available - Quantity_Allocated > 0 then InStock_Flag = 'T'
    Where Quantity_Available - Quantity_Allocated < 1 then InStock_Flag ='F' and Quantity = 0

    Again, I have a query that does all of this except for the first part...and not sure if this should be done as a subquery (which frankly I have no experience with) or other.

    Any help much appreciated.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    try:

    select upc_no,
    case when sum(Quantity_Available - Quantity_Allocated) > 0 then 'T' else 'F' end,
    case when sum(Quantity_Available - Quantity_Allocated) > 0 then quantity else 0 end
    from table_a a
    join table_b b
    on a.item_no = b.item_no
    where where Warehouse_ID <> 'x'
    group by upc_no

IMN logo majestic logo threadwatch logo seochat tools logo