Select single row

5 pts.
Tags:
SQL
SQL Server
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.
ASKED: June 16, 2006  1:03 PM
UPDATED: June 20, 2006  1:15 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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)

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gematrian
    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.
    0 pointsBadges:
    report
  • MooseDrool
    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'
    0 pointsBadges:
    report
  • Invisflare
    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.
    0 pointsBadges:
    report
  • Gullu1
    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..
    5 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following