Select single row
Hi,
I have a sql server table which has fields like this
productNO, Description, loc_quantity. one product can have multiple locations . Location has multiple columns for different location codes. Like Location_qty_11, Location_qty_40 like that. The table gets populated from other tables which puts null in the loc_qty column if it does not have any product in that location. How can I select from that table which will have only one productNO, and description then qty for all the locations. If it does n't have a qty it will put 0. When I select it always returns multiple rows for the productNO and description column. Please help.
Thanks in advance.
Software/Hardware used:
Software/Hardware used:
ASKED:
June 16, 2006 1:03 PM
UPDATED:
June 20, 2006 1:15 PM
Answer Wiki:
To select a single row with all the totals, possibly zero, use this DB2 command:
SELECT COALESCE(SUM(position1),0) position1,
COALESCE(SUM(position2),0) position2
FROM table
WHERE criteria=:value;
Using Oracle, do it this way:
SELECT NVL(SUM(position1),0) AS position1,
NVL(SUM(position2),0) AS position2
FROM table
WHERE critera=:value;
---
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)
To see all answers submitted to the Answer Wiki: View Answer History.
Seems like you already have the multple quantity columns. Just use a SUM() on each of the Quantity columns, and do a GROUP BY on the product code and description columns. SUM will ignore all the null values so that shouldn’t be an issue.
If you are using MSSQL then I would do it this way:
select ProcuctNo,Description,Sum(isnull(loc_qty_x)) as ‘loc_qty_x’,
Sum(isnull(loc_qty_y)) as ‘loc_qty_y’,Sum(isnull(loc_qty_z)) as ‘loc_qty_z’
group by ProductNo,Description where productNo = ‘blah’
Really need some clarification from the gullu1… A table has multiple quantity@location columns and a table has only location-quantity column pair will have very different solutions.
Also in a multiple quantity@location scenario, the design (or data load procedure) is probably flawed to allow multiple rows with the same product number, which should be the primary key.
Hi everyone,
Thanks for all these replies. Actually my problem was a tricky one . I figured that out. May be I did not give more clarification. The problem was , I got an user request to get data from 2 tables then create another table out of it, then he can show it in the web site.
He wants columns like productNo, description, division,transit, some other columns.., then for the quantity and date columns he wants to select by the location. location is one of the columns in the one of the 2 tables. He wants if the location is 01 then the quantity
will for that location will be put in a new column like qty_01, same for the date like date_01. The table has 6 different locations. And productNO. can have multiple locations. So when I did case like
Case table2.location when 01 then — else 0. It did not work. It will put multiple rows for the ProductNo column which he doesn’t want. So I created 3 stored procedures. One will insert into the distinct productNo, and all other columns into the new table except the qty_01,date_01 columns.Then I declare a cursor and fetch the productNo and passing that productNo I call another procedure which will get the location for that productNo then passing both productNo and location i call the third procedure which will update the qty_xx, and date_xx columns and put the values in there. This one works fine. Thanks..