Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2012
    Posts
    23

    Unanswered: Qry returning multiple wrong values

    Hello,
    I've got this sql statement that keeps returning the wrong data.
    (it's related to a previous post, but is different)

    Code:
    SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY  
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.ID 
            INNER JOIN ORDERS O ON C.ID = O.ID 
            INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
               U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    GROUP BY C.NAME, OL.PART_ID, SL.SHIPPED_QTY ;
    The qry pulls like this: (wrong way)
    HTML Code:
    company    partID   Qty
    Acme Inc  ABC123   3
    Acme Inc  ABC123   4
    Acme Inc  ABC123   100
    Acme Inc  KLM444   3
    Acme Inc  KLM444   4
    Acme Inc  KLM444   100
    Acme Inc  QRP456   3
    Acme Inc  QRP456   4
    Acme Inc  QRP456   100
    It should be:
    HTML Code:
    Co.         part      Qty
    Acme Inc  ABC123   3
    Acme Inc  KLM444   4
    Acme Inc  QRP456   100
    The Qty field has duplicates. I've tried various ways, but I cannot make this work. TIA
    Last edited by PennyLayne; 01-16-13 at 12:06.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess is a bad join, but you'll have to check PK versus FK definitions to find that.

    The short and simple answer in this case would be to use the DISTINCT keyword to eliminate the duplicates, but that masks the problem instead of fixing it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ...
            INNER JOIN ORDERS O ON C.ID = O.ID
    If O.ID was Order ID and C.ID was Customer ID,
    is it meaningfull to compare O.ID(Order ID?) with C.ID(Customer ID?)?

    Code:
            INNER JOIN ORDER_LINE  OL ON O.ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
    ...
    SHIP_LINE was joined with ORDER_LINE by a condition OL.ORDER_ID = SL.ORDER_ID only.
    So, each row of ORDER_LINE(i.e. partID = ABC123 , KLM444 , QRP456) might be joined with all rows of SHIP_LINE which had same ORDER_ID(i.e. Qty
    = 3 , 4 , 100).

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This is why we need DDL. There is no way to see how the REFERENCES are set up. We also have a DATE data type now and CURRENT_TIMESTAMP (not the old Sybase getdate()/UNIX).

    I see that you also have Kabbalah numbers called “id” that violate the Law of Identity (“To be is to be something in particular; to be nothing in particular is to be nothing”). Your magical “id” can be a user (what is that?), a customer, an order, an automobile or squid! Think how silly that is.

    The usual model is to make the shipper_id a reference in the Orders (assuming one shipper per order), the shipment quantity an attribute of each order item (ordered_qty, shipped_qty, backorder_qty, etc)

    What you have probably done is mimic pointer chains from a non-RDBMS model of data. That does not work. Let's start with the DDL.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Greetings Mr. Celko! It is good to see that you're still crawling the face of the earth these days!

    I just fired you a text message. I hope that you respond, I've got news!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Where is the message?

    Pat, I cannot find your message. I am now to this group and i do not know how to navigate yet.

    Yep, I am still around. Trying to get two books done this year and I am falling behind. I get out to SQL Saturdays and need to get back on the road this year.

  7. #7
    Join Date
    Dec 2012
    Posts
    23
    Thank you all for your replies.

    @tonkuma, your reply was correct. That's exactly what's going on
    So, each row of ORDER_LINE(i.e. partID = ABC123 , KLM444 , QRP456) might be joined with all rows of SHIP_LINE which had same ORDER_ID(i.e. Qty = 3 , 4 , 100)
    but I'm unsure how to fix it.

    Here are the DDLs

    Customer: ROWID, ID, NAME
    USERS: P_ID, ID , D_ID (P_ID & ID are used in WHERE)
    ORDERS: ROWID, ID, CUST_ID
    ORDER_LINE: ROWID, ORDER_ID, LINE_NO, PART_ID
    SHIP_LINE: ROWID, PACKAGE_ID, LINE_NO, ORDER_ID, ORDER_LINE_NO, SHIPPED_QTY
    SHIPPER: ROWID, PACKAGE_ID, ORDER_ID, SHIPPED_DATE (shipped_date used in WHERE)

    they're all numbers except for shipped_date/Part_id/Customer Name

    current code:
    Code:
    SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY  
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.D_ID 
            INNER JOIN ORDERS O ON U.D_ID = O.CUST_ID 
            INNER JOIN ORDER_LINE  OL ON O.CUST_ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
              U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    GROUP BY C.NAME, OL.PART_ID, SL.SHIPPED_QTY;
    Thanks

    so ship_line.shipped_qty has Order_id's attached to the QTY numbers, causing the repitition of output.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    Greetings Mr. Celko!
    seconded!!

    i've known joe for years, met him once (about 20 years ago), and he was kind enough to be technical editor for my book

    joe's "instant sql programming" (wrox, 1995) was the first sql book i used as a reference, and it came complete with a 3¼" floppy containing watcom 4.0 sql runtime

    welcome, joe

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by PennyLayne View Post
    ...
    Here are the DDLs

    Customer: ROWID, ID, NAME
    USERS: P_ID, ID , D_ID (P_ID & ID are used in WHERE)
    ORDERS: ROWID, ID, CUST_ID
    ORDER_LINE: ROWID, ORDER_ID, LINE_NO, PART_ID
    SHIP_LINE: ROWID, PACKAGE_ID, LINE_NO, ORDER_ID, ORDER_LINE_NO, SHIPPED_QTY
    SHIPPER: ROWID, PACKAGE_ID, ORDER_ID, SHIPPED_DATE (shipped_date used in WHERE)

    they're all numbers except for shipped_date/Part_id/Customer Name

    current code:
    Code:
    SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY  
    FROM CUSTOMER C 
           	INNER JOIN USERS  U ON C.ID = U.D_ID 
            INNER JOIN ORDERS O ON U.D_ID = O.CUST_ID 
            INNER JOIN ORDER_LINE  OL ON O.CUST_ID = OL.ORDER_ID 
            INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID 
           	INNER JOIN SHIPPER SH ON SL.PACKAGE_ID = SH.PACKAGE_ID 
    WHERE U.P_ID = 'CCNNTT' AND
              U.D_ID = 'U-0022' AND
             ((SH.SHIPPED_DATE >= DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)) AND
              (SH.SHIPPED_DATE < DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0))) 
    GROUP BY C.NAME, OL.PART_ID, SL.SHIPPED_QTY;
    Thanks

    so ship_line.shipped_qty has Order_id's attached to the QTY numbers, causing the repitition of output.
    I thought that SHIP_LINE.LINE_NO or SHIP_LINE.ORDER_LINE_NO might be corresponded to ORDER_LINE.LINE_NO.
    I guessed SHIP_LINE.ORDER_LINE_NO was more likely to correspond to ORDER_LINE.LINE_NO.
    If so,
    try to add a condition
    AND SL.ORDER_LINE_NO = OL.LINE_NO
    to
    INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This is not DDL! This is a narrative again. Even worse, you have a magical Kabbalah number called “row_id” that is probably a psychical location in a table on one disk on one machine. Nothing you posted makes any sense in RDBMS, nothing follows ISO-11179 rules. Why do you think that “id” and “name” not vague generic attribute properties?

    Identifiers are seldom INTEGER because we do not use them for calculations. But we need to have check digits and regular expressions, so we use tag numbers stored in strings. Look at your credit cards, etc

    CREATE TABLE Customers
    (customer_nbr CHAR(9) NOT NULL
    CHECK (customer_nbr LIKE '[0-9]..[0-9]')PRIMARY KEY,
    customer_name VARCHAR(35) NOT NULL);

    Look at that table; it has a plural name because it models a set, not oen cusoemr at a time. I picked CHAR(9) for the customer_nbr because that is a common length for identifiers such as the DUNS. The VARCHAR(35) for name is USPS standard for mailing labels. See the need for basic research? The use of industry standards?

    Next table:

    CREATE TABLE Users:
    (p_id ??,
    d_id ??);

    What do “p_id” and “d_id” reference? What is a “p”? What is a “d”? You said they were integers, but show them as strings. Did the magical “id” transmogrify into a user in this table? Then it becomes an order in the following table. I will drop it until it makes sense.

    CREATE TABLE Orders
    (order_nbr CHAR(10) NOT NULL PRIMARY KEY,
    customer_nbr CHAR(9) NOT NULL
    REFERENCES Customers (customer_nbr),
    order_date DATE NOT NULL,
    shipment_nbr CHAR(15) NOT NULL
    REFERENCES Shipments (shipment_nbr),
    ..);

    Without any specs, or DDL, I will guess that an order goes out in a single shipment. If that is wrong, I can change this and create a weak entity for the parts of a shipment, then put it in a VIEW that looks like my Shipmetns table.

    You then have another fundamental design error. We do not model the paper forms, so we would never model the physical lines from a paper form. We model the items in an order as a weak entity. How do you identify your items? UPC? EAN? Internal SKU? I guess part_gtin, but we do not know from the narrative. I will also use CHAR(15) because of GTIN Standards.

    CREATE TABLE Order_Details
    (order_nbr CHAR(10) NOT NULL
    REFERENCES Orders(order_nbr),
    part_gtin CHAR(15) NOT NULL
    CHECK (part_gtin LIKE '[0-9] ..[0-9]')
    REFERENCES Inventory(part_nbr),
    PRIMARY KEY (order_nbr, part_gtin), --- see the key?
    order_qty INTEGER NOT NULL
    CHECK (order_qty > 0),
    unit_price DECIMAL (10,4) NOT NULL,
    ..);

    A shipper does not have a package attribute; he has a relationship with a package or a shipment. I am an author, and do not have books; the books and I have an authorship relation. You data model is wrong.

    CREATE TABLE Shipments
    (shipment_nbr CHAR(15) NOT NULL PRIMARY KEY,
    shipper_duns CHAR(9) NOT NULL
    REFERENCES Shippers(shipper_duns), --- entity table needed!
    order_nbr CHAR(10) NOT NULL
    REFERENCES Orders(order_nbr),
    shipment_date DATE NOT NULL,
    receipt_date DATE, --- null means in transit
    CHECK (shipment_date < receipt_date)
    ..);

    My guess at the desired query is a count of undelivered items by customers and parts. That could be done with something like this:

    SELECT C.customer_name, OD.part_gtin,
    SUM(SH.shipment_qty) AS undelivered_qty
    FROM Customers AS C,
    Orders AS O,
    Order_Details AS OD,
    Shipments AS SH
    WHERE O.order_nbr = OD.order_nbr
    AND O.customer_nbr = C.customer_nbr
    AND O.shipment_nbr = SH.shipment_nbr
    AND received_date IS NOT NULL
    GROUP BY C.customer_name, OD.part_gtin;

    You need more help than you can get here. Start with a book on RDBMS, then one on Data Modeling.

  11. #11
    Join Date
    Dec 2012
    Posts
    23
    @tonkuma oh my goodness! It works!! I wish I could buy you a beer or something!!

    This did it:
    INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID AND OL.LINE_NO = SL.CUST_ORDER_LINE_NO

    I don't usually write SQL code so I wasn't aware that one could use the AND statement attached to an Inner Join. I appreciate your help tremendously.
    Last edited by PennyLayne; 01-17-13 at 17:18.

  12. #12
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    seconded!!
    I'll up it to 3 . . .

    I bought Joe's "SQL for Smarties" in the fall of '98(iirc). My contract was to be the remediation dba for the mainframe y2k work. There was a problem with the group that was to provide SQL Server resources and i was asked to stand up 10 Win-servers, get SQL Server running, and migrate the data from the mainframe so the vendors could verify their products worked on the new servers. . . Some did, some needed lots of help.
    Last edited by papadi; 01-17-13 at 17:42.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Welcome, Alice Cooper (a few of us are old enough to get that one).

    I'm still 99% sure that my SELECT DISTINCT will at least mask the problem.

    Without at least the FK and PK definitions, I don't know that we can fix the query.

    As Mr. Celko points out, the underlying schema probably needs to be fixed. If that is needed, it will take more work than I'm comfortable trying to help with via the forum.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    PennyLayne,

    I think that design of Users table should be reconsidered, because of the reasons Celko described...
    Quote Originally Posted by Celko View Post
    ...

    Next table:

    CREATE TABLE Users:
    (p_id ??,
    d_id ??);

    What do “p_id” and “d_id” reference? What is a “p”? What is a “d”? You said they were integers, but show them as strings. Did the magical “id” transmogrify into a user in this table? Then it becomes an order in the following table. I will drop it until it makes sense.

    ...

  15. #15
    Join Date
    Dec 2012
    Posts
    23
    Yes, you are no (know) doubt right, but I am not a dba and was pulling data solely for reporting. in fact, my rights will not allow me to update any of their data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •